Home

Log Analytics Query - Azure Active Directory ExtendedProperties

Highlighted
Bas van der Kruijssen - Live ID
New Contributor

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.

 

extendedproperties.png

 

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

5 Replies

RE: Log Analytics Query - Azure Active Directory ExtendedProperties

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

Re: RE: Log Analytics Query - Azure Active Directory ExtendedProperties

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.

RE: Log Analytics Query - Azure Active Directory ExtendedProperties

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

Re: Log Analytics Query - Azure Active Directory ExtendedProperties

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:

Re: Log Analytics Query - Azure Active Directory ExtendedProperties

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