VBScript & SQL: Centralize Windows Events in SQL Server

This is a barebones script that gathers server events across a domain and stores them in SQL Server using asyncronous notifications as seen in this sample of the code:

strWQL = "Select * from __InstanceCreationEvent where TargetInstance isa 'Win32_NTLogEvent'" objWMIService.ExecNotificationQueryAsync eventSink,strWQL

Only one instance of this script is required to monitor multiple servers across a domain.

/* I chose to normalize this database just a tad.

Since event messages are large and often repeated, I created a table specifically for them. Otherwise, the database would grow big fairly quickly.

Be sure to populate your computers table with the computers you'd like monitored. If you'd like to automatically populate this table using ADSI, see barebonespopsql.vbs

The Event Types table is just for reference.

*/ create database WindowsEvents go use windowsevents go

create table computers ( ID int identity primary key, computer varchar(255) )

insert into computers (computer) values ('ALPHONSE') insert into computers (computer) values ('LELAND')

Create table eventViewerMessages ( id int identity primary key, ComputerID int references computers(ID), SourceName varchar(255), eventcode int, Message varchar(1024), )

Create table eventTypes ( ID int primary key, eventType varchar(25) )

Insert into eventTypes (ID, eventType) values (1,'Error') Insert into eventTypes (ID, eventType) values (2,'Warning') Insert into eventTypes (ID, eventType) values (4,'Information') Insert into eventTypes (ID, eventType) values (8,'Security audit success') Insert into eventTypes (ID, eventType) values (16,'Security audit failure')

Create table eventViewer ( ID int identity primary key, Category int, CategoryString varchar(512), ComputerID int references computers(ID), EventCode int, EventIdentifier int, Logfile varchar(32), MessageID int references eventViewerMessages(ID), RecordNumber int, SourceName varchar(255), TimeGenerated varchar(255), TimeWritten varchar(255), EventType varchar(512), NTUser varchar(104) ) Go

strDomain = "HOME" ConnString = "Provider=SQLOLEDB; Data Source=LELAND; Initial catalog=WindowsEvents; Trusted_Connection=yes;" Set TheDomain = GetObject("WinNT://" & strDomain) TheDomain.Filter = Array("Computer")

Set rs = CreateObject("adodb.recordset") strSQL = "select computer from computers where id = 0" rs.Open strSQL,ConnString,1,2 For Each Computer In TheDomain rs.AddNew strComputer = UCase(Computer.Name) Set objWMIService = GetObject("winmgmts:" & strComputer & "\root\cimv2") Set colSettings = objWMIService.ExecQuery ("SELECT * FROM Win32_OperatingSystem") For Each objOperatingSystem in colSettings If InStr(UCase(objOperatingSystem.Name),"SERVER") > 0 Then rs("Computer") = strComputer Next Set colSettings = Nothing Set objWMIService = Nothing Next rs.UpdateBatch rs.close Set rs = nothing Set TheDomain = Nothing

'''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Set Your SQL String Here. Get tables from barebonesEVSQL.sql '''''''''''''''''''''''''''''''''''''''''''''''''''''' ConnString = "Provider=SQLOLEDB; Data Source=LELAND; Initial catalog=WindowsEvents; Trusted_Connection=yes;"

''''''''''''''''''''''''''''''''''''''''''''''''''''' ' GET COMPUTERS TO MONITOR '''''''''''''''''''''''''''''''''''''''''''''''''''''

Set rs = CreateObject("adodb.recordset") strSQL = "select computer from computers" rs.Open strSQL,ConnString,1,1 Do until rs.eof Push2EVServer(rs("computer")) rs.movenext loop rs.close Set rs = nothing

''''''''''''''''''''''''''''''''''''''''''''''''''''' ' KEEP THE SCRIPT GOING FOREVER '''''''''''''''''''''''''''''''''''''''''''''''''''''

While (True) Wscript.Sleep(1000) Wend

''''''''''''''''''''''''''''''''''''''''''''''''''''' ' SET SINK SUB '''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub Push2EVServer(strComputer) Set objWMIService = GetObject("winmgmts:" & strComputer & "\root\cimv2") Set eventSink = wscript.CreateObject("WbemScripting.SWbemSink", "EVSINK_") strWQL = "Select * from __InstanceCreationEvent where TargetInstance isa 'Win32_NTLogEvent'" objWMIService.ExecNotificationQueryAsync eventSink,strWQL End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ADD EVENTS TO SEMI-NORMALIZED SQL DATABASE '''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub EVSINK_OnObjectReady(objObject, objAsyncContext) 'Set Variables and Objects Set rs = CreateObject("adodb.recordset") ConnString = "Provider=SQLOLEDB; Data Source=LELAND; Initial catalog=WindowsEvents; Trusted_Connection=yes;"

'Event Stuff Category = objObject.TargetInstance.Category CategoryString = objObject.TargetInstance.CategoryString ComputerName = objObject.TargetInstance.ComputerName EventCode = objObject.TargetInstance.EventCode Logfile = objObject.TargetInstance.Logfile Message = objObject.TargetInstance.Message RecordNumber = objObject.TargetInstance.RecordNumber SourceName = objObject.TargetInstance.SourceName TimeGenerated = objObject.TargetInstance.TimeGenerated TimeWritten = objObject.TargetInstance.TimeWritten EventType = objObject.TargetInstance.Type 'Type, as opposed to EventType, is backwards compat. NTUser = objObject.TargetInstance.User

strSQL = "select ID from computers where computer = '" & ComputerName & "'" rs.Open strSQL,ConnString,1,1 If rs.eof And rs.bof Then Exit Sub 'big problems Else ComputerID = rs("ID") End If rs.Close

strSQL = "select ID,ComputerID,SourceName,EventCode,Message from eventViewerMessages where ComputerID = " & ComputerID & " and sourcename = '" & SourceName & "' and EventCode = '" & EventCode & "' and Message = '" & Message & "'" rs.Open strSQL,ConnString,1,2 If rs.eof And rs.bof Then ' Add it rs.AddNew rs("ComputerID") = ComputerID rs("SourceName") = SourceName rs("EventCode") = EventCode rs("Message") = Message rs.Update MessageID = rs("ID") Else MessageID = rs("ID") End If rs.Close

strSQL = "select * from eventViewer where id = 0" rs.Open strSQL,ConnString,1,2 rs.AddNew rs("Category") = Category rs("CategoryString") = CategoryString rs("ComputerID") = ComputerID rs("EventCode") = EventCode rs("Logfile") = Logfile rs("MessageID") = MessageID rs("RecordNumber") = RecordNumber rs("SourceName") = SourceName rs("TimeGenerated") = TimeGenerated rs("TimeWritten") = TimeWritten rs("Eventtype") = Eventtype rs("NTUser") = User ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'InsertionStrings, Data and Eventtype are not queried. Eventtype 'is win2k3/xp only and the other two are arrays ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' rs.Update rs.Close

Set rs = Nothing End Sub

Front end code to view and manage Windows Events coming later....