Category Archives: VBA

SQL Server connection in WMI Script

Posted on by

SQL Server connection in WMI Script.

VB Script provides composition of WMI and SQL Server. You can connect SQL Server and store WMI informations in SQL Server. Before reading this blog you already know that WMI is also works like central communication machine for all network servers. To know more read http://nirav.extreme-advice.com/2013/05/15/get-disk-space-usage-from-central-server-with-wmi/.

I have created one example for to collect windows event log information from Win32_NTLogEvent and store the information into SQL Server table. This task is helpfull to monitor event log error.

you need to create event log table in your sql server instance.


USE [AdventureWorks2012]
GO

/****** Object:  Table [dbo].[Win32_Event_Log]    Script Date: 27-05-2012 12:13:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Win32_Event_Log](
[Category] [bigint] NULL,
[CategoryString] [varchar](200) NULL,
[ComputerName] [varchar](20) NULL,
[EventCode] [bigint] NULL,
[EventIdentifier] [bigint] NULL,
[EventType] [bigint] NULL,
[Logfile] [varchar](200) NULL,
[Message] [varchar](max) NULL,
[RecordNumber] [bigint] NULL,
[SourceName] [varchar](20) NULL,
[TimeGenerated] [varchar](100) NULL,
[TimeWritten] [varchar](100) NULL,
[Type] [varchar](20) NULL,
[user] [varchar](20) NULL
) ON [PRIMARY]

GO

Now open Notepad paste below code in your notepad and save with “WMI_Event_Log.vbs”.


strComputer = "."
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colScheduledJobs = objWMIService.ExecQuery ("Select * from Win32_NTLogEvent where Logfile='System'")
flg = 0
Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=localmachinename ;Initial Catalog=AdventureWorks2012;user id ='sa';password='password'"

Set myConn = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command" )
myConn.Open DB_CONNECT_STRING
Set myCommand.ActiveConnection = myConn

For Each objJob in colScheduledJobs

'WMIDateStringToTimegenerator = CDate(Mid(objJob.TimeGenerated, 5, 2) & "/" & _
'     Mid(objJob.TimeGenerated, 7, 2) & "/" & Left(objJob.TimeGenerated, 4) _
'         & " " & Mid (objJob.TimeGenerated, 9, 2) & ":" & _
'             Mid(objJob.TimeGenerated, 11, 2) & ":" & Mid(objJob.TimeGenerated, _
'                 13, 2))
'WMIDateStringToTimeWr = CDate(Mid(objJob.TimeWritten, 5, 2) & "/" & _
'     Mid(objJob.TimeWritten, 7, 2) & "/" & Left(objJob.TimeWritten, 4) _
'         & " " & Mid (objJob.TimeWritten, 9, 2) & ":" & _

'             Mid(objJob.TimeWritten, 11, 2) & ":" & Mid(objJob.TimeWritten, _
'                 13, 2))

myCommand.CommandText = "Insert into Win32_Event_Log values(" & objJob.Category & ",'" & objJob.CategoryString & "','" & objJob.ComputerName & "'," & objJob.EventCode & "," & objJob.EventIdentifier & "," & objJob.EventType & ",'" & objJob.Logfile & "','" & objJob.Message & "'," & objJob.RecordNumber & ",'" & objJob.SourceName & "','" & objJob.TimeGenerated & "','" & objJob.TimeWritten & "','" & objJob.Type & "','" & objJob.User & "')"
myCommand.Execute
Next
myConn.Close

Execute this file periodically by schedule task and get information as your need.

Regards,
Nirav Gajjar

Monitor performance counter with WMI and SQL Server

Posted on by

Monitor performance counter with WMI and SQL Server.

WMI is very important to use as central monitoring task. I have created performance monitoring which checks Disk Queue Legth, Disk Read/Sec, Disk Write/Sec, CPU Usage, Memory Usage, Disk Read Bytes/Sec, Disk Write Bytes/Sec, ASP Request/Sec. Create a table “PerformanceCounter” to store all information.


USE [Adventureworks2012]
GO

/****** Object: Table [dbo].[PerformanceCounter] Script Date: 05/27/2013 12:35:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PerformanceCounter](
 [ServerName] [varchar](20) NULL,
 [CounterName] [varchar](100) NULL,
 [CounterValue] [varchar](100) NULL,
 [CreatedDate] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PerformanceCounter] ADD DEFAULT (getdate()) FOR [CreatedDate]
GO

Open Notepad and save below code with “PerformanceCount.vbs”.

Option Explicit
 On Error Goto 0
 Dim strSrv, strQuery
 Dim arrylist(9)
 arrylist(0) = ("Server1")
 arrylist(1) = ("Server2")
 arrylist(2) = ("Server3")
 arrylist(3) = ("Server4")
 arrylist(4) = ("Server5")
 arrylist(5) = ("Server6")
 arrylist(6) = ("Server7")
 arrylist(7) = ("Server8")
 arrylist(8) = ("Server9")
 arrylist(9) = ("Server10")

Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=localmachinename ;Initial Catalog=AdventureWorks2012;user id ='sa';password='password'"
 Dim myConn, myCommand

For Each strSrv in arrylist
 On Error Resume Next
 Call GetDiskQueueLegth(StrSrv)
 Call GetDiskReadperSec(StrSrv)
 Call GetDiskWritesPerSec(StrSrv)
 Call GetCPUUsage(StrSrv)
 Call GetMemory(strSrv)
 Call GetDiskWriteBytesPerSec(strSrv)
 Call GetDiskReadBytesPerSec(strSrv)
 Call GetRequestPerSec(strSrv)
 Next

Function GetMemory(strSrv)
 'On Error Resume Next
 Dim objWMIService, Item, Proc
 strQuery = "select Name,Pagelifeexpectancy from Win32_PerfRawData_MSSQLSERVER_SQLServerBufferManager"
 Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
 Set Item = objWMIService.ExecQuery(strQuery,,48)
 For Each Proc In Item
 Dim myConn,myCommand
 'wscript.echo Proc.Name
 'wscript.echo Proc.Pagelifeexpectancy
 If Proc.Pagelifeexpectancy < 300 Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','Memory PLE','" & Proc.Pagelifeexpectancy & "',getdate())"
 myCommand.Execute
 myConn.Close
 End If
 Next
 End Function

Function GetCPUUsage(strSrv)
 Dim objWMIService,objRefresher,colItems,Proc,CPUUsage,Cnt
 Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strSrv & "\root\cimv2")
 set objRefresher = CreateObject("WbemScripting.SWbemRefresher")
 Set colItems = objRefresher.AddEnum (objWMIService, "Win32_Processor").objectSet
 objRefresher.Refresh
 CPUUsage = 0
 Cnt = 0
 For Each Proc in colItems
 If Proc.LoadPercentage Then
 CPUUsage = CPUUsage + Proc.LoadPercentage
 'objRefresher.Refresh
 Cnt = Cnt + 1
 End If
 Next
 If Cnt > 0 and Cint(CPUUsage/cnt) > 90 Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','CPU Usage','" & Cint(CPUUsage/cnt) & "',getdate())"
 myCommand.Execute
 myConn.Close
 End If
 End Function

Function GetDiskQueueLegth(strSrv)
 Dim objWMIService, Item, Proc

strQuery = "select Name,AvgDiskQueueLength from win32_perfformatteddata_perfdisk_physicaldisk"
 Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
 Set Item = objWMIService.ExecQuery(strQuery,,48)
 For Each Proc In Item
 Dim myConn,myCommand
 'wscript.echo Proc.Name
 'wscript.echo Proc.AvgDiskQueueLength
 If Proc.Name = "_Total" and Proc.AvgDiskQueueLength > 2 Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','I/O Queue','" & Proc.AvgDiskQueueLength & "',getdate())"
 myCommand.Execute
 myConn.Close
 End If
 Next
 End Function

Function GetDiskWritesPerSec(strSrv)
 Dim objWMIService,objRefresher,colItems,Proc
 Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strSrv & "\root\cimv2")
 set objRefresher = CreateObject("WbemScripting.SWbemRefresher")
 Set colItems = objRefresher.AddEnum _
 (objWMIService, "Win32_PerfFormattedData_PerfDisk_PhysicalDisk").objectSet
 objRefresher.Refresh
 For Each Proc in colItems
 If Proc.name = "_Total" and Proc.DiskWritesPerSec Then
 IF Cint(1000/Proc.DiskWritesPerSec) > 15 Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','MS/Write','" & Cint(1000/Proc.DiskWritesPerSec) & "',getdate())"
 myCommand.Execute
 myConn.Close
 objRefresher.Refresh
 End If
 End If
 Next
 End Function

Function GetDiskReadperSec(strSrv)
 Dim objWMIService,objRefresher,colItems,Proc
 Set objWMIService = GetObject("winmgmts:" _
 & "{impersonationLevel=impersonate}!\\" & strSrv & "\root\cimv2")
 set objRefresher = CreateObject("WbemScripting.SWbemRefresher")
 Set colItems = objRefresher.AddEnum _
 (objWMIService, "Win32_PerfFormattedData_PerfDisk_PhysicalDisk").objectSet
 objRefresher.Refresh
 For Each Proc in colItems
 If Proc.name = "_Total" and Proc.DiskReadsPerSec Then
 If Cint(1000/Proc.DiskReadsPerSec) > 15 Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','MS/Read','" & Cint(1000/Proc.DiskReadsPerSec) & "',getdate())"
 myCommand.Execute
 myConn.Close
 objRefresher.Refresh
 End If
 End If
 Next
 End Function

Function GetDiskReadBytesPerSec(strSrv)
 Dim objWMIService, Item, Proc

strQuery = "select Name,DiskReadBytesPerSec from win32_perfformatteddata_perfdisk_physicaldisk"
 Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
 Set Item = objWMIService.ExecQuery(strQuery,,48)
 For Each Proc In Item
 Dim myConn,myCommand
 'wscript.echo Proc.Name
 'wscript.echo Proc.AvgDiskQueueLength
 If Proc.Name = "_Total" Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','RGB/Sec','" & Round((Proc.DiskReadBytesPerSec/1024/1024/1024),3) & "',getdate())"
 myCommand.Execute
 myConn.Close
 End If
 Next
 End Function

Function GetDiskWriteBytesPerSec(strSrv)
 Dim objWMIService, Item, Proc

strQuery = "select Name,DiskWriteBytesPerSec from win32_perfformatteddata_perfdisk_physicaldisk"
 Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
 Set Item = objWMIService.ExecQuery(strQuery,,48)
 For Each Proc In Item
 Dim myConn,myCommand
 'wscript.echo Proc.Name
 'wscript.echo Proc.AvgDiskQueueLength
 If Proc.Name = "_Total" Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','WGB/Sec','" & Round((Proc.DiskWriteBytesPerSec/1024/1024/1024),3) & "',getdate())"
 myCommand.Execute
 myConn.Close
 End If
 Next
 End Function

Function GetRequestPerSec(strSrv)
 'On Error Resume Next
 Dim objWMIService, Item, Proc
 strQuery = "select Name,RequestsPerSec from Win32_PerfFormattedData_ASPNET_ASPNETApplications"
 Set objWMIService = GetObject("winmgmts:\\" & StrSrv & "\root\cimv2")
 Set Item = objWMIService.ExecQuery(strQuery,,48)
 For Each Proc In Item
 Dim myConn,myCommand
 If Proc.Name = "_Total" and (strSrv = "Server9" or strSrv = "Server14")Then
 Set myConn = CreateObject("ADODB.Connection")
 Set myCommand = CreateObject("ADODB.Command" )
 myConn.Open DB_CONNECT_STRING
 Set myCommand.ActiveConnection = myConn
 myCommand.CommandText = "Insert into PerformanceCounter values('" & StrSrv & "','Req/Sec','" & Proc.RequestsPerSec & "',getdate())"
 myCommand.Execute
 myConn.Close
 End IF
 Next
 End Function

Regards,
Nirav Gajjar