Automation: EVT Logs to Excel

[stanl]

I am trying to automate the procedure that appeared in this blog -
http://www.harper.no/valery/PermaLin...dce018250.aspx

LogParser 2.2 has a COM interface, so it was easy enough to re-write the command line argumenets with CreateObject() and load the resultant XML into Excel (2003 - not sure it would work with earlier versions). The attached zip contains a workbook created from just a sample of .evt and it represents the procedure right up until the creation of the Pivot Tables from the data. I tried to record a macro to get code snippets - w/out success. Would appreciate if someone could help pick up the ball from there.

I will eventually code the automation as a .wsc rather than vba code in an existing workbook, but VBA and vbscript are cousins... and easily converted.

and I hope everyone that contributes to this forum has a happy and productive 2007.

Stan

[XLGibbs]

[vba]
Sub MakemeAPivot(),
Dim wsData As Worksheet
Dim rngData As Range
Dim wb As Workbook

Set wb = ActiveWorkbook
Set wsData = Sheets("Sheet1")
Set rngData = wsData.Cells(1, 1).CurrentRegion

rngDataString = wsData.Name & "!" & rngData.Address
With wb
.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
rngDataString).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1"
With ActiveSheet
.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
.PivotTables("PivotTable1").AddFields RowFields:="EventTypeName" 'adds EventTypeName as row
.PivotTables("PivotTable1").PivotFields("EventTypeName"). _
Orientation = xlDataField 'adds default Count of EventTypeName as Data field
End With
End With
Set wb = Nothing
Set rngData = Nothing
Set wsData = Nothing

End Sub
[/vba]

THis would generate a new sheet with a pivot table..real basic...but is this what you were looking for?

[stanl]

THis would generate a new sheet with a pivot table..real basic...but is this what you were looking for?

I'm sure it will work fine once integrated into the existing code. I feel a little lame asking for help on something I probably should have figured out for myself Thank you. Stan

[XLGibbs]

I wouldn't think that it was lame to ask at all, I was just unclear on exactly what you were looking for...

Depending on how you want the fields laid out and such...I added a row field and a calculated value field for samples...

I am not sure I would have figured out getting the data into excel like you did, so don't feel lame at all.

Even that code can probably be better, it was just sort of thrown together ...

[stanl]

Well, it worked like a charm- first try - I added code to rename sheets and delete unncessary ones if they exist [attached]. I just would never have thought of using the PivotWizard in code, the way you did. I realize this is a VBA forum, and my final product is not exactly VBA, but I can post code 90% VBA is anyone is interested. BTW: it took 3-4 seconds to parse and produce the Pivot. Thanks again. Stan

[XLGibbs]

Well VB6, and VBA are so close, code is always useful as reference. Glad my mud against the wall stuck for you.

[stanl]

Well VB6, and VBA are so close, code is always useful as reference. Glad my mud against the wall stuck for you.

well... basic code for creating the XML file

Sub evtlogs()
cXML = "C:\temp\events.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 *.evt WHERE "
cSQL = cSQL & "eventtype IN (1;2) AND timegenerated > '2006-01-01 00:00:00' "
cSQL = cSQL & "ORDER BY timegenerated"
Set oLog = CreateObject("MSUtil.LogQuery")
oLog.maxParseErrors = 100
Set oInput = CreateObject("MSUtil.LogQuery.EventLogInputFormat")
Set oOut = CreateObject("MSUtil.LogQuery.XMLOutputFormat.1")
oLog.ExecuteBatch cSQL, oInput, oOut
If oLog.lastError <> 0 Then
   For Each strMessage In oLog.errorMessages
      'display error message
   Next
End If
Set oOut = Nothing
Set oInput = Nothing
Set oLog = Nothing
End Sub

then you would use the Workbook .OpenXML(Filename:="cXML",LoadOption:=xlXmlLoadImportToList) then run your code afterwards. As I said, I wrote this in a scripting language that is external (and can be compiled as a standalone exe, or called as a UDF within an exe). It has a few nuances [like] code must make explicit references, functions use ( and ), positional parameters are coded differently. I took your pivot sub and massaged it to my scripting. After my code creates an Excel instance with oXL = CreateObject("Excel.Application") I do this....

oXL.WorkBooks.OpenXML(::Filename="%cXML%",LoadOption=xlXmlLoadImportToList)
oWS=oXL.ActiveSheet
oWS.Name="Data"
;remove 2 columns not really needed - they were added by LogParser
oWS.Columns("A:A").Select
oXL.Selection.Delete(::Shift=xlToLeft)
oXL.Selection.Delete(::Shift=xlToLeft)
oWS.Cells(1,1).Select
oRange=oWS.Cells(1, 1).CurrentRegion
cRange = StrCat(oWS.Name,"!",oRange.Address)
oXL.ActiveWorkBook.PivotCaches.Add(::SourceType=xlDatabase, SourceData=cRange).CreatePivotTable(::TableDestination="", TableName="Events") 
oWS=oXL.ActiveSheet
oXL.ActiveWindow.DisplayGridlines = @False
oWS.Name="Pivot"
oWS.PivotTableWizard(::TableDestination=oXL.ActiveSheet.Cells(3, 1)) 
oWS.PivotTables("Events").AddFields(::RowFields="EventTypeName")
oWS.PivotTables("Events").PivotFields("EventTypeName").Orientation = xlDataField 
ForEach s in oXL.ActiveWorkbook.Sheets
   If !StrIndex("Data/Pivot",s.Name,0,@FWDSCAN) Then s.Delete
Next
oXL.ActiveWorkbook.Sheets("Pivot").Select
oXL.ActiveWorkbook.Sheets("Pivot").Move(::After=oXL.ActiveWorkbook.Sheets("Data"))
oXL.ActiveWorkbook.Sheets("Data").Activate()
oXL.ActiveWorkbook.SaveAs(cXLS)
oRange=0
oWS=0
;uncomment next line to close and exit excel
;oXL.Quit()
oXL=0

pretty similar... code is code... it is the results that matter. Stan