SQL Server connection in WMI Script

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

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

Get List of Stopped SQL Services from central server with WMI

Get List of Stopped SQL Services from central server with WMI.
As a DBA we have to monitor many things. To monitor all SQL Server Serives wotking properly works fine we have to 
check status of services periodically.
We can do that it manually or automatically, Manually - By using Services.msc, Automatically - By using scripting and programming.
I have created a WMI Script which is very helpfull to DBA Monitoring Task.

Benifits of using WMI.
1) WMI gives detail information without affecting performance.
2) WMI is applicable to handle from central server, you can get information from all remote server without any overhead.
For this you just need to create Wnidows user which have WMI Permission. For Permission please see http://nirav.extreme-advice.com/2013/05/15/get-disk-space-usage-from-central-server-with-wmi/
3) Automatically sends mail for which service is stopped.
4) WMI script has a provision to start stopped services automatically.

Get List of Stopped SQL Services from central server with WMI.
Step1 : Open notepad and save below code with "list of stopped services.vbs"
Dim strComputer
Dim objWMIService, objItem, colItems
Dim strDriveType,txt,flg
Dim arrylist(4)

arrylist(0) = ("Server1")
arrylist(1) = ("Server2")
arrylist(2) = ("Server3")
arrylist(3) = ("Server4")
arrylist(4) = ("Server5")

txt = "<table cellspacing='0' cellpadding='0' border='0' bordercolor='black' id='shell' colspace ='2'>"
txt = txt & "<tr bgcolor='#DDDDDD' height=30>"
txt = txt & "<th width='50' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Server</th>"
txt = txt & "<th width='100' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Service</th>"
txt = txt & "<th width='50' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Status</th>"
txt = txt & "</tr>"

For Each strComputer in arrylist
On Error Resume Next

Set objWMIService = Nothing
Set colItems = Nothing
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_Service Where DisplayName like'%sql%' and AcceptStop = False")
    If Err.Number = 0 Then
	    For Each objItem in colItems
            txt = txt & "<tr Bordercolor='#000000'>"
            txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strComputer &"</Font></td>"
            txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& objItem.DisplayName &"</Font></td>"
            txt = txt & "<td align = 'Left' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& objItem.State &"</Font></td>"
            txt = txt & "</tr>"
            Set objWMIService = Nothing
            Set colItems = Nothing
        Next
    End If
Next
txt = txt & "</table>"

    Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = "List of stopped sql services"
    objMessage.From = "Alerts@Sender.com"
    objMessage.To = "niravgajjar@Receiver.com"
    objMessage.htmlbody = txt
    objMessage.Send

Step2 : Create one Schedule task and call this VBS file periodically like Daily ones or twice as need of process. You will get mail like below.

Server Service Status
Server1 SQL Active Directory Helper Service Stopped
Server2 SQL Active Directory Helper Service Stopped
Server2 SQL Server Agent (MSSQLSERVER) Stopped
Server3 SQL Active Directory Helper Service Stopped
Server3 SQL Server Agent (SQL2K8R2EXP) Stopped
Server4 SQL Server Distributed Replay Client Stopped
Server4 SQL Server Distributed Replay Controller Stopped
Server4 SQL Server Agent (SQL2K12EXPR) Stopped
Server5 SQL Server FullText Search (MSSQLSERVER) Stopped
Server5 SQL Server Active Directory Helper Stopped
 Step3 : This is an additional step to make changes in script.
if you need all stopped services then change query.
Select * from Win32_Service Where AcceptStop = False

if you need all SQL services then change query.

Select * from Win32_Service Where DisplayName like'%sql%'

if you need all services.

Select * from Win32_Service

SSIS Package to get Disk Space Info with WMI

SSIS Package to get Disk Space Info with WMI.

I have created package that gives Disk Space info using WMI Data Reader Task.
You need minor knowledge on WMI Query and WMI Class, here i have used “Win32_LogicalDisk” WMI class.
This package checks disk info for logical disk of your machine and stores value to the database.
To store information create one table on your database.


Use AdventureWorks2012
Go
Create Table ServerDiskInfo
(
Drive varchar(5),
DriveSize numeric(18,2),
FreeSpace numeric(18,2),
CollectionDate Datetime Not NUll Default GetDate()
)

Lets start to create SSIS package.
Step1 : Open your SSIS package and drag WMI Data Reader Task from SSIS Toolbox.
SSIS Package Disk Space With WMI 1

Step2 : Rigth click on Connection Manager pane –> Select New Connection –> Select WMI and click on ADD button.

SSIS Package Disk Space With WMI 2

SSIS Package Disk Space With WMI 3

Step3 : It will opens WMI Connection Manger Editor. you can give any name to connection name and discription.

If you are creating package for your local machine then

\\LocalHost to connect your local machine and check on Use Windows Authentication.

If you are creating package for remote server then

\\RemoteServerName to connect remote server and provide “UserName” and “Password” for User Credentials.

SSIS Package Disk Space With WMI 4

Click on Test button to confirm connetion established successfully.

Step4 : Right Click on WMI Data Reader Task –> Click on Edit –> WMI Options Pane.

Give your WMI Connection –> WMI_localmachine(WMI Connection Manager).

WQL Query Source Type –> Direct Input.

WQL Query Source –>Select Name, Size, FreeSpace From Win32_LogicalDisk Where DriveType = 3.

OUTPUT Type –>DataTable.

OverWrite Destination –> Overwrite Destination.

Destination Type –> File Connecion.

Destination –> Create New Connetion.

SSIS Package Disk Space With WMI 6

Click OK.

SSIS Package Disk Space With WMI 7 New

Click Ok.

Step5 : Execute task to generate C:\ServerDiskInfo.csv file.

Step6: Drag Data Flow task –> Right Click –> Edit.

Step7: Drag Flat File Source from Other Source Tool, Drag Data Conversion from Other Transforms, Drag OLEDB Destination from Other Destinations.

SSIS Package Disk Space With WMI 8 New

Step8 : Right Click on Flat File Source –> Edit

SSIS Package Disk Space With WMI 9

Click on Columns Tab to check the columns then click on OK.

Step9 : Right Click on Data Conversion –> Select FreeSpace and Size.

Give Numeric[DT_numeric] in Data Type to both columns.

SSIS Package Disk Space With WMI 10

Step10 : Right Click on OLE DB Connection –> Edit.

Select OLEDB connection manager. if there is no OLEDB Connection manager then create new connection manager for AdventureWorks2012 and Select ServerDiskInfo Table.

SSIS Package Disk Space With WMI 11

Step11: Select mappings from left pane.

SSIS Package Disk Space With WMI 12 new

map Input Columns with Destination Columns given in image.

Click OK.

Executing whole task it will make entry in ServerDiskinfo Table.

Query to get ServerDiskInfo.

declare @Today as datetime
set @Today = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
select Drive,Cast(DriveSize/1024/1024/1024 as Numeric(10,2)) as SizeInGB,Cast(FreeSpace/1024/1024/1024 as Numeric(10,2)) as FreeSpaceInGB from ServerDiskInfo
where DATEADD(DD,0,DATEDIFF(DD,0,CollectionDate)) = @Today

Regards,
Nirav Gajjar