Forum Discussion
Log Analytics Query - Azure Active Directory ExtendedProperties
MicrosoftHi,
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"
Thanks,
Meir
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?
- SecurityAlert
| where Description contains "Mandatory rule. Cannot be disabled."
| mvexpand parsejson(ExtendedProperties)
| extend source_ip = ExtendedProperties
| where source_ip !contains "Hit Count" and source_ip !contains "Management URL" and source_ip !contains "ActionTaken" and source_ip !contains "resourceType" and source_ip !contains "ReportingSystem" and source_ip !contains "OccuringDatacenter"- Noa Kuperberg
Hi,
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...
Noa
