Azure Log Analytics: Using the Parse operator
Updated: to include some screenshots (as thus wasn’t working the other day)
Today I had to look at getting some data from SecurityEvent. This is using the new Log Analytics query language and the Advanced Analytics portal.
I was looking at EventID: 5061, but you can use any EventID you like, e.g.
SecurityEvent
| where EventID == 5061
This returns all the normal fields such as Computer, TimeGenerated, EventID, and EventData etc.. It was the EVENTDATA I was interested in:
SecurityEvent
| where EventID == 5061
| project TimeGenerated, Computer, EventData, EventID
There were quite a lot of Events so I also used the Limit command to get 10 results to speed up the work, this is a best practise, which makes the query look like this:
SecurityEvent
| where EventID == 5061
| project TimeGenerated, Computer, EventData, EventID
| limit 10
What I really wanted was a piece of data in the EventData, for that I used the parse command. The syntax examples are a little light on parse hence me writing this post to give another common example. Please take a look at Parse for full syntax help in the Language Reference: https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators/parse-operator
EventData contains a lot of info (this is a shortened paste)
The data I wanted was after "SubjectUserName" and contained between the ">" and "<" symbols.
There wasn't a like for like example in the help for this so after a little trial and error I got to this (from an example I'd seen elsewhere, apologies but I cant remember where now!):
SecurityEvent
| where EventID == 5061
| project TimeGenerated, Computer, EventData, EventID
| limit 10
|
parse EventData with * "SubjectUserName" SubjectUserNameValue " " *
This sort of worked but the new table I created "SubjectUserNameValue" had these results ">LOCAL, it was stopping at the space between LOCAL and SERVICE, and I wanted the full value of "LOCAL SERVICE"
Changing the last line to this allowed me to search until the "<" character was detected.
| parse EventData with * "SubjectUserName" SubjectUserNameValue "<" *
However the result still contained a leading " and > symbol.
| ">LOCAL SERVICE |
You need to wrap the SubjectUserName (in my example) in single quotes if you wish to include the removal of symbols e.g.
| parse EventData with * ' "SubjectUserName"> ' SubjectUserName '<' *
So the final syntax is:
SecurityEvent
| where EventID == 5061
| project TimeGenerated, Computer, EventData, EventID
| parse EventData with * '"SubjectUserName">' SubjectUserName '<' *
| where isnotempty (SubjectUserName)
| project SubjectUserName
Note: I had removed the Limit and also added a check to only display non empty values (isnotempty); if you have lots of data you might want to look at Limit, Top or Take to reduce the results returned.
Comments
- Anonymous
September 06, 2018
This is a helpful start, thank you! Given the fact much of the event log data is in XML, and log analytics have a major focus on windows event logs, I hope to see more native XML handling in the future. Hopefully the powers that be are very aware of this famous caution against parsing HTML/XML with regex: https://stackoverflow.com/questions/1732348/regex-match-open-tags-except-xhtml-self-contained-tags- Anonymous
September 06, 2018
The comment has been removed
- Anonymous