KQL Search

Query Details

HomeAI ToolsDevice Query

Audit Logs PIM Settings Modified

Query

AuditLogs
| where LoggedByService == "PIM" and OperationName has "update" and not(OperationName has "role update" or OperationName has_all("update", "member"))
| extend
    Initiator = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["displayName"]), tostring(InitiatedBy["user"]["userPrincipalName"])),
    InitiatorId = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["servicePrincipalId"]), tostring(InitiatedBy["user"]["id"])),
    IPAddress = tostring(InitiatedBy[tostring(bag_keys(InitiatedBy)[0])]["ipAddress"])
| project
    TimeGenerated,
    Category,
    Initiator,
    IPAddress,
    OperationName,
    Result,
    InitiatedBy,
    AdditionalDetails,
    TargetResources,
    LoggedByService,
    InitiatorId,
    CorrelationId

Explanation

This query is searching through audit logs where the service logged is "PIM" and the operation name contains "update" but does not contain "role update" or both "update" and "member".

It then creates new fields for the initiator and initiator ID, which are determined based on whether the 'InitiatedBy' field contains an 'app' or 'user'. If it's an app, it uses the app's display name and service principal ID, otherwise it uses the user's principal name and ID. It also extracts the IP address from the 'InitiatedBy' field.

Finally, it projects (or selects) a number of fields to display in the results, including the time the event was generated, the category, the initiator and their IP address, the operation name, the result, who initiated the event, any additional details, the target resources, the service that logged the event, the initiator ID, and the correlation ID.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: May 24, 2023

Tables

AuditLogs

Keywords

AuditLogs,PIM,Update,RoleUpdate,Member,Initiator,InitiatedBy,App,DisplayName,User,UserPrincipalName,InitiatorId,ServicePrincipalId,IPAddress,TimeGenerated,Category,OperationName,Result,AdditionalDetails,TargetResources,LoggedByService,CorrelationId

Operators

| summarize arg_max(TimeGenerated*) | extend Target = iif(isnotempty(TargetResources[0]["user"]["userPrincipalName"])tostring(TargetResources[0]["user"]["userPrincipalName"])tostring(TargetResources[0]["group"]["displayName"])), TargetId = iif(isnotempty(TargetResources[0]["user"]["id"])tostring(TargetResources[0]["user"]["id"])tostring(TargetResources[0]["group"]["id"])), AdditionalDetails = tostring(AdditionalDetails) | project TimeGenerated, Category, Initiator, IPAddress, OperationName, Result, InitiatedBy, AdditionalDetails, Target, TargetId, LoggedByService, InitiatorId, CorrelationId | order by TimeGenerated desc | take 100 | mv-expand TargetResources | extend Target = iif(isnotempty(TargetResources["user"]["userPrincipalName"])tostring(TargetResources["user"]["userPrincipalName"])tostring(TargetResources["group"]["displayName"])), TargetId = iif(isnotempty(TargetResources["user"]["id"])tostring(TargetResources["user"]["id"])tostring(TargetResources["group"]["id"])) | project TimeGenerated, Category, Initiator, IPAddress, OperationName, Result, InitiatedBy, AdditionalDetails, Target, TargetId, LoggedByService, InitiatorId, CorrelationId | summarize count() by bin(TimeGenerated1h)InitiatorIPAddressOperationNameResultTargetLoggedByService | extend TimeGenerated = toscalar(datetime_add('hour'-7TimeGenerated)) | order by TimeGenerated desccount_ desc | take 100

Actions