Load Windows Event Log Errors into a SQL Table

By Lori Brown | Helpful Scripts

Apr 07

–By Lori Brown @SQLSupahStah

Part of being a proactive DBA is to get information on what is going on at the server level as well as at the SQL level. We know that the Windows Event logs are around but I find that not many DBA’s check them regularly. You can make this easier by using a vbscript to load errors and warnings from the Event Logs into a SQL table.

Review or scrape the Event Logs at a minimum to find out if any Windows or hardware related errors or warnings are being written. Most hardware vendors write warnings to the Event Logs when they anticipate an error is going to occur, so this gives you the opportunity to be proactive and correct the problem during a scheduled down time, rather than having a mid-day emergency.

Build a SQL table to hold Event Log info:

— table for the Windows Event Log

CREATE TABLE [dbo].[WinEventLog](

[ID] INT IDENTITY(1,1) NOT NULL,

[ComputerName] VARCHAR(128) NULL,

[EventCode] INT NULL,

[RecordNumber] INT NULL,

[SourceName] VARCHAR(128) NULL,

[EventType] VARCHAR(50) NULL,

[WrittenDate] DATETIME NULL,

[UserName] VARCHAR(128) NULL,

[Message] VARCHAR(MAX) NULL

) ON [PRIMARY]

GO

Use the vbscipt code below by saving it into a notepad document with the extension .vbs. Make sure to modify connection strings to connect to your instance and the correct database. And, set the number of days you want to check as well. I have it defaulted to 2 days but you can set it to whatever makes sense for you.

************************

‘VBScript

‘Purpose of script to query Application log for errors

dim strConnect, strComputer, strMessage, RoleStr

dim Category, Computer_Name, Event_Code, Message, Record_Number, Source_Name, Time_Written, Event_Type, User

dim dtmStartDate, dtmEndDate, DateToCheck

dim dtTimeWritten

 

‘Connection string for SQL Server database.

strConnect = “DRIVER=SQL Server;” _

& “Trusted_Connection=Yes;” _

& “DATABASE=<<Database Name>>;” _

& “SERVER=<<SQL Instance Name>>”

 

‘Use this string if SQL Server driver does not work

‘strConnect = “Provider=SQLOLEDB;” _

‘& “Data Source=<<SQL Instance Name>>;” _

‘& “Initial Catalog=<<Database Name>>;” _

‘& “Integrated Security=SSPI;”

 

‘ Connect to database.

Set adoConnection = CreateObject(“ADODB.Connection”)

adoConnection.ConnectionString = strConnect

adoConnection.Open

 

Set dtmStartDate = CreateObject(“WbemScripting.SWbemDateTime”)

Set dtmEndDate = CreateObject(“WbemScripting.SWbemDateTime”)

‘ Set number of days to scrape here

DateToCheck = Date – 2

dtmEndDate.SetVarDate Date, True

dtmStartDate.SetVarDate DateToCheck, True

 

strComputer = “.”

Set objWMIService = GetObject(“winmgmts:” _

& “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2”)

Set colLoggedEvents = objWMIService.ExecQuery _

(“Select * from Win32_NTLogEvent Where Logfile = ‘Application’ and (” & _

“TimeWritten >= ‘” & dtmStartDate & _

“‘ and TimeWritten < ‘” & dtmEndDate & _

“‘) and (EventType = ‘1’ or EventType = ‘2’)”)

 

For Each objEvent in colLoggedEvents

Category = objEvent.Category

Computer_Name = objEvent.ComputerName

Event_Code = objEvent.EventCode

Message = objEvent.Message

Record_Number = objEvent.RecordNumber

Source_Name = objEvent.SourceName

Time_Written = objEvent.TimeWritten

Event_Type = objEvent.type

User = objEvent.User

 

‘Fix single quotes in the message string

strSQ = Chr(39)

strDQ = Chr(34)

if len(Message) > 0 then

strMessage = Replace(Message, strSQ, strDQ)

else

strMessage = ” “

end if

 

dtTimeWritten = WMIDateStringToDate(Time_Written)

 

RoleStr = “SET NOCOUNT ON INSERT INTO WinEventLog (ComputerName, EventCode, RecordNumber,” _

& “SourceName, EventType, WrittenDate, UserName, Message) VALUES” _

& “(‘” & Computer_Name & “‘, ‘” & CLng(Event_Code) & “‘, ‘” & CLng(Record_Number) _

& “‘, ‘” & Source_Name & “‘, ‘” & Event_Type & “‘, ‘” & dtTimeWritten _

& “‘, ‘” & User & “‘, ‘” & strMessage & “‘)”

adoConnection.Execute RoleStr

Next

 

adoConnection.Close

 

Function WMIDateStringToDate(Time_Written)

WMIDateStringToDate = CDate(Mid(Time_Written, 5, 2) & “/” & _

Mid(Time_Written, 7, 2) & “/” & Left(Time_Written, 4) _

& ” ” & Mid (Time_Written, 9, 2) & “:” & _

Mid(Time_Written, 11, 2) & “:” & Mid(Time_Written, _

13, 2))

End Function

************************

To run the vbscript, you can double click it to run it manually or you can set up a SQL job that uses CMDEXEC to call the script.

@subsystem = N’CmdExec’,

@command = N’cscript E:\SQLRX\ScrapeWindowsEventLog.vbs’

Hopefully this will help make you more proactive and knowledgeable about your servers in general.

Enjoy!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>