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