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 

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

        CliveWatson 

         

        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

Resources