Query for a User Management Activity

%3CLINGO-SUB%20id%3D%22lingo-sub-1104327%22%20slang%3D%22en-US%22%3EQuery%20for%20a%20User%20Management%20Activity%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1104327%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wanted%20to%20write%20a%20KQL%20query%20for%20the%20following%20scenario%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20user%20%22X%22%20is%20created%2C%20%22X%22%20is%20added%20to%20a%20security%20enabled%20group.%20Then%20X%20is%20deleted%20or%20X%20deletes%20some%20other%20account.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20written%20a%20sample%20query%2C%20please%20guide%20me%20how%20to%20write%20the%20correct%20query%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Elet%20a%3DSecurityEvent%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20EventID%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%224720%22%3C%2FSPAN%3E%3CSPAN%3E%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Account_Cre%3DTargetUserName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Account_Final%3D%20tostring(Account_Cre)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20Account_Final%2C%20Activity%20%2C%20SubjectUserName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E%20kind%3D%20inner%20(%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ESecurityEvent%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20EventID%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%224728%22%3C%2FSPAN%3E%20%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%20EventID%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%224732%22%3C%2FSPAN%3E%3CSPAN%3E%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Account_Finally%3D%20extract_all(%40%3C%2FSPAN%3E%3CSPAN%3E%22(%3D)(.*)(%2CCN%3D)%22%3C%2FSPAN%3E%3CSPAN%3E%2C%20dynamic(%5B%3C%2FSPAN%3E%3CSPAN%3E2%3C%2FSPAN%3E%3CSPAN%3E%5D)%2CMemberName%20)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Account_Cre%3DAccount_Finally%5B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%5D%5B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Account_Final%3D%20tostring(Account_Cre)%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Account_Final%20!%3D%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20Account_Final%20%2C%20Activity%2C%20SubjectUserName%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%20Account_Final%20%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3Elet%20b%3D%20a%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E%20kind%3D%20inner%20(SecurityEvent%20%7C%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20EventID%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%224726%22%3C%2FSPAN%3E%3CSPAN%3E%20%7C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Account_Final%20%3D%20TargetUserName%20)%20%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%20Account_Final%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Elet%20c%3D%20a%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E%20kind%3D%20inner%20(SecurityEvent%20%7C%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20EventID%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%224726%22%3C%2FSPAN%3E%3CSPAN%3E%20%7C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Account_Final%20%3DSubjectUserName%20)%20%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%20Account_Final%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%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%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F293879%22%20target%3D%22_blank%22%3E%40Ofer_Shezaf%3C%2FA%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3Eb%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Eunion%3C%2FSPAN%3E%3CSPAN%3E%20c%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1104425%22%20slang%3D%22en-US%22%3ERe%3A%20Query%20for%20a%20User%20Management%20Activity%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1104425%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F503472%22%20target%3D%22_blank%22%3E%40kmanish%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20you%20looked%20at%20the%20samples%20in%20the%20Sentinel%20GitHub%2C%20this%20example%20is%20similar%20in%20structure%20to%20your%20request%20(and%20has%20an%20adjustable%20time%20window%20%5B10mins%5D%20as%20well%2C%20which%20could%20be%20useful).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fblob%2Fmaster%2FDetections%2FSecurityEvent%2FUserAccountCreatedDeleted_10m.yaml%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FAzure%2FAzure-Sentinel%2Fblob%2Fmaster%2FDetections%2FSecurityEvent%2FUserAccountCreatedDeleted_10m.yaml%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi All, 

 

I wanted to write a KQL query for the following scenario:

 

A user "X" is created, "X" is added to a security enabled group. Then X is deleted or X deletes some other account. 

 

I have written a sample query, please guide me how to write the correct query

 

let a=SecurityEvent
| where EventID == "4720" | extend Account_Cre=TargetUserName
| extend Account_Final= tostring(Account_Cre)
| project Account_Final, Activity , SubjectUserName
| join kind= inner (
SecurityEvent
| where EventID == "4728" or EventID == "4732" | extend Account_Finally= extract_all(@"(=)(.*)(,CN=)", dynamic([2]),MemberName )
| extend Account_Cre=Account_Finally[0][0]
| extend Account_Final= tostring(Account_Cre) | where Account_Final !=""
| project Account_Final , Activity, SubjectUserName
) on Account_Final ;

let b= a | join kind= inner (SecurityEvent |where EventID == "4726" |
extend Account_Final = TargetUserName ) on Account_Final;
let c= a | join kind= inner (SecurityEvent |where EventID == "4726" |
extend Account_Final =SubjectUserName ) on Account_Final;
 

b | union c
1 Reply

Hi @kmanish 

 

Have you looked at the samples in the Sentinel GitHub, this example is similar in structure to your request (and has an adjustable time window [10mins] as well, which could be useful).  

 

https://github.com/Azure/Azure-Sentinel/blob/master/Detections/SecurityEvent/UserAccountCreatedDelet...