Log Analytics Query - Azure Active Directory ExtendedProperties

Copper Contributor

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.

 

extendedproperties.png

 

Can someone point me to some tips on how to expand and filter on this value?

8 Replies
Hi, 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. Dan

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)Azure_LA_Query_ExtendedProperties.png

 I'm trying to filter on the "Value" field in the 2nd entry of the array, but no luck so far.

Copying @Satya Vel ; Maybe he knows someone that can assist.

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].Value
 
More examples are available here:

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"

Hi,

The query could not be exactly replicated on our demo env. I assume you meant something similar to this:

multi-entities.png

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:
makeset-results.png

 

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