KQL Search

let query_frequency = 5m;
let query_period = 2d;
let _ADFSTokenIssuerNames = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "ADFSTokenIssuerName"
    | summarize make_list(Auxiliar)
);
// let _ADFSResourceIdentities = toscalar(
//     _GetWatchlist("Activity-ExpectedSignificantActivity")
//     | where Activity == "ADFSTokenIssuerName"
//     | summarize make_list(DestinationAddress)
// );
union (
    AADUserRiskEvents
    | where TimeGenerated > ago(query_period)
    | where TokenIssuerType == "ADFederationServices"
    | summarize FirstTimeGenerated = min(TimeGenerated), arg_max(TimeGenerated, *) by Id
    | where FirstTimeGenerated > ago(query_frequency)
    | extend AltAlertLink = strcat("https://entra.microsoft.com/#blade/Microsoft_AAD_IAM/RiskDetectionsBlade/riskState~/[]/userId/", UserId, "/riskLevel/[]/daysBack/90")// Someone wrote "90s" incorrectly in Defender XDR portal
    | where case(
        //RiskState == "dismissed" and RiskDetail == "aiConfirmedSigninSafe", false,
        RiskState == "remediated" and RiskDetail == "userChangedPasswordOnPremises", false,
        true
        )
    | project
        //TimeGenerated,
        ActivityDateTime,
        DetectedDateTime,
        Source,
        Activity,
        DetectionTimingType,
        UserDisplayName,
        UserPrincipalName,
        UserId,
        IpAddress,
        RequestId,
        CorrelationId,
        TokenIssuerType,
        RiskEventType,
        RiskDetail,
        RiskLevel,
        RiskState,
        AdditionalInfo,
        Id,
        AltAlertLink
    ),(
    SigninLogs
    | where TimeGenerated > ago(query_frequency)
    | where TokenIssuerName has_any (_ADFSTokenIssuerNames) and RiskState == "atRisk" and not(RiskLevelAggregated == "none")
        // and ResourceIdentity has_any (_ADFSResourceIdentities) and AppId == "NotApplicable" and ResourceDisplayName == "SSO"
        // and TokenIssuerType == ???
    | project
        TimeGenerated,
        CreatedDateTime,
        Type,
        UserDisplayName,
        UserPrincipalName,
        UserId,
        AlternateSignInName,
        SignInIdentifier,
        UserType,
        IPAddress,
        AutonomousSystemNumber,
        Location,
        NetworkLocationDetails,
        ResultType,
        ResultSignature,
        ResultDescription,
        ClientAppUsed,
        AppDisplayName,
        ResourceDisplayName,
        DeviceDetail,
        UserAgent,
        Status,
        MfaDetail,
        AuthenticationContextClassReferences,
        AuthenticationDetails,
        AuthenticationProcessingDetails,
        AuthenticationProtocol,
        AuthenticationRequirement,
        AuthenticationRequirementPolicies,
        SessionLifetimePolicies,
        TokenIssuerType,
        IncomingTokenType,
        TokenProtectionStatusDetails,
        ConditionalAccessStatus,
        ConditionalAccessPolicies,
        RiskDetail,
        RiskEventTypes_V2,
        RiskLevelAggregated,
        RiskLevelDuringSignIn,
        RiskState,
        HomeTenantId,
        ResourceTenantId,
        CrossTenantAccessType,
        AppId,
        ResourceIdentity,
        UniqueTokenIdentifier,
        SessionId,
        OriginalRequestId,
        CorrelationId
    )