Forum Discussion
Log Analytics Query - Azure Active Directory ExtendedProperties
Hello,
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[0].Value property in my query.
Can someone point me to some tips on how to expand and filter on this value?
8 Replies
- Meir_Mendelovich
Microsoft
Hi,
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
You can access a specific item on the array using [1] or [2], and then access an item named "Value" is through ".Value" as shown here:
extend second_item_value = your_array[1].ValueMore examples are available here: - Copying SatyaVel ; Maybe he knows someone that can assist.
Dan HadariHi, You should be able to do | extend properties = parse_json(tostring(ExtendedProperties) ) | where tostring(properties.Name) == "XYZ" You might not be required to cast Name into string but it doesn't matter. DanFirst 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.
