Forum Discussion

Arnoldas's avatar
Arnoldas
Copper Contributor
Jan 18, 2020

Display user role in AD

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...
AD Azure
azure monitor
CliveWatson's avatar
CliveWatson
Former Employee
Jan 20, 2020

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

 

  • Arnoldas's avatar
    Arnoldas
    Copper Contributor
    Jan 21, 2020

    CliveWatson 

     

    Thanks for the information provided!

    Will let you know what was the outcome.

    Thanks one more time.

    Arnold

    • Arnoldas's avatar
      Arnoldas
      Copper Contributor
      Jan 23, 2020

      @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

       

      • CliveWatson's avatar
        CliveWatson
        Former Employee
        Jan 23, 2020

        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

        d 

Resources