Home

Display user role in AD

%3CLINGO-SUB%20id%3D%22lingo-sub-1116525%22%20slang%3D%22en-US%22%3EDisplay%20user%20role%20in%20AD%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1116525%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20creating%20a%20query%20to%20display%20AD%20accounts%20activity.%20Such%20as%20account%20creation.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20see%20who%20has%20reacted%20an%20account%20(With%20caller%20command)%20I%20would%20like%20to%20see%20Users%20role%20as%20well%20(such%20as%20global%20admin%2C%20security%20admin%2C%20etc).%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20Can%20I%20achieve%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1116525%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAD%20Azure%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1119138%22%20slang%3D%22en-US%22%3ERe%3A%20Display%20user%20role%20in%20AD%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1119138%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%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20information%20provided!%3C%2FP%3E%3CP%3EWill%20let%20you%20know%20what%20was%20the%20outcome.%3C%2FP%3E%3CP%3EThanks%20one%20more%20time.%3C%2FP%3E%3CP%3EArnold%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1118111%22%20slang%3D%22en-US%22%3ERe%3A%20Display%20user%20role%20in%20AD%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1118111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523808%22%20target%3D%22_blank%22%3E%40Arnoldas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESome%20AzureAD%20samples%20to%20get%20you%20started...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20Look%20at%20Audit%20logs%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EAuditLogs%0A%7C%20extend%20userPrincipalName_%20%3D%20tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)%20%0A%7C%20extend%20userPrincipalName2_%20%3D%20tostring(TargetResources%5B0%5D.userPrincipalName)%20%0A%7C%20extend%20userPrincipalName%20%3D%20iif(isempty(userPrincipalName_)%2C%20userPrincipalName2_%2C%20userPrincipalName_)%0A%7C%20where%20OperationName%20!contains%20%22service%20principal%22%0A%7C%20summarize%20count()%2C%20make_set(InitiatedBy)%20%20by%20ActivityDisplayName%2C%20userPrincipalName%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E2.%26nbsp%3B%20SigninLogs%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESigninLogs%20%0A%7C%20extend%20ErrorCode%20%3D%20tostring(Status.errorCode)%20%0A%7C%20extend%20FailureReason%20%3D%20Status.failureReason%20%0A%7C%20where%20ErrorCode%20in%20(%2250058%22%2C%2250140%22%2C%20%2251006%22%2C%20%2250059%22%2C%20%2265001%22%2C%20%2252004%22%2C%20%2250055%22%2C%20%2250144%22%2C%2250072%22%2C%20%2250074%22%2C%20%2216000%22%2C%2216001%22%2C%20%2216003%22%2C%20%2250127%22%2C%20%2250125%22%2C%20%2250129%22%2C%2250143%22%2C%20%2281010%22%2C%20%2281014%22%2C%20%2281012%22)%20%0A%7C%20summarize%20errCount%20%3D%20count()%20by%20ErrorCode%2C%20tostring(FailureReason)%2C%20UserDisplayName%2C%20UserPrincipalName%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Arnoldas
Occasional Contributor

hello,

 

I'm creating a query to display AD accounts activity. Such as account creation.

I would like to see who has reacted an account (With caller command) I would like to see Users role as well (such as global admin, security admin, etc). 

How Can I achieve that?

 

Regards,

7 Replies
Highlighted

@Arnoldas 

 

Some AzureAD samples to get you started...

 

1. Look at Audit logs

AuditLogs
| extend userPrincipalName_ = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName) 
| extend userPrincipalName2_ = tostring(TargetResources[0].userPrincipalName) 
| extend userPrincipalName = iif(isempty(userPrincipalName_), userPrincipalName2_, userPrincipalName_)
| where OperationName !contains "service principal"
| summarize count(), make_set(InitiatedBy)  by ActivityDisplayName, userPrincipalName

2.  SigninLogs 

SigninLogs 
| extend ErrorCode = tostring(Status.errorCode) 
| extend FailureReason = Status.failureReason 
| where ErrorCode in ("50058","50140", "51006", "50059", "65001", "52004", "50055", "50144","50072", "50074", "16000","16001", "16003", "50127", "50125", "50129","50143", "81010", "81014", "81012") 
| summarize errCount = count() by ErrorCode, tostring(FailureReason), UserDisplayName, UserPrincipalName

 

Highlighted

@Clive Watson 

 

Thanks for the information provided!

Will let you know what was the outcome.

Thanks one more time.

Arnold

Highlighted

@Clive Watson

 

Hello,

 

I have managed to gather some code but sadly it's not providing info needed in the alert itself.

Code itself is straight forward:

AuditLogs
| where OperationName == "Add user"
| extend userPrincipalName_ = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend userPrincipalName2_ = tostring(TargetResources[0].userPrincipalName)
| extend AccountCustomEntity = userPrincipalName_
| extend AccountCustomEntity2 = userPrincipalName2_
 
It does generate info needed in the logs tab. Such as who performed activity userPrincipalName_ and who was impacted userPrincipalName2_.
But when I add this query to alert it only generates userPrincipalName_ name only.
I want to see who performed what based on OperationName and who was impacted.
Maybe you can assist me here as well?
 
thanks in advance,
Arnold

 

Highlighted

@Arnoldas 

 

You can create a merges column (called here AggregatedValue), I used strcat to create a comma separated list of the 4 items 

AuditLogs
| where OperationName == "Add user"
| extend userPrincipalName_ = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend userPrincipalName2_ = tostring(TargetResources[0].userPrincipalName)
| extend AccountCustomEntity = userPrincipalName_
| extend AccountCustomEntity2 = userPrincipalName2_
| extend AggregatedValue = strcat (userPrincipalName_,", ", userPrincipalName2_,", ", AccountCustomEntity,", ", AccountCustomEntity2)
| summarize count() by AggregatedValue 

Highlighted

@Clive Watson 

 

Hey, your help is much appreciated! 

I managed to display the information needed by adding one account as AccountCustomEntity and other by HostCustomEntity:

AuditLogs
| where ActivityDisplayName == "Add user"
| extend userPrincipalName_ = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend userPrincipalName2_ = tostring(TargetResources[0].userPrincipalName)
| extend AccountCustomEntity = userPrincipalName_
| extend HostCustomEntity = userPrincipalName2_
 
This does work, but that's being said it is not accurate as it should be two AccountCustomEntites and one should be AccountCustomEntity = userPrincipalName_ which should display the username of account which started ActivityDisplayName and AccountCustomEntity2 should be impacted account.
So maybe you know how to display two AccountCustomEntites?
Or my approach is making no sense?
 
Regards,
Arnold
Highlighted

@Arnoldas 

 

Something like this?

 

AuditLogs
| where ActivityDisplayName == "Add user"
| extend userPerformingAction = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend userAmended          = tostring(TargetResources[0].userPrincipalName)
| summarize by userPerformingAction, userAmended, ActivityDisplayName, Result
Highlighted

@Clive Watson 

thanks for your help!