KQL Search

let query_frequency = 1d;
let query_period = 14d;
let operation_list = dynamic(["VaultGet", "SecretGet", "KeyGet", "CertificateGet"]);
let event_count_threshold = 10;
let _AADApps =
    _GetWatchlist("UUID-AADApps")
    | 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 = DestinationResource,
        OperationName = Auxiliar
;
AzureDiagnostics
| where TimeGenerated > ago(query_frequency)
| 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
| as _Retrievals
| where CallerObjectId in (toscalar(
    _Retrievals
    | summarize Count = count() by CallerObjectId
    | where Count > event_count_threshold
    | summarize make_set(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 (
    _AADApps
    | project AppId = tostring(AppId), AppDisplayName
    ) on AppId
| lookup kind=leftouter (
    union
        (
        _AADApps
        | 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