Consolidating security events to database

[tr6boy]

I would like to use WMI to pipe security events from all
DCs to a single SQL database so they are easier to
search. It's working, except for the most needed
field, "message" which is a "catch-all" for most of the
per-event data, such as "Target Account".

I can capture the whole "message" contents in a single
large varchar field, but because of the tabs and extra
formatting embedded in it, it's hard to make it useful
for searching. Same result if I save the event log to a
CSV ahead of time.

If anyone has tried this, I'd be interested in your
methods for dealing with the "message" field.

Thanks,

 

[Eric Fitzgerald [MSFT]]

WMI has access to the individual insertion strings (I've only tried this
script on W2K3, YMMV):

On Error Resume Next

strComputer = "."

Set objWMIService =
GetObject("winmgmts:{impersonationLevel=impersonate,(security)}\\" &
strComputer & "\root\cimv2")

'Set colItems = objWMIService.ExecQuery("Select * from Win32_NTLogEvent
Where Logfile='Security' and EventIdentifier=680",,48)
'Set colItems = objWMIService.ExecQuery("Select * from Win32_NTLogEvent
Where Logfile='Security' and EventIdentifier=528 and
InsertionStrings(4)='2'",,48)
Set colItems = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where
Logfile='Security'",,48)

For Each objItem in colItems
thedate = GetFileTime(objItem.TimeGenerated)
Wscript.Echo "Timestamp: " & thedate.Year
Wscript.Echo "User: " & objItem.User
Wscript.Echo "Computer: " & objItem.ComputerName
Wscript.Echo "Type: " & objItem.Type
Wscript.Echo "Source: " & objItem.SourceName
Wscript.Echo "Category: " & objItem.CategoryString
Wscript.Echo "Event ID: " & objItem.EventIdentifier
Wscript.Echo "Description:"

for each insertString in objItem.InsertionStrings
WScript.Echo " " & insertString
next

Wscript.Echo

Next

--
Eric Fitzgerald
Program Manager, Windows Auditing
Microsoft Corporation

The above message is provided "AS-IS" with no warranties, and confers no
rights.