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….

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server
4 comments on “VBScript & SQL: Centralize Windows Events in SQL Server
  1. Andrei says:

    What happens if a server that’s being monitored reboots? Would the script resume monitoring of that box automaticaly or would it rather need to be re-run?

  2. Daniyar says:

    Thanks for publishing it. I’m going to try it. It seems straght forward and easy to implement.

  3. venkatesh says:

    How can I use the scripts, and what is pre-requirement action I need to get success output

Leave a Reply to venkatesh Cancel reply