stanl
04-06-2009, 01:02 PM
Might be of interest to anyone who does admin work or is generally interested in parsing event logs:
The code below is again, bare bones. If you have logparser installed, just open Excel pop-in the code and run
The SQL statement is more complicated since it works with the event log structure which is itself more complicated. But, if you looked at the code in the previous post, you might say to yourself - it's pretty much the same, just the specific input/output objects have changed. That is the beauty of Logparser. :thumb Yeah, I could have done the extra steps after importing the XML, like saving the file, and I have code that creates a Pivot - but that is a compiled exe, not a module. P.S. - the the eventual Excel output, the first 2 columns are generated by logparser and can be deleted.
Comments welcomed: Stan
Function getdate()
'obtains an event log date format (go back seven days)
getdate = Format(DateAdd("d", -7, Now), "yyyy-mm-dd") & " 00:00:00"
End Function
Function FileExists(FullFileName As String) As Boolean
' returns TRUE if the file exists
FileExists = Len(Dir(FullFileName)) > 0
End Function
Sub evtlog2xl()
Dim oLog, oInput, oOutput, cXLS As String, cXML As String, cSQL As String
'first delete any previous instance of files
cXLS = ThisWorkbook.Path & "\evt.xls"
If FileExists(cXLS) Then Kill cXLS
cXML = ThisWorkbook.Path & "\evt.xml"
If FileExists(cXML) Then Kill cXML
'next, execute logparser SQL to obtain events and output as xml
cSQL = "SELECT timegenerated, timewritten, computername, eventid, eventtypename, "
cSQL = cSQL & "eventcategory, eventcategoryname, sourcename, SID, Resolve_SID(SID), "
cSQL = cSQL & "message, strings, data INTO " & cXML & " FROM System,Security WHERE "
cSQL = cSQL & "eventtype IN (1;2) AND timegenerated > '" & getdate() & "' "
cSQL = cSQL & "ORDER BY timegenerated"
Set oLog = CreateObject("MSUtil.LogQuery")
Set oInput = CreateObject("MSUtil.LogQuery.EventLogInputFormat")
Set oOutput = CreateObject("MSUtil.LogQuery.XMLOutputFormat.1")
oLog.ExecuteBatch cSQL, oInput, oOutput
Set oInput = Nothing
Set oOutput = Nothing
Set oLog = Nothing
'finally, since Excel can load the xml -
Application.Workbooks.OpenXML Filename:=cXML, LoadOption:=xlXmlLoadImportToList
End Sub
'log file error types
'//////////////////////////////////////////////////////////////////////////////////////
'Error=2 An error event. This indicates a significant problem the user should
' know about' usually a loss of functionality or data.
'
'FailureAudit=4 A failure audit event. This indicates a security event that occurs when
' an audited access attempt fails; for example, a failed attempt to open a file.
'
'Information=0 An information event. This indicates a significant, successful operation.
'
'SuccessAudit=3 A success audit event. This indicates a security event that occurs when
' an audited access attempt is successful; for example, logging on successfully.
'
'Warning=1 A warning event. This indicates a problem that is not immediately significant,
' but that may signify conditions that could cause future problems.
'//////////////////////////////////////////////////////////////////////////////////////
The code below is again, bare bones. If you have logparser installed, just open Excel pop-in the code and run
The SQL statement is more complicated since it works with the event log structure which is itself more complicated. But, if you looked at the code in the previous post, you might say to yourself - it's pretty much the same, just the specific input/output objects have changed. That is the beauty of Logparser. :thumb Yeah, I could have done the extra steps after importing the XML, like saving the file, and I have code that creates a Pivot - but that is a compiled exe, not a module. P.S. - the the eventual Excel output, the first 2 columns are generated by logparser and can be deleted.
Comments welcomed: Stan
Function getdate()
'obtains an event log date format (go back seven days)
getdate = Format(DateAdd("d", -7, Now), "yyyy-mm-dd") & " 00:00:00"
End Function
Function FileExists(FullFileName As String) As Boolean
' returns TRUE if the file exists
FileExists = Len(Dir(FullFileName)) > 0
End Function
Sub evtlog2xl()
Dim oLog, oInput, oOutput, cXLS As String, cXML As String, cSQL As String
'first delete any previous instance of files
cXLS = ThisWorkbook.Path & "\evt.xls"
If FileExists(cXLS) Then Kill cXLS
cXML = ThisWorkbook.Path & "\evt.xml"
If FileExists(cXML) Then Kill cXML
'next, execute logparser SQL to obtain events and output as xml
cSQL = "SELECT timegenerated, timewritten, computername, eventid, eventtypename, "
cSQL = cSQL & "eventcategory, eventcategoryname, sourcename, SID, Resolve_SID(SID), "
cSQL = cSQL & "message, strings, data INTO " & cXML & " FROM System,Security WHERE "
cSQL = cSQL & "eventtype IN (1;2) AND timegenerated > '" & getdate() & "' "
cSQL = cSQL & "ORDER BY timegenerated"
Set oLog = CreateObject("MSUtil.LogQuery")
Set oInput = CreateObject("MSUtil.LogQuery.EventLogInputFormat")
Set oOutput = CreateObject("MSUtil.LogQuery.XMLOutputFormat.1")
oLog.ExecuteBatch cSQL, oInput, oOutput
Set oInput = Nothing
Set oOutput = Nothing
Set oLog = Nothing
'finally, since Excel can load the xml -
Application.Workbooks.OpenXML Filename:=cXML, LoadOption:=xlXmlLoadImportToList
End Sub
'log file error types
'//////////////////////////////////////////////////////////////////////////////////////
'Error=2 An error event. This indicates a significant problem the user should
' know about' usually a loss of functionality or data.
'
'FailureAudit=4 A failure audit event. This indicates a security event that occurs when
' an audited access attempt fails; for example, a failed attempt to open a file.
'
'Information=0 An information event. This indicates a significant, successful operation.
'
'SuccessAudit=3 A success audit event. This indicates a security event that occurs when
' an audited access attempt is successful; for example, logging on successfully.
'
'Warning=1 A warning event. This indicates a problem that is not immediately significant,
' but that may signify conditions that could cause future problems.
'//////////////////////////////////////////////////////////////////////////////////////