There are cases where Microsoft tries to make our live a bit harder than it needs to be. One thing I always struggled with in the old SCOM days and also in the present Azure Log Analytics (ALA) days are Windows event descriptions.
I just had a need to get the event description for a specific Windows event in Azure Log Analytics. In my case it was Event ID 1116 in the Microsoft-Windows-Windows Defender/Operational log. Something like this…
Usually we would expect to get some column in ALA which we just could project the data and used it in your query. Well this is just not the case, if I run a query to fetch the data it look like this…
…some usable data can be found in RenderedDescription, EventData or in ParameterXml column.
If we just have a close look, RenderedDescription has some wired characters and no real delimiter to distinguish the data. ParameterXML column doesn’t work, because we cannot figure out what data belongs to what field. Everything seems to be <Param>Some funny data</Param>. The only chance we have to parse the data, is to use the EventData column. It seems to be real XML data. If we try to explore it, using an online viewer, we will get the result…
…this means we need to play with the built-in ALA XML functions.
To make things short, I played around and a reasonable solution was to parse the XML using the parse_xml() function, then we just convert it into a string, because the next function needs a string as input. In my case I wanted to extract data and decided to extract data from an JSON object. Luckily there is a function called extractjson() which allows us to extract JSON data easily.
So my final solution looks like this to extract the following event description data:
- ThreatName
- DetectionTime
- Severity
- ErrorDescription
- DetectionUser
- Action
The final query looks like this…
Event
| where EventID == 1116
| where Source == "Microsoft-Windows-Windows Defender"
| extend eventData = tostring(parse_xml(EventData))
| project eventData
| extend ThreatName = extractjson("$['DataItem']['EventData']['Data'][7]['#text']", eventData )
| extend DetectionTime = extractjson("$['DataItem']['EventData']['Data'][3]['#text']", eventData )
| extend Severity = extractjson("$['DataItem']['EventData']['Data'][9]['#text']", eventData )
| extend ErrorDescription = extractjson("$['DataItem']['EventData']['Data'][33]['#text']", eventData )
| extend DetectionUser = extractjson("$['DataItem']['EventData']['Data'][19]['#text']", eventData )
| extend Action = extractjson("$['DataItem']['EventData']['Data'][37]['#text']", eventData )
| project ThreatName, DetectionTime, Severity, ErrorDescription,DetectionUser,Action
The problem is, that the JSON object has no named properties, there are just #text and @Name as you can see here…
…this makes life really hard to extract data. But using the above query, I think it is a reasonable way to get your specific fields.
I hope I could help you and you enjoyed this post. If you think you have a much better / easier way let me know and leave a comment. I am very interested in your solution!
