Home

Using KQL queries to dive into dynamic arrays Azure Log Analytics

%3CLINGO-SUB%20id%3D%22lingo-sub-741771%22%20slang%3D%22en-US%22%3EUsing%20KQL%20queries%20to%20dive%20into%20dynamic%20arrays%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741771%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20running%20this%20command%20to%20break%20out%20the%20dynamic%20arrays%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIntuneAuditLogs%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(7d)%3CBR%20%2F%3E%7C%20extend%20propertiesJson%20%3D%20todynamic(Properties)%3CBR%20%2F%3E%7C%20extend%20propertiesTargets%20%3D%20todynamic(propertiesJson.Targets)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20have%20these%20arrays%20that%20appear%20to%20have%20these%20index%20numbers%20and%20data%20within%20them%20is%20different%20between%20each%20data%20type%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20704px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122498i23D38CB582B65ACF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22array.png%22%20title%3D%22array.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20would%20I%20go%20about%20referencing%20each%20of%20these%20and%20their%20subsequent%20values%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-741771%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ekql%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELog%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-741994%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20KQL%20queries%20to%20dive%20into%20dynamic%20arrays%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35655%22%20target%3D%22_blank%22%3E%40Chris%20Blackburn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20know%20the%20Index%20number%20and%20field%2C%20then%20you%20can%20modify%20a%20query%20like%20this%20one%20to%20suit%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%3ESecurityAlert%20%0A%2F%2F%7C%20where%20DisplayName%20%3D%3D%20%22Detected%20suspicious%20DNS%20resolution%22%20%0A%7C%20extend%20entities%20%3D%20todynamic(Entities)%20%0A%7C%20project%20AlertName%20%2C%0A%20%20%20%20%20%20%20%20%20%20TimeGenerated%2C%0A%20%20%20%20%20%20%20%20%20%20Description%20%3D%20parse_json(entities%5B0%5D.HostName)%20%2C%0A%20%20%20%20%20%20%20%20%20%20osFamily%20%3Dparse_json(entities%5B1%5D.OSFamily)%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-742845%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20KQL%20queries%20to%20dive%20into%20dynamic%20arrays%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-742845%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3Bextending%20the%20commands%20to%20expand%20out%20index%200%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIntuneAuditLogs%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%20ago(7d)%3CBR%20%2F%3E%7C%20extend%20propertiesJson%20%3D%20todynamic(Properties)%3CBR%20%2F%3E%7C%20extend%20propertiesTargets%20%3D%20todynamic(propertiesJson.Targets)%3CBR%20%2F%3E%7C%20extend%20mydisc%20%3D%20todynamic(propertiesTargets%5B0%5D.ModifiedProperties)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I've%20seen%20is%20as%20I%20continue%20to%20dig%20deeper%20into%20the%20properties%2C%20the%20ModifiedProperties%20field%20varies%20based%20on%20the%20specific%20operation%2C%20which%20makes%20it%20painful%20to%20determine%20the%20values%20I%20can%20consistently%20pull%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20711px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122579i10CBEE6EC3B95BEE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22array2.png%22%20title%3D%22array2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-743638%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20KQL%20queries%20to%20dive%20into%20dynamic%20arrays%20Azure%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-743638%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35655%22%20target%3D%22_blank%22%3E%40Chris%20Blackburn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20not%20familiar%20with%20the%20Intune%20data%2C%20this%20maybe%20a%20question%20for%20the%20Intune%20team%20-%20in%20case%20they%20have%20some%20plans%20for%20standardizing%20this%20data%2Ffields%2C%20or%20so%20they%20get%20visibility%3F%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FMicrosoft-Intune%2Fbd-p%2FMicrosoft-Intune%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FMicrosoft-Intune%2Fbd-p%2FMicrosoft-Intune%3C%2FA%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Chris Blackburn
New Contributor

I'm running this command to break out the dynamic arrays

 

IntuneAuditLogs
| where TimeGenerated > ago(7d)
| extend propertiesJson = todynamic(Properties)
| extend propertiesTargets = todynamic(propertiesJson.Targets)

 

But I have these arrays that appear to have these index numbers and data within them is different between each data type:

 

array.png

 

How would I go about referencing each of these and their subsequent values?

3 Replies

@Chris Blackburn 

 

If you know the Index number and field, then you can modify a query like this one to suit?

SecurityAlert 
//| where DisplayName == "Detected suspicious DNS resolution" 
| extend entities = todynamic(Entities) 
| project AlertName ,
          TimeGenerated,
          Description = parse_json(entities[0].HostName) ,
          osFamily =parse_json(entities[1].OSFamily)

@Clive Watson extending the commands to expand out index 0

 

IntuneAuditLogs
| where TimeGenerated > ago(7d)
| extend propertiesJson = todynamic(Properties)
| extend propertiesTargets = todynamic(propertiesJson.Targets)
| extend mydisc = todynamic(propertiesTargets[0].ModifiedProperties)

 

What I've seen is as I continue to dig deeper into the properties, the ModifiedProperties field varies based on the specific operation, which makes it painful to determine the values I can consistently pull

 

array2.png

Hi @Chris Blackburn 

 

I'm not familiar with the Intune data, this maybe a question for the Intune team - in case they have some plans for standardizing this data/fields, or so they get visibility?  https://techcommunity.microsoft.com/t5/Microsoft-Intune/bd-p/Microsoft-Intune