Reading Windows Events and Inserting in SQL Server
As a DBA its not only we need to check for SQL Server Error Logs but often we also need to check for Windows Event Log for any Critical Error or Warning as well. Incase a daily check list needs to be followed at that time reading events if in thousands and filtering is a big pain. Here is where WMI and Script comes handy.
So i have written below VBScript which reads Windows Application and System events for Errors and Warnings for 1 day back till date and inserts into a table into SQL Server. This can later be used for querying and multiple purposes etc. This can we turned to Weekly as well by Selecting number of days back you want the logs to to be Read.
This can further be used in SSIS Which i will be posting shortly.
Sub Main()
Const CONVERT_TO_LOCAL_TIME = False
Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
Set dtmEndDate = CreateObject("WbemScripting.SWbemDateTime")
DateToCheck = CDate(Date)
'SET Number of Days back u want the logs to be read
dtmStartDate.SetVarDate DateToCheck - 1, CONVERT_TO_LOCAL_TIME
dtmEndDate.SetVarDate DateToCheck, CONVERT_TO_LOCAL_TIME
dim sServer, sConn, oConn,oRS
'Connection to SQL Server
'Provide Server Name instead of "." if any other machine
'Replace <DBServerName>,<USERID>,<PASSWORD>,<DATABASENAME> with your desired details
sServer="."
sConn= _
"Provider=SQLOLEDB;Data Source=<DBServerName>;" & _
"Trusted_Connection=Yes;Initial Catalog=<DATABASENAME>;" & _
"User ID=<USERID>;Password=<PASSWORD>;"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn
Set oRS =CreateObject("ADODB.Recordset")
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & "." & "\root\cimv2")
'USE THIS FOR READING SYSTEM EVENTS AND TYPE ERROR
Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where TimeGenerated >= '" & dtmStartDate &
"' and Logfile = 'System' and Type = 'Error'")
For Each objEvent in colLoggedEvents
strLogFile = objEvent.LogFile
strSourceName = objEvent.SourceName
strType = objEvent.Type
strEventCode = objEvent.EventCode
strMessage = objEvent.Message
strMessage = Replace(strMessage,"'","")
strDate = WMIDateStringToDate(objEvent.TimeGenerated)
'REPLACE <TABLENAME> with your table Name
sSQL = "Insert into <TABLENAME> (LogFile,"_
&"EventSource,"_
&"EventType,"_
&"EventID,"_
&"EventMessage,"_
&"TimeGenerated)"_
&"Values"_
&"('" &strLogFile& "',"_
&"'" &strSourceName& "',"_
&"'" &strType& "',"_
&"'" &strEventCode& "',"_
&"'" &strMessage& "',"_
&"'" &strDate& "')"
ors.open sSQL, oconn
'ors.close
Next
'USE THIS FOR READING SYSTEM EVENTS AND TYPE WARNING
Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where TimeGenerated >= '" & dtmStartDate & "' and Logfile = 'System' and Type =
'Warning'")
For Each objEvent in colLoggedEvents
strLogFile = objEvent.LogFile
strSourceName = objEvent.SourceName
strType = objEvent.Type
strEventCode = objEvent.EventCode
strMessage = objEvent.Message
strMessage = Replace(strMessage,"'","")
strDate = WMIDateStringToDate(objEvent.TimeGenerated)
'REPLACE <TABLENAME> with your table Name
sSQL = "Insert into <TABLENAME>(LogFile,"_
&"EventSource,"_
&"EventType,"_
&"EventID,"_
&"EventMessage,"_
&"TimeGenerated)"_
&"Values"_
&"('" &strLogFile& "',"_
&"'" &strSourceName& "',"_
&"'" &strType& "',"_
&"'" &strEventCode& "',"_
&"'" &strMessage& "',"_
&"'" &strDate& "')"
ors.open sSQL, oconn
'ors.close
Next
'USE THIS FOR READING APPLICATION EVENTS AND TYPE ERROR
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & "." & "\root\cimv2")
Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where TimeGenerated >= '" & dtmStartDate &
"' and Logfile = 'Application' and Type = 'Error'")
For Each objEvent in colLoggedEvents
strLogFile = objEvent.LogFile
strSourceName = objEvent.SourceName
strType = objEvent.Type
strEventCode = objEvent.EventCode
strMessage = objEvent.Message
strMessage = Replace(strMessage,"'","")
strDate = WMIDateStringToDate(objEvent.TimeGenerated)
'REPLACE <TABLENAME> with your table Name
sSQL = "Insert into <TABLENAME>(LogFile,"_
&"EventSource,"_
&"EventType,"_
&"EventID,"_
&"EventMessage,"_
&"TimeGenerated)"_
&"Values"_
&"('" &strLogFile& "',"_
&"'" &strSourceName& "',"_
&"'" &strType& "',"_
&"'" &strEventCode& "',"_
&"'" &strMessage& "',"_
&"'" &strDate& "')"
ors.open sSQL, oconn
'ors.close
Next
'USE THIS FOR READING APPLICATION EVENTS AND TYPE WARNING
Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where TimeGenerated >= '" & dtmStartDate & "' and Logfile = 'Application' and Type =
'Warning'")
For Each objEvent in colLoggedEvents
strLogFile = objEvent.LogFile
strSourceName = objEvent.SourceName
strType = objEvent.Type
strEventCode = objEvent.EventCode
strMessage = objEvent.Message
strMessage = Replace(strMessage,"'","")
strDate = WMIDateStringToDate(objEvent.TimeGenerated)
'REPLACE <TABLENAME> with your table Name
sSQL = "Insert into <TABLENAME>(LogFile,"_
&"EventSource,"_
&"EventType,"_
&"EventID,"_
&"EventMessage,"_
&"TimeGenerated)"_
&"Values"_
&"('" &strLogFile& "',"_
&"'" &strSourceName& "',"_
&"'" &strType& "',"_
&"'" &strEventCode& "',"_
&"'" &strMessage& "',"_
&"'" &strDate& "')"
ors.open sSQL, oconn
'ors.close
Next
'ors.close
End Sub
'FUNCTION FOR PARSING DATE
Function WMIDateStringToDate(dtmInstallDate)
WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) _
& " " & Mid (dtmInstallDate, 9, 2) & ":" & Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, 13, 2))
End Function