KQL Search

Query Details

HomeAI ToolsDevice Query

Audit Logs Azure AD Account Created Without AD Synchronization

Query

let _AADADSyncAccounts = toscalar(
    _GetWatchlist('Activity-ExpectedSignificantActivity')
    | where Activity == "AADADSync"
    | summarize make_list(ActorPrincipalName)
);
AuditLogs
| where OperationName == "Add user" and Result == "success"
| extend
    UserPrincipalName = tostring(TargetResources[0].userPrincipalName),
    InitiatedByUser = tostring(InitiatedBy.user.userPrincipalName),
    InitiatedByApp = tostring(InitiatedBy.app.displayName)
| where not(InitiatedByUser in (_AADADSyncAccounts))
| mv-apply ModifiedProperty = TargetResources[0].modifiedProperties on (
    summarize BagToUnpack = make_bag(pack(tostring(ModifiedProperty.displayName), tostring(ModifiedProperty.newValue)))
    )
| evaluate bag_unpack(BagToUnpack, OutputColumnPrefix = "TargetResources_", columnsConflict = 'replace_source')
| where todynamic(column_ifexists("TargetResources_UserType", ""))[0] != "Guest"
| project
    TimeGenerated,
    InitiatedByUser,
    InitiatedByApp,
    OperationName,
    Result,
    UserPrincipalName,
    InitiatedBy,
    AdditionalDetails,
    TargetResources,
    CorrelationId

Explanation

This query is checking the audit logs for successful "Add user" operations. It's looking for operations that were not initiated by certain accounts (those listed in the 'Activity-ExpectedSignificantActivity' watchlist under the activity 'AADADSync').

The query then unpacks the modified properties of the target resources (the users being added) and checks if the user type is not a "Guest".

Finally, it projects or displays the following information: the time the operation was generated, the user and app that initiated the operation, the operation name, the result, the principal name of the user being added, the initiator's details, additional details, target resources, and the correlation ID.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: December 5, 2022

Tables

_GetWatchlistAuditLogs

Keywords

AADADSyncAccounts,AuditLogs,OperationName,Result,UserPrincipalName,InitiatedByUser,InitiatedByApp,ModifiedProperty,BagToUnpack,TargetResources,TimeGenerated,InitiatedBy,AdditionalDetails,CorrelationId

Operators

toscalar()_GetWatchlist()where()summarize()make_list()extend()tostring()not()in()mv-apply()make_bag()pack()evaluate()bag_unpack()todynamic()column_ifexists()project().

Actions