Menu
–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!