More LogParser and Power Query fun. (A few weeks ago I did a similar thing with IIS Logs and Active Directory)
Event logs. Windows PC’s kick out a ton of events, and the Event Logs store them. While the event log viewer has gotten better with time, it is more of a quick glance than analyzing tool in my eyes. Let’s get to Excel.
I tried seven ways to Sunday to try to get Event Logs directly with Power Query, but exporting to text, csv, xml, etc from Event Logs just ends up in a mess once you get down to the details and try to get the event data from a record.
What I ended up doing was first kicking out all the data I wanted with LogParser
logparser -i:EVT -o:CSV “SELECT EventID,SourceName, TimeGenerated, Strings into c:results.csv FROM C:EventLog_Application.evtx”
Then pulling that data into Power Query. With Power Query, on the “Strings” column I just delimited by pipe (|) for the entire strings column (which in my case split it out to 6 varying columns) and then went about analyzing my data and pivoting/slicing/dicing.
let
Source = Csv.Document(File.Contents(“C:results.csv”),null,null,null,1252),
FirstRowAsHeader = Table.PromoteHeaders(Source),
ChangedType = Table.TransformColumnTypes(FirstRowAsHeader,{{“EventID”, type number}, {“SourceName”, type text}, {“TimeGenerated”, type datetime}, {“Strings”, type text}}),
SplitColumnDelimiter = Table.SplitColumn(ChangedType,”Strings”,Splitter.SplitTextByDelimiter(“|”),{“Strings.1”, “Strings.2”, “Strings.3”, “Strings.4”, “Strings.5”, “Strings.6”}),
ChangedType1 = Table.TransformColumnTypes(SplitColumnDelimiter,{{“Strings.1”, type text}, {“Strings.2”, type text}, {“Strings.3”, type text}, {“Strings.4”, type text}, {“Strings.5”, type text}, {“Strings.6”, type text}})
in
ChangedType1
I do wish Power Query could hit Event Logs and .evt/evtx files directly, Maybe it does and I am missing it.
