Home

Log Analytics Query - Azure Active Directory ExtendedProperties

%3CLINGO-SUB%20id%3D%22lingo-sub-135446%22%20slang%3D%22en-US%22%3ELog%20Analytics%20Query%20-%20Azure%20Active%20Directory%20ExtendedProperties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-135446%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20currently%20working%20on%20a%20query%20in%20Log%20Analytics%20which%20requires%20me%20to%20filter%20on%20properties%20which%20are%20in%20the%20ExtendedProperties%20field.%20See%20below%20example%2C%20I%20would%20like%20to%20use%20the%20%3CEM%3EExtendedProperties%5B0%5D.Value%3C%2FEM%3E%26nbsp%3Bproperty%20in%20my%20query.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20776px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F25334i126768FE42344A3D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22extendedproperties.png%22%20title%3D%22extendedproperties.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20someone%20point%20me%20to%20some%20tips%20on%20how%20to%20expand%20and%20filter%20on%26nbsp%3Bthis%20value%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-135446%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205713%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20Query%20-%20Azure%20Active%20Directory%20ExtendedProperties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205713%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EThe%20query%20could%20not%20be%20exactly%20replicated%20on%20our%20demo%20env.%20I%20assume%20you%20meant%20something%20similar%20to%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36266iE3E54B1E737B6012%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22multi-entities.png%22%20title%3D%22multi-entities.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhere%20each%20result%20has%20a%20set%20of%20Entities%20(parallel%20to%20the%20ExtendedProperties%20you%20mention)%20and%20in%20it%20can%20appear%20a%20number%20or%20rows%2C%20each%20with%20another%20Type%20(parallel%20to%20the%20source_ip%20you%20mention).%3C%2FP%3E%0A%3CP%3ETo%20parse%20that%2C%20I%20also%20used%20mvexpand%20and%20continued%20with%20extend%20and%20makeset%3A%3C%2FP%3E%0A%3CPRE%3ESecurityAlert%0A%7C%20mvexpand%20parsejson(Entities)%0A%7C%20extend%20entity_type%3DEntities%5B%22Type%22%5D%0A%7C%20summarize%20makeset(entity_type)%20by%20SystemAlertId%3C%2FPRE%3E%0A%3CP%3Eyou%20can%20try%20it%20%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAAwtOTS4tyiypdMxJLSrh5apRyC1LrShIzEtRKEgsKk7NKs7P03DNK8ksyUwt1gTJp1aUpAJlU0FilfEllQWptjD5aKUQIFcpFqSsuDQ3N7EosypVITcxO7U4tUQDSYemQlKlQnBlcUlqLthezxQABS7UsIcAAAA%253D%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%20The%20results%20would%20be%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20737px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36265i5DC738B8F5088575%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22makeset-results.png%22%20title%3D%22makeset-results.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20option%20is%20to%20apply%20a%20filter%20according%20to%20the%20entity_type%20(or%20source_ip)%20that%20interests%20you%2C%20like%20%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAAwtOTS4tyiypdMxJLSrh5apRyC1LrShIzEtRKEgsKk7NKs7P03DNK8ksyUwt1gTJp1aUpAJlU0FilfEllQWptjD5aKUQIFcpFqSsPCO1KBVZlYKtrYJSYnJyfmleiRIAS8vrvnYAAAA%253D%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20that%20helps...%3C%2FP%3E%0A%3CP%3ENoa%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-203529%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20Query%20-%20Azure%20Active%20Directory%20ExtendedProperties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-203529%22%20slang%3D%22en-US%22%3ESecurityAlert%3CBR%20%2F%3E%7C%20where%20Description%20contains%20%22Mandatory%20rule.%20Cannot%20be%20disabled.%22%3CBR%20%2F%3E%7C%20mvexpand%20parsejson(ExtendedProperties)%3CBR%20%2F%3E%7C%20extend%20source_ip%20%3D%20ExtendedProperties%3CBR%20%2F%3E%7C%20where%20source_ip%20!contains%20%22Hit%20Count%22%20and%20source_ip%20!contains%20%22Management%20URL%22%20and%20source_ip%20!contains%20%22ActionTaken%22%20and%20source_ip%20!contains%20%22resourceType%22%20and%20source_ip%20!contains%20%22ReportingSystem%22%20and%20source_ip%20!contains%20%22OccuringDatacenter%22%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-203528%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20Query%20-%20Azure%20Active%20Directory%20ExtendedProperties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-203528%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20does%20one%20go%20about%20parsing%20ExtendedProperties%20when%20one%20of%20its%20values%20is%20source%20ips%20and%20there%20are%20like%2010%20of%20them%20to%20one%20record%20per%20source%20ip%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-141222%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20Query%20-%20Azure%20Active%20Directory%20ExtendedProperties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-141222%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20understand%20your%20question%20correctly%2C%20here%20is%20a%20query%20that%20is%20doing%20what%20you%20are%20looking%20for%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOfficeActivity%20%7C%20where%20RecordType%20%3D%3D%20%22AzureActiveDirectory%22%20and%20Operation%20!contains%20%22device%22%3CBR%20%2F%3E%7C%20mvexpand%20parse_json(ExtendedProperties)%20%3CBR%20%2F%3E%7C%20extend%20PropName%20%3D%20ExtendedProperties.Name%2C%20PropValue%20%3D%20ExtendedProperties.Value%20%3CBR%20%2F%3E%7C%20where%20PropName%20%3D%3D%20%22Action%20client%20name%22%20and%20PropValue%20%3D%3D%20%22DirectorySync%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%2C%3C%2FP%3E%0A%3CP%3EMeir%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-136347%22%20slang%3D%22en-US%22%3ERe%3A%20Log%20Analytics%20Query%20-%20Azure%20Active%20Directory%20ExtendedProperties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-136347%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20access%20a%20specific%20item%20on%20the%20array%20using%20%5B1%5D%20or%20%5B2%5D%2C%20and%20then%20access%20an%20item%20named%20%22Value%22%20is%20through%20%22.Value%22%20as%20shown%20here%3A%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20second_item_value%20%3D%20your_array%5B%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3E%5D.Value%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EMore%20examples%20are%20available%20here%3A%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLearn%2FTutorials%2FWorking-with-JSON-and-data-structures%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLearn%2FTutorials%2FWorking-with-JSON-and-data-structures%3C%2FA%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-135916%22%20slang%3D%22en-US%22%3ERE%3A%20Log%20Analytics%20Query%20-%20Azure%20Active%20Directory%20ExtendedProperties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-135916%22%20slang%3D%22en-US%22%3ECopying%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F58802%22%20target%3D%22_blank%22%3E%40Satya%20Vel%3C%2FA%3E%20%3B%20Maybe%20he%20knows%20someone%20that%20can%20assist.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-135877%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20Log%20Analytics%20Query%20-%20Azure%20Active%20Directory%20ExtendedProperties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-135877%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20of%20all%2C%20thanks%20for%20the%20response%20%3A)%3C%2Fimg%3E%20Unfortunately%20this%20doesn't%20do%20the%20trick.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20total%20query%20I'm%20using%20now%20is%20as%20follows%3A%3C%2FP%3E%0A%3CPRE%3EOfficeActivity%20%7C%20where%20RecordType%20%3D%3D%20%22AzureActiveDirectory%22%20and%20Operation%20!contains%20%22device%22%0A%7C%20extend%20properties%20%3D%20parse_json(tostring(ExtendedProperties))%0A%7C%20where%20tostring(properties.Value)%20%3D%3D%20%22Privileged%20Role%20Administrator%22%3C%2FPRE%3E%0A%3CP%3EThis%20query%20results%20in%20the%20following%20output%3C%2FP%3E%0A%3CPRE%3E0%20records%20matched%20for%20the%20selected%20time%20range%3C%2FPRE%3E%0A%3CP%3EThe%20ExtendedProperties%20field%20is%20actually%20an%20array%20of%20values%20(see%20below%20picture)%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20708px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F25418i8769171A56C2550B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Azure_LA_Query_ExtendedProperties.png%22%20title%3D%22Azure_LA_Query_ExtendedProperties.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3BI'm%20trying%20to%20filter%20on%20the%20%22Value%22%20field%20in%20the%202nd%20entry%20of%20the%20array%2C%20but%20no%20luck%20so%20far.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-135643%22%20slang%3D%22en-US%22%3ERE%3A%20Log%20Analytics%20Query%20-%20Azure%20Active%20Directory%20ExtendedProperties%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-135643%22%20slang%3D%22en-US%22%3EHi%2C%20You%20should%20be%20able%20to%20do%20%7C%20extend%20properties%20%3D%0Aparse_json(tostring(ExtendedProperties)%20)%20%7C%20where%0Atostring(properties.Name)%20%3D%3D%20%22XYZ%22%20You%20might%20not%20be%20required%20to%20cast%0AName%20into%20string%20but%20it%20doesn't%20matter.%20Dan%3C%2FLINGO-BODY%3E
Bas van der Kruijssen - Live ID
New 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