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

Get Disk space usage from central server with WMI

Get Disk space usage from central server with WMI.

As a DBA job we have to check server health regularly. sometime that happens when you are working on multiple servers and its very difficult to monitor health of all servers. I faced the same issue last week. My application abnormally going down and i am unable to connect database, while i had tried to connect that particular server i found there is only 20KB free space in data drive.

It happens sometime that you don’t know how fast drive is filling and adversity occurs due to low disk space.
I have created one wmi application that observes disk space and mail us on your defined email id’s. This application will handles all server’s Disk usage from central server. For that you need to create one common user with WMI permission.
Please follow the below steps to manage disk space usage from all servers.

Step1 : All of your server must be enable for remote WMI connection.

Click on Start –> Run –> WMIMGMT.MSC

WMI1

Just right click on WMI Control ans select property.

On “WMI Control Property” go to “Security” tab and select “Root” click on Security button which will pop one more window for “Security for root”.

On “Security for Root” window mark on Remote enable for your “WMIUser” or “Everyone”.

WMI2

Step2 : Open Notepad and copy below WMI Code to notepad and save with “DiskUsageWithWMi.csv”.

Sub SendMail(Sender, Recipient, Subject, Message)
   Set objMessage = CreateObject("CDO.Message")
   objMessage.Subject = Subject
   objMessage.From = Sender
   objMessage.To = Recipient
   objMessage.htmlbody = Message
   objMessage.Send
End Sub

Dim strComputer
Dim objWMIService, objItem, colItems
Dim strDriveType,txt
Dim arrylist(4)
Dim pctFreeSpace,strFreeSpace,strusedSpace,strDiskSize

arrylist(0) = ("Server1")
arrylist(0) = ("Server2")
arrylist(0) = ("Server3")
arrylist(0) = ("Server4")
arrylist(0) = ("Server5")

txt = "<table cellspacing='1' cellpadding='0' border='0' bordercolor='black' id='shell'>"
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'>Drive</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Size(GB)</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Used(GB)</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Free(GB)</th>"
txt = txt & "<th width='75' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'>Free(%)</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_LogicalDisk WHERE DriveType=3 or DriveType=4")

   txt = txt & "<tr bgcolor='#DDDDDD'Bordercolor='#000000'><td colspan='5' 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></tr>"

   For Each objItem in colItems

      pctFreeSpace = FormatNumber(((objItem.FreeSpace / objItem.Size) * 1000)/10,2)
      strDiskSize = FormatNumber(objItem.Size /1073741824,3)
      strFreeSpace = FormatNumber(objItem.FreeSpace /1073741824,3)
      strUsedSpace = objItem.Size-objItem.FreeSpace
      strUsedSpace = FormatNumber(strUsedSpace/1073741824,3)
      IF pctFreeSpace <= 10 Then
         txt = txt & "<tr bgcolor='#9C3333' Bordercolor='#000000'>"
      Else
         txt = txt & "<tr Bordercolor='#000000'>"
      End If
      txt = txt & "<td 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.Name & "</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strDiskSize &"</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strUsedSpace &"</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& strFreeSpace &"</Font></td>"
      txt = txt & "<td align = 'Right' style='border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid'><font size='2'>"& pctFreeSpace &"</Font></td>"
      txt = txt & "</tr>"
      pctFreeSpace = 0
      strDiskSize = 0
      strFreeSpace = 0
      strUsedSpace = 0
      strUsedSpace = 0
      Set objWMIService = Nothing
      Set colItems = Nothing
   Next
Next
txt = txt & "</table>"
SendMail "emailid@sender.com","emailid.reciever.com", strComputer & "Disk Space Information was executed at " & Now(), txt

Step3 : You can create a schedule task which calls this VBS file daily on your required duration like every hour or every 3 hour or daily one time etc.

This code will send you a message like this.

Drive Size(GB) Used(GB) Free(GB) Free(%)
Server1
C: 465.150 213.078 252.073 54.19
D: 465.247 340.871 124.376 26.73
E: 465.247 425.474 39.773 8.55
F: 465.247 328.124 137.123 29.47
L: 169.853 125.357 44.496 26.20
P: 749.997 664.429 85.568 11.41
T: 169.991 72.388 97.603 57.42
Server2
C: 20.407 15.690 4.717 23.12
E: 54.052 35.063 18.988 35.13
F: 465.759 279.674 186.085 39.95
Server3
C: 22.399 20.217 2.182 9.74
D: 67.750 37.477 30.273 44.68
E: 45.281 37.560 7.721 17.05
G: 67.748 53.881 13.868 20.47
H: 272.243 244.077 28.166 10.35
W: 5,527.029 4,654.987 872.042 15.78
Server4
C: 20.216 18.012 2.204 10.90
E: 115.243 58.476 56.767 49.26

Some of them showing in red color which means that drive has lower than 10 % disk space.

Regards,

Nirav Gajjar

Posted in WMI

SSIS Package to get Disk Space Info with WMI and Script Task

SSIS Package to get Disk Space Info with WMI and Script Task.

In Previous blog i have created SSIS Package to get Disk Space Info with WMI.(Link: http://nirav.extreme-advice.com/2013/02/20/ssis-package-to-get-disk-space-info-with-wmi/)
That was a simple way to gather disk space information with importing data in CSV file and exporting data in database.
I have used same table in this task and i have used Script Task in behalf of CSV file.

Table Structure.

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 to get Disk Space Info with WMI and Script Task.

Step1 : Open your SSIS package and drag WMI Data Reader Task from SSIS Toolbox.

DiskSpaceInfoWithWMIScript

Step2 : Create new WMI Connection shown as below.

DiskSpaceInfoWithWMIScript2

Step3 : Create variable Obj_Result with data type object to recieve result data.

DiskSpaceInfoWithWMIScript3

Step4 : Select User::Obj_Result in Destination.

DiskSpaceInfoWithWMIScript4 

Step5 : Right click on Script Task –> Edit.

Select User::Obj_Result as ReadOnlyVariables.

DiskSpaceInfoWithWMIScript5

Step5 : Click on Edit on Script Task Editor and paste below code.


public void Main()
 {
            try
            {
                DataTable DT;
                DataRow DR;
                SqlConnection SQLCon = new SqlConnection("Server=Nirav'sDiary;Database=AdventureWorks2012;User Id=sa;Password=pwd;");

                DT = (DataTable)Dts.Variables["User::Obj_Result"].Value;
                foreach (DataRow row in DT.Rows)
                {
                    DR = row;
                    string Name;
                    int Size, FreeSpace;
                    Name = DR["Name"].ToString();
                    Size = Convert.ToInt32(Convert.ToDouble(DR["Size"].ToString()) / 1024 / 1024 / 1024);
                    FreeSpace = Convert.ToInt32(Convert.ToDouble(DR["FreeSpace"].ToString()) /1024/ 1024 / 1024);
                    SqlCommand cmd = new SqlCommand("Insert Into ServerDiskInfo Values('"+ Name +"',"+ Size +","+ FreeSpace +",GetDate())", SQLCon);
                    SQLCon.Open();
                    cmd.ExecuteNonQuery();
                    SQLCon.Close();
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
 }

Step6 : Executing whole task it will make entry in ServerDiskinfo Table.

Query to get ServerDiskInfo.

Declare @Today as datetime
Det @Today = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
Delect 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</pre>

Regards,
Nirav gajjar

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