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?

8 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 

Re: Log Analytics Query - Azure Active Directory ExtendedProperties

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?

Re: Log Analytics Query - Azure Active Directory ExtendedProperties

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"

Re: Log Analytics Query - Azure Active Directory ExtendedProperties

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