Dec 08 2017 08:12 AM
Dec 08 2017 08:12 AM
I'm currently working on a query in Log Analytics which requires me to filter on properties which are in the ExtendedProperties field. See below example, I would like to use the ExtendedProperties.Value property in my query.
Can someone point me to some tips on how to expand and filter on this value?
Dec 09 2017 05:27 AM
Dec 11 2017 12:53 AM
First of all, thanks for the response :) Unfortunately this doesn't do the trick.
The total query I'm using now is as follows:
OfficeActivity | where RecordType == "AzureActiveDirectory" and Operation !contains "device" | extend properties = parse_json(tostring(ExtendedProperties)) | where tostring(properties.Value) == "Privileged Role Administrator"
This query results in the following output
0 records matched for the selected time range
The ExtendedProperties field is actually an array of values (see below picture)
I'm trying to filter on the "Value" field in the 2nd entry of the array, but no luck so far.
Dec 11 2017 04:10 AM
Dec 12 2017 05:52 AM - edited Dec 12 2017 05:53 AM
You can access a specific item on the array using  or , and then access an item named "Value" is through ".Value" as shown here:
Jan 03 2018 05:07 AM
If I understand your question correctly, here is a query that is doing what you are looking for:
OfficeActivity | where RecordType == "AzureActiveDirectory" and Operation !contains "device"
| mvexpand parse_json(ExtendedProperties)
| extend PropName = ExtendedProperties.Name, PropValue = ExtendedProperties.Value
| where PropName == "Action client name" and PropValue == "DirectorySync"
Jun 12 2018 03:25 PM
How does one go about parsing ExtendedProperties when one of its values is source ips and there are like 10 of them to one record per source ip?
Jun 12 2018 03:26 PM
Jun 19 2018 08:28 AM
The query could not be exactly replicated on our demo env. I assume you meant something similar to this:
where each result has a set of Entities (parallel to the ExtendedProperties you mention) and in it can appear a number or rows, each with another Type (parallel to the source_ip you mention).
To parse that, I also used mvexpand and continued with extend and makeset:
SecurityAlert | mvexpand parsejson(Entities) | extend entity_type=Entities["Type"] | summarize makeset(entity_type) by SystemAlertId
you can try it here. The results would be:
Another option is to apply a filter according to the entity_type (or source_ip) that interests you, like here.
I hope that helps...