KQL Search

let query_frequency = 1h;
let query_period = 14d;
let operation_list = dynamic(["VaultGet", "SecretGet", "KeyGet", "CertificateGet"]);
let threshold = 5;
let _EntraIdApps =
    _GetWatchlist("UUID-EntraIdApps")
    | project AppId, ObjectId, AppDisplayName
;
let _ExpectedRetrieval =
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "SecretRetrieval" and (isnotempty(DestinationResource) or isnotempty(Auxiliar))
    | project
        CallerObjectId = tostring(ActorId),
        identity_claim_appid_g = tostring(SourceResource),
        Resource = tostring(DestinationResource),
        OperationName = tostring(Auxiliar)
;
AzureDiagnostics
| where TimeGenerated > ago(query_period)
| where ResourceType =~ "VAULTS" and OperationName in (operation_list)
| extend
    ResultType = column_ifexists("ResultType", ""),
    identity_claim_http_schemas_microsoft_com_identity_claims_objectidentifier_g  = column_ifexists("identity_claim_http_schemas_microsoft_com_identity_claims_objectidentifier_g", ""),
    identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s = column_ifexists("identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s", ""),
    identity_claim_oid_g = column_ifexists("identity_claim_oid_g", ""),
    identity_claim_upn_s = column_ifexists("identity_claim_upn_s", "")
| extend
    CallerObjectId = iff(isempty(identity_claim_oid_g), identity_claim_http_schemas_microsoft_com_identity_claims_objectidentifier_g, identity_claim_oid_g),
    CallerObjectUPN = iff(isempty(identity_claim_upn_s), identity_claim_http_schemas_xmlsoap_org_ws_2005_05_identity_claims_upn_s, identity_claim_upn_s)
| join kind=leftanti (
    _ExpectedRetrieval
    | where isnotempty(Resource)
    ) on CallerObjectId, identity_claim_appid_g, OperationName, Resource
| join kind=leftanti (
    _ExpectedRetrieval
    | where isempty(Resource)
    ) on CallerObjectId, identity_claim_appid_g, OperationName
| summarize arg_min(TimeGenerated, *) by OperationName, requestUri_s, CallerObjectId, _ResourceId
| where TimeGenerated > ago(2 * query_frequency)
| as _Events
| join kind=leftsemi (
    _Events
    // query_period should be 2 * query_frequency
    | evaluate activity_counts_metrics(Type, TimeGenerated, ago(2 * query_frequency), now(), query_frequency, CallerObjectId)
    | summarize
        arg_min(PreviousTimeGenerated = TimeGenerated, PreviousCount = ["count"]),
        arg_max(CurrentTimeGenerated = TimeGenerated, CurrentCount = ["count"])
        by CallerObjectId
    | where CurrentTimeGenerated > ago(query_period)
    | extend PreviousCount = iff(PreviousTimeGenerated == CurrentTimeGenerated, 0, PreviousCount)
    | where (not(PreviousCount > threshold) and CurrentCount > threshold)
        or ((CurrentCount - PreviousCount) > threshold)
    ) on CallerObjectId
| extend
    requestUri_s = column_ifexists("requestUri_s", ""),
    id_s = column_ifexists("id_s", ""),
    CallerIPAddress = column_ifexists("CallerIPAddress", ""),
    clientInfo_s = column_ifexists("clientInfo_s", "")
| summarize
    StartTime = min(TimeGenerated),
    EndTime = max(TimeGenerated),
    EventCount = count(),
    OperationNames = make_set(OperationName, 100),
    RequestURIs = make_set(requestUri_s, 100),
    CallerIPAddresses = make_set(CallerIPAddress, 100),
    clientInfo_s = make_set(clientInfo_s, 100),
    take_any(ResourceType, CallerIPAddress, _ResourceId)
    by Resource, id_s, CallerObjectId, CallerObjectUPN, identity_claim_appid_g, ResultType
| project-rename AppId = identity_claim_appid_g
| lookup kind=leftouter (
    _EntraIdApps
    | project AppId = tostring(AppId), AppDisplayName
    ) on AppId
| lookup kind=leftouter (
    union
        (
        _EntraIdApps
        | project ServicePrincipalId = tostring(ObjectId), ServicePrincipalName = tostring(AppDisplayName)
        ),
        (
        AADServicePrincipalSignInLogs
        | where TimeGenerated > ago(query_period)
        ),
        (
        AADManagedIdentitySignInLogs
        | where TimeGenerated > ago(query_period)
        ),
        (
        AuditLogs
        | where TimeGenerated > ago(query_period)
        | where Category == "ApplicationManagement" and OperationName has "service principal" and not(AADOperationType in ("Assign", "Unassign"))
        | project ServicePrincipalId = tostring(TargetResources[0]["id"]), ServicePrincipalName = tostring(TargetResources[0]["displayName"])
        )
    | where isnotempty(ServicePrincipalId)
    | distinct ServicePrincipalId, ServicePrincipalName
    ) on $left.CallerObjectId == $right.ServicePrincipalId
| extend CallerObject = coalesce(CallerObjectUPN, ServicePrincipalName)
| project
    StartTime,
    EndTime,
    EventCount,
    ResourceType,
    Resource,
    id_s,
    AppId,
    AppDisplayName,
    CallerObjectId,
    CallerObject,
    ResultType,
    OperationNames,
    RequestURIs,
    CallerIPAddress,
    CallerIPAddresses,
    clientInfo_s,
    _ResourceId