VBScript & SQL: Centralize Windows Events in SQL Server

 

» 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 topopulate 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 & "
ootcimv2")
                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 & "
ootcimv2")
    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....