SOLVED

Querying AAD Audit Logs

%3CLINGO-SUB%20id%3D%22lingo-sub-872364%22%20slang%3D%22en-US%22%3EQuerying%20AAD%20Audit%20Logs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-872364%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20piping%20my%20AAD%20audit%20logs%20to%20Log%20Analytics%20through%20the%20Diagnostic%20Logs%2C%20and%20then%20I%20want%20to%20set%20up%20some%20alerts%20if%20users%20are%20added%20to%20certain%20administrator%20roles..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20got%20this%20far%2C%20but%20this%20returns%20nothing%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EAuditLogs%0A%7C%20where%20Category%20%3D%3D%20%22RoleManagement%22%20%0A%7C%20extend%20PropertiesJSON%20%3D%20parse_json(TargetResources)%0A%7C%20extend%20role%20%3D%20PropertiesJSON%5B0%5D.modifiedProperties%5B1%5D%5B'newValue'%5D%0A%7C%20where%20role%20%3D%3D%20'SharePoint%20Service%20Administrator'%0A%7C%20project%20role%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20because%20the%20value%20I%20am%20searching%20for%20appears%20to%20be%20an%20Array%20of%20characters%2C%20rather%20than%20a%20string..%26nbsp%3B%20(same%20code%2C%20just%20taking%20out%20the%20filter)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EAuditLogs%0A%7C%20where%20Category%20%3D%3D%20%22RoleManagement%22%20%0A%7C%20extend%20PropertiesJSON%20%3D%20parse_json(TargetResources)%0A%7C%20extend%20role%20%3D%20PropertiesJSON%5B0%5D.modifiedProperties%5B1%5D%5B'newValue'%5D%0A%7C%20project%20role%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20294px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133314i872819F40C72E990%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Screen%20Shot%202019-09-24%20at%204.23.45%20PM.png%22%20title%3D%22Screen%20Shot%202019-09-24%20at%204.23.45%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAny%20ideas%20how%20I%20can%20search%20for%20a%20term%20that%20is%20stored%20in%20the%20data%20like%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-872364%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-873136%22%20slang%3D%22en-US%22%3ERe%3A%20Querying%20AAD%20Audit%20Logs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873136%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414328%22%20target%3D%22_blank%22%3E%40dave8thomas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20just%20use%26nbsp%3B%20'%26nbsp%3B%20%26nbsp%3B'%20around%20the%20string%20as%20there%20are%20%22quotes%22%20in%20the%20returned%20data%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3EE.g.%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EAuditLogs%0A%7C%20where%20Category%20%3D%3D%20%22RoleManagement%22%20%0A%7C%20extend%20PropertiesJSON%20%3D%20parse_json(TargetResources)%0A%7C%20extend%20role%20%3D%20PropertiesJSON%5B0%5D.modifiedProperties%5B1%5D%5B'newValue'%5D%20%0A%7C%20where%20role%20%3D%3D%20'%22Company%20Administrator%22'%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%20cleanup%20the%20returned%20data%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EAuditLogs%0A%7C%20where%20Category%20%3D%3D%20%22RoleManagement%22%20%0A%7C%20extend%20PropertiesJSON%20%3D%20parse_json(TargetResources)%0A%7C%20extend%20role%20%3D%20trim(%40%22%5B%5E%5Cw%5D%2B%22%2C%20tostring(PropertiesJSON%5B0%5D.modifiedProperties%5B1%5D%5B'newValue'%5D)%20)%0A%7C%20where%20role%20%3D%3D%20%22Company%20Administrator%22%0A%0A%2F%2F%20trims%20all%20non-word%20characters%20from%20start%20and%20end%20of%20the%20string%0A%2F%2F%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Ftrimfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Ftrimfunction%3C%2FA%3E%20%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-875128%22%20slang%3D%22en-US%22%3ERe%3A%20Querying%20AAD%20Audit%20Logs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-875128%22%20slang%3D%22en-US%22%3EAwesome%2C%20thanks%20Clive!!%20It's%20the%20simple%20things%20in%20life%20..%20like%20quotes!%20%3A)%3C%2Fimg%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-876182%22%20slang%3D%22en-US%22%3ERE%3A%20Querying%20AAD%20Audit%20Logs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-876182%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22http%3A%2F%2Fwww.computerwali.com%2Fdownload-free-movies-from-movies-counter%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.computerwali.com%2Fdownload-free-movies-from-movies-counter%2F%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-879974%22%20slang%3D%22en-US%22%3ERE%3A%20Querying%20AAD%20Audit%20Logs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-879974%22%20slang%3D%22en-US%22%3Eplease%20get%20this%20out%20of%20here...%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello all,

 

I am piping my AAD audit logs to Log Analytics through the Diagnostic Logs, and then I want to set up some alerts if users are added to certain administrator roles..

 

I have got this far, but this returns nothing:

 

 

 

AuditLogs
| where Category == "RoleManagement" 
| extend PropertiesJSON = parse_json(TargetResources)
| extend role = PropertiesJSON[0].modifiedProperties[1]['newValue']
| where role == 'SharePoint Service Administrator'
| project role

 

 

I think because the value I am searching for appears to be an Array of characters, rather than a string..  (same code, just taking out the filter)

 

 

AuditLogs
| where Category == "RoleManagement" 
| extend PropertiesJSON = parse_json(TargetResources)
| extend role = PropertiesJSON[0].modifiedProperties[1]['newValue']
| project role

 

 

 

 

Screen Shot 2019-09-24 at 4.23.45 PM.png

 


Any ideas how I can search for a term that is stored in the data like this?

 

Thanks!

 
2 Replies
Highlighted
Solution

Hi @dave8thomas 

 

You could just use  '   ' around the string as there are "quotes" in the returned data

 

E.g.

 

AuditLogs
| where Category == "RoleManagement" 
| extend PropertiesJSON = parse_json(TargetResources)
| extend role = PropertiesJSON[0].modifiedProperties[1]['newValue'] 
| where role == '"Company Administrator"'

 

or cleanup the returned data 

 

AuditLogs
| where Category == "RoleManagement" 
| extend PropertiesJSON = parse_json(TargetResources)
| extend role = trim(@"[^\w]+", tostring(PropertiesJSON[0].modifiedProperties[1]['newValue']) )
| where role == "Company Administrator"

// trims all non-word characters from start and end of the string
// https://docs.microsoft.com/en-us/azure/kusto/query/trimfunction 

 

Highlighted
Awesome, thanks Clive!! It's the simple things in life .. like quotes! :)