KQL Search

Query Details

HomeAI ToolsDevice Query

Audit Logs Admin Promo After Role Mgmt App Permission Grant

Query

let query_frequency = 1h;
let query_period = 14d;
AuditLogs
| where TimeGenerated > ago(query_frequency)
| where Category == "RoleManagement" and LoggedByService == "Core Directory" and AADOperationType == "Assign"
//| where OperationName in ("Add eligible member to role", "Add member to role")
| where isnotempty(InitiatedBy["app"])
| mv-expand TargetResource = TargetResources
| mv-expand modifiedProperty = TargetResource["modifiedProperties"]
| where tostring(modifiedProperty["displayName"]) in ("Role.DisplayName", "RoleDefinition.DisplayName")
| extend RoleAssignment = tostring(modifiedProperty["newValue"])
| where RoleAssignment contains "Admin"
| project
    RoleAssignment_TimeGenerated = TimeGenerated,
    RoleAssignment_OperationName = OperationName,
    RoleAssignment_Result = Result,
    RoleAssignment,
    TargetType = tostring(TargetResources[0]["type"]),
    Target = iff(isnotempty(TargetResources[0]["displayName"]), tostring(TargetResources[0]["displayName"]), tolower(TargetResources[0]["userPrincipalName"])),
    TargetId = tostring(TargetResources[0]["id"]),
    RoleAssignment_InitiatedBy = InitiatedBy,
    RoleAssignment_TargetResources = TargetResources,
    RoleAssignment_AdditionalDetails = AdditionalDetails,
    RoleAssignment_CorrelationId = CorrelationId,
    AppServicePrincipalId = tostring(InitiatedBy["app"]["servicePrincipalId"])
| join kind=inner (
    AuditLogs
    | where TimeGenerated > ago(query_period)
    | where Category == "ApplicationManagement" and LoggedByService == "Core Directory" and OperationName == "Add app role assignment to service principal"
    | mv-expand TargetResource = TargetResources
    | mv-expand modifiedProperty = TargetResource["modifiedProperties"]
    | where tostring(modifiedProperty["displayName"]) == "AppRole.Value"
    | extend PermissionGrant = tostring(modifiedProperty["newValue"])
    | where PermissionGrant has "RoleManagement.ReadWrite.Directory"
    | mv-apply modifiedProperty = TargetResource["modifiedProperties"] on (
        summarize modifiedProperties = make_bag(
            pack(tostring(modifiedProperty["displayName"]),
                pack("oldValue", trim(@'[\"\s]+', tostring(modifiedProperty["oldValue"])),
                    "newValue", trim(@'[\"\s]+', tostring(modifiedProperty["newValue"])))))
    )
    | project
        PermissionGrant_TimeGenerated = TimeGenerated,
        PermissionGrant_OperationName = OperationName,
        PermissionGrant_Result = Result,
        PermissionGrant,
        AppDisplayName = tostring(modifiedProperties["ServicePrincipal.DisplayName"]["newValue"]),
        AppServicePrincipalId = tostring(modifiedProperties["ServicePrincipal.ObjectID"]["newValue"]),
        PermissionGrant_InitiatedBy = InitiatedBy,
        PermissionGrant_TargetResources = TargetResources,
        PermissionGrant_AdditionalDetails = AdditionalDetails,
        PermissionGrant_CorrelationId = CorrelationId
) on AppServicePrincipalId
| where PermissionGrant_TimeGenerated < RoleAssignment_TimeGenerated
| project
    PermissionGrant_TimeGenerated,
    PermissionGrant_OperationName,
    PermissionGrant_Result,
    PermissionGrant,
    AppDisplayName,
    AppServicePrincipalId,
    PermissionGrant_InitiatedBy,
    PermissionGrant_TargetResources,
    PermissionGrant_AdditionalDetails,
    PermissionGrant_CorrelationId,
    RoleAssignment_TimeGenerated,
    RoleAssignment_OperationName,
    RoleAssignment_Result,
    RoleAssignment,
    TargetType,
    Target,
    TargetId,
    RoleAssignment_InitiatedBy,
    RoleAssignment_TargetResources,
    RoleAssignment_AdditionalDetails,
    RoleAssignment_CorrelationId

Explanation

This query is designed to extract specific information from the AuditLogs. It first sets a query frequency of 1 hour and a query period of 14 days. It then filters the logs for entries where the category is "RoleManagement", the service logged by is "Core Directory", and the operation type is "Assign".

The query further filters for entries where the 'InitiatedBy' field is not empty and expands the 'TargetResource' and 'modifiedProperty' fields. It then filters for entries where the 'displayName' field is either "Role.DisplayName" or "RoleDefinition.DisplayName", and extends a new field 'RoleAssignment' with the 'newValue' of the 'modifiedProperty'.

The query then filters for entries where the 'RoleAssignment' contains "Admin" and projects several fields including 'TimeGenerated', 'OperationName', 'Result', 'RoleAssignment', 'TargetType', 'Target', 'TargetId', 'InitiatedBy', 'TargetResources', 'AdditionalDetails', 'CorrelationId', and 'AppServicePrincipalId'.

The query then joins this data with another set of data from the AuditLogs, filtered and processed in a similar way but with different conditions and fields. The join is based on the 'AppServicePrincipalId' field.

Finally, the query filters for entries where 'PermissionGrant_TimeGenerated' is less than 'RoleAssignment_TimeGenerated' and projects a set of fields from both the initial and joined data.

In simpler terms, this query is looking for instances where an admin role was assigned in the system, and then it checks if the user who was assigned this role also had permission to manage roles in the system. It provides a detailed report of these instances.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: January 4, 2023

Tables

AuditLogs

Keywords

AuditLogs,TimeGenerated,Category,RoleManagement,LoggedByService,CoreDirectory,AADOperationType,Assign,InitiatedBy,App,TargetResources,ModifiedProperties,DisplayName,Role,RoleDefinition,RoleAssignment,Admin,TargetType,Target,TargetId,AdditionalDetails,CorrelationId,AppServicePrincipalId,ApplicationManagement,OperationName,AddAppRoleAssignmenttoServicePrincipal,PermissionGrant,RoleManagement,ReadWrite,Directory,ServicePrincipal,ObjectID

Operators

letwhereisnotemptymv-expandtostringinextendcontainsprojectifftolowerjoinkind=innerhasmv-applyonsummarizemake_bagpacktrim<

Actions