SOLVED

How to find the old and new values of the proxyAddresses property change.

%3CLINGO-SUB%20id%3D%22lingo-sub-1230005%22%20slang%3D%22en-US%22%3EHow%20to%20find%20the%20old%20and%20new%20values%20of%20the%20proxyAddresses%20property%20change.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1230005%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20does%20one%20pull%20out%20the%20oldValue%20and%20newValue%20of%20a%20specific%20index%20from%20the%20AuditLog%2C%26nbsp%3B%3CSPAN%3ETargetResources%5B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%5D%5B%3C%2FSPAN%3E%3CSPAN%3E'modifiedProperties'%3C%2FSPAN%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%20object%2C%20when%20multiple%20attributes%20have%20been%20changed%20and%20you%20don't%20know%20the%20index%20number%20of%20the%20property%20you%20are%20specifically%20looking%20for%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20property%20always%20had%20a%200%20index%2C%20that%20would%20be%20easy%2C%20but%20sometimes%20it's%20index%200%2C%20sometimes%20index%203%2C%20sometimes%20index%204%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20find%20the%20old%20and%20new%20values%20from%20the%20proxyAddresses%20property%20change.%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EI%20would%20like%20to%20do%20something%20like%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EAuditLogs%0A%7C%20extend%20proxyAddresses%20%3D%20parse_json(TargetResources%5B0%5D%5B'modifiedProperties'%5D%20%7C%20where%20%22displayName%22%20%3D%3D%20%22proxyAddresses%22)%0A%7C%20extend%20newValue%20%3D%20proxyAddresses.newValue%0A%7C%20extend%20oldValue%20%3D%20proxyAddresses.oldValue%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3EThanks.%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1230005%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1230822%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20find%20the%20old%20and%20new%20values%20of%20the%20proxyAddresses%20property%20change.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1230822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F4821%22%20target%3D%22_blank%22%3E%40Andrew%20Huddleston%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20should%20turn%20all%20matches%20to%20index%200%20(note%3A%20I%20only%20did%20a%20quick%20test%20in%20this%20method!!!!!)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20srch%20%3D%20%22proxyAddresses%22%3B%20%20%20%20%20%20%20%20%2F%2F%20search%20for%20%0Asearch%20in%20(AuditLogs)%20srch%20%20%20%20%20%20%20%20%20%20%2F%2F%20Table%20to%20search%20in%0A%7C%20evaluate%20narrow()%0A%7C%20where%20Value%20has%20srch%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20also%20try%20%22has%22%20for%20better%20efficiency%20rather%20than%20%22contains%22%0A%7C%20extend%20newValue%20%3D%20tostring(parse_json(tostring(parse_json(tostring(parse_json(Value)%5B0%5D.modifiedProperties))%5B0%5D.newValue))%5B0%5D)%20%0A%7C%20extend%20oldValue%20%3D%20tostring(parse_json(tostring(parse_json(tostring(parse_json(Value)%5B0%5D.modifiedProperties))%5B0%5D.oldValue))%5B0%5D)%20%0A%7C%20summarize%20count()%20by%20Column%2C%20txtFound%20%3D%20srch%20%2C%20oldValue%2C%20newValue%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThe%20hard%20works%20is%20done%20in%20lines%201-4%2C%20you%20may%20want%20to%20just%20run%20those%20lines%20first.%26nbsp%3B%20I%20use%20this%20a%20lot%20(lines%201-4)%20to%20find%20in%20which%20columns%20I%20get%20a%20data%20match%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ee.g.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Elet%20srch%20%3D%20%22proxyAddresses%22%3B%20%20%20%20%20%20%20%20%2F%2F%20search%20for%20%0Asearch%20in%20(AuditLogs)%20srch%20%20%20%20%20%20%20%20%20%20%2F%2F%20Table%20to%20search%20in%0A%7C%20evaluate%20narrow()%0A%7C%20where%20Value%20has%20srch%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2F%20also%20try%20%22has%22%20for%20better%20efficiency%20rather%20than%20%22contains%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EShows%20that%20%3CSTRONG%3ETargetResources%3C%2FSTRONG%3E%20has%20%22proxyAddresses%22%20in%20it%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3ERow%3C%2FTH%3E%0A%3CTH%3EColumn%3C%2FTH%3E%0A%3CTH%3EValue%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E0%3C%2FTD%3E%0A%3CTD%3ETargetResources%3C%2FTD%3E%0A%3CTD%3E%5B%7B%22displayName%22%3Anull%2C%22modifiedProperties%22%3A%5B%7B%22displayName%22%3A%22AccountEnabled%22%2C%22oldValue%22%3A%22%5B%5D%22%2C%22newValue%22%3A%22%5Btrue%5D%22%7D%2C%7B%22displayName%22%3A%22CreationType%22%2C%22oldValue%22%3A%22%5B%5D%22%2C%22newValue%22%3A%22%3CBR%20%2F%3E....%3CBR%20%2F%3Edata%20omitted%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20Clive%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1231872%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20find%20the%20old%20and%20new%20values%20of%20the%20proxyAddresses%20property%20change.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1231872%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%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%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERunning%20the%20query%20as%20is%2C%20got%20me%20close%20i%20think%2C%20really%20appreciate%20it%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAndrew%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

How does one pull out the oldValue and newValue of a specific index from the AuditLog, TargetResources[0]['modifiedProperties'] object, when multiple attributes have been changed and you don't know the index number of the property you are specifically looking for?

 

If the property always had a 0 index, that would be easy, but sometimes it's index 0, sometimes index 3, sometimes index 4 etc.

 

I am trying to find the old and new values from the proxyAddresses property change.

 
I would like to do something like;
 

 

AuditLogs
| extend proxyAddresses = parse_json(TargetResources[0]['modifiedProperties'] | where "displayName" == "proxyAddresses")
| extend newValue = proxyAddresses.newValue
| extend oldValue = proxyAddresses.oldValue

 

Thanks.
2 Replies

@Andrew Huddleston 

 

This should turn all matches to index 0 (note: I only did a quick test in this method!!!!!)

 

let srch = "proxyAddresses";        // search for 
search in (AuditLogs) srch          // Table to search in
| evaluate narrow()
| where Value has srch              // also try "has" for better efficiency rather than "contains"
| extend newValue = tostring(parse_json(tostring(parse_json(tostring(parse_json(Value)[0].modifiedProperties))[0].newValue))[0]) 
| extend oldValue = tostring(parse_json(tostring(parse_json(tostring(parse_json(Value)[0].modifiedProperties))[0].oldValue))[0]) 
| summarize count() by Column, txtFound = srch , oldValue, newValue

The hard works is done in lines 1-4, you may want to just run those lines first.  I use this a lot (lines 1-4) to find in which columns I get a data match 

 

e.g.

 

let srch = "proxyAddresses";        // search for 
search in (AuditLogs) srch          // Table to search in
| evaluate narrow()
| where Value has srch              // also try "has" for better efficiency rather than "contains"

 

Shows that TargetResources has "proxyAddresses" in it

 

Row Column Value
0 TargetResources [{"displayName":null,"modifiedProperties":[{"displayName":"AccountEnabled","oldValue":"[]","newValue":"[true]"},{"displayName":"CreationType","oldValue":"[]","newValue":"
....
data omitted 

 

Thanks Clive  

Best Response confirmed by Andrew Huddleston (Frequent Contributor)
Solution

Thank you @Clive Watson 

 

Running the query as is, got me close i think, really appreciate it :)

 

Andrew