KQL Search

Query Details

HomeAI ToolsDevice Query

Audit Daily Summaryof Admin Activity

Query

//Create a daily summary of activities completed by your Azure AD privileged users

//Data connector required for this query - Azure Active Directory - Audit Logs
//Data connector required for this query - Microsoft Sentinel UEBA

let timerange=30d;
IdentityInfo
| where TimeGenerated > ago(21d)
| summarize arg_max(TimeGenerated, *) by AccountUPN
| where isnotempty(AssignedRoles)
| where AssignedRoles != "[]"
| project Actor=AccountUPN
| join kind=inner (
    AuditLogs
    | where TimeGenerated > ago(timerange)
    | extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
    | where isnotempty(Actor)
    )
    on Actor
| summarize AdminActivity = make_list(OperationName) by Actor, startofday(TimeGenerated)

Explanation

This query creates a daily summary of activities completed by privileged users in Azure AD. It uses data connectors for Azure Active Directory Audit Logs and Microsoft Sentinel UEBA. The query retrieves identity information for the past 21 days, filters out empty or null assigned roles, and projects the account's user principal name. It then joins this information with audit logs for the past 30 days, matching on the actor's user principal name. Finally, it summarizes the admin activity by creating a list of operation names for each actor and grouping them by actor and the start of each day.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

IdentityInfoAuditLogs

Keywords

AzureAD,AuditLogs,MicrosoftSentinelUEBA,IdentityInfo,AccountUPN,AssignedRoles,Actor,AuditLogs,TimeGenerated,InitiatedBy.user,OperationName

Operators

wheresummarizearg_maxbyisnotemptyprojectjoinkindextendonmake_list

Actions