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.

Download SQL script for creating the necessary tables

/*
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

Download VBS code for populating the database with domain computer names

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

Download the actual monitoring code

''''''''''''''''''''''''''''''''''''''''''''''''''''''
'    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 PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. 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

Your email address will not be published. Required fields are marked *

*