Microsoft Entra Workbook

Federated sign-in risk scenarios.md

Federated sign-in risk scenarios

1. Sign-in risk redirected to external identity

SigninLogs
| where RiskLevelDuringSignIn in ("high", "medium") and ResultType == 50074
| where RiskState !in ("dismissed", "remediated")
| where AuthenticationRequirementPolicies has "riskBasedPolicy"
| where Status has "Redirected to external provider for MFA"
| distinct UserPrincipalName=tolower(UserPrincipalName)
| summarize count(UserPrincipalName)

2. Sign-in risk remediated by external identit

SigninLogs
| where ResultType == 0
| where RiskLevelDuringSignIn in ("high", "medium")
| where RiskState in ("remediated") and RiskDetail == "userPassedMFADrivenByRiskBasedPolicy"
| where Status has "MFA requirement satisfied by claim provided by external provider"
| distinct UserPrincipalName=tolower(UserPrincipalName)
| summarize count(UserPrincipalName)

Legacy Identity Protection policies.md

Legacy Identity Protection policies

1. Impacted by legacy user risk policy

SigninLogs
| where ResultType in (50135)
| where AuthenticationRequirementPolicies has "riskBasedPolicy"
| where AuthenticationRequirementPolicies has "tenantSessionRiskPolicy" or AuthenticationRequirementPolicies has "accountCompromisePolicies"
| distinct UserPrincipalName=tolower(UserPrincipalName)
| summarize count(UserPrincipalName)

2. Impacted by legacy sign-in risk policy

SigninLogs
| where ResultType in (0)
| where RiskLevelDuringSignIn in ("high", "medium")
| where AuthenticationRequirementPolicies has "riskBasedPolicy"
| where AuthenticationRequirementPolicies has "tenantSessionRiskPolicy"
| distinct UserPrincipalName=tolower(UserPrincipalName)
| summarize count(UserPrincipalName)

Sign-in risk & trusted network scenarios.md

Sign-in risk & trusted network scenarios

1. High risk sign-ins not being blocked

SigninLogs 
| where ResultType in (0)
| where AppDisplayName <> "Microsoft Authentication Broker"
| where RiskLevelDuringSignIn in ("high")
| distinct UserPrincipalName = tolower(UserPrincipalName)
| count

2. Medium or high risk sign-ins not remediated using multifactor authentication

let strauthreq = SigninLogs 
    | where ResultType in (50074)
    | where RiskLevelDuringSignIn in ("high", "medium")
    | where AuthenticationRequirementPolicies !has "riskBasedPolicy"
    | distinct CorrelationId; 
SigninLogs
| where ResultType in (0)
| where AppDisplayName <> "Microsoft Authentication Broker"
| where RiskLevelDuringSignIn in ("high", "medium")
| where CorrelationId !in (strauthreq)
| extend authRequirement = tostring(parse_json(AuthenticationRequirementPolicies)[1].requirementProvider)
| where authRequirement <> "riskBasedPolicy"
| where RiskState !in ("dismissed", "remediated")
| distinct UserPrincipalName = tolower(UserPrincipalName)
| count

3. Risky sign-ins remediated by multifactor authentication

SigninLogs
| where RiskDetail == "userPassedMFADrivenByRiskBasedPolicy" | where ResultType in (0) | where AuthenticationRequirementPolicies !has "tenantSessionRiskPolicy" | where AppDisplayName <> "Microsoft Authentication Broker" | distinct TimeGenerated, UserPrincipalName = tolower(UserPrincipalName) | count

4. High risk sign-ins not successful

SigninLogs  
| where RiskDetail == "userPassedMFADrivenByRiskBasedPolicy" 
| where ResultType in (0) 
| where AuthenticationRequirementPolicies !has "tenantSessionRiskPolicy"
| where AppDisplayName <> "Microsoft Authentication Broker" 
| distinct TimeGenerated, UserPrincipalName = tolower(UserPrincipalName)
| count

5. IP addresses not trusted

SigninLogs
//| where TimeGenerated > ago(30d)
| where ResultType == "0"
| where HomeTenantId == ResourceTenantId and UserType <> "Guest"
| where NetworkLocationDetails !contains "trustedNamedLocation"
| distinct IPAddress, UserPrincipalName
| summarize UniqueUserCount = count() by IPAddress
| where UniqueUserCount >= 10 
| summarize count(IPAddress)

User risk scenarios.md

User risk scenarios

1. High risk users not being blocked

let risklevel = pack_array("high");
let riskeventsid = SigninLogs
    | where RiskLevelAggregated in (risklevel)
    | distinct OriginalRequestId;
let remediated = SigninLogs
    | where RiskState !in ("none")
    | where OriginalRequestId in (riskeventsid)
    | where RiskState !in ("atRisk", "none")
    | project
        OriginalRequestId,
        RemediatedDateTime = TimeGenerated,
        UserPrincipalName,
        RiskState;
let risk = SigninLogs
    | where RiskState !in ("none")
    | where OriginalRequestId in (riskeventsid)
    | where RiskState in ("atRisk") and RiskLevelAggregated in (risklevel)
    | where not(ResultType == 53003 and ResultDescription == "Access has been blocked due to conditional access policies.")
    | join kind=leftouter (remediated) on OriginalRequestId
    | project
        RiskDateTime = TimeGenerated,
        UserPrincipalName = tolower(UserPrincipalName),
        RemediatedDateTime,
        riskuserRiskLevelAggregated = RiskLevelAggregated,
        ResultType;
let riskusers = risk
    | distinct UserPrincipalName = tolower(UserPrincipalName);
SigninLogs
| where ResultType == 0
| where tolower(UserPrincipalName) in (riskusers) 
| where AppDisplayName !in ("Windows Sign In", "Microsoft Authentication Broker")
| extend ["Device trust type"] = tostring(parse_json(DeviceDetail).trustType) 
| extend ["Device is compliant"] = tostring(parse_json(DeviceDetail).isCompliant) 
| join kind=leftouter (risk) on UserPrincipalName
| where ((TimeGenerated <= RemediatedDateTime) or (isnull(RemediatedDateTime))) and (TimeGenerated >= RiskDateTime)
| distinct UserPrincipalName = tolower(UserPrincipalName)
| summarize ["Blocked high risk users"] = count(UserPrincipalName)

2. High risk users not prompted for password change

let risklevel = pack_array("high");
let riskeventsid = SigninLogs
    | where RiskLevelAggregated in (risklevel)
    | distinct OriginalRequestId;
let remediated = SigninLogs
    | where RiskState !in ("none")
    | where OriginalRequestId in (riskeventsid)
    | where RiskState !in ("atRisk", "none")
    | project
        OriginalRequestId,
        RemediatedDateTime = TimeGenerated,
        UserPrincipalName,
        RiskState;
let risk = SigninLogs
    | where RiskState !in ("none")
    | where OriginalRequestId in (riskeventsid)
    | where RiskState in ("atRisk") and RiskLevelAggregated in (risklevel)
    | where not(ResultType == 53003 and ResultDescription == "Access has been blocked due to conditional access policies.")
    | join kind=leftouter (remediated) on OriginalRequestId
    | project
        RiskDateTime = TimeGenerated,
        UserPrincipalName = tolower(UserPrincipalName),
        RemediatedDateTime,
        riskuserRiskLevelAggregated = RiskLevelAggregated,
        riskResultType=ResultType,
        riskResultDescription=ResultDescription;
let riskusers = risk
    | distinct UserPrincipalName = tolower(UserPrincipalName);
SigninLogs
| where ResultType == 0 or (ResultType == 50142 and AuthenticationRequirementPolicies has "riskBasedPolicy")
| where tolower(UserPrincipalName) in (riskusers) 
| where AppDisplayName !in ("Windows Sign In", "Microsoft Authentication Broker")
| extend ["Device trust type"] = tostring(parse_json(DeviceDetail).trustType) 
| extend ["Device is compliant"] = tostring(parse_json(DeviceDetail).isCompliant) 
| join kind=leftouter (risk) on UserPrincipalName
| where ((TimeGenerated <= RemediatedDateTime) or (isnull(RemediatedDateTime))) and (TimeGenerated >= RiskDateTime)
| distinct
    ResultType,
    ResultDescription,
    AppDisplayName,
    UserDisplayName,
    UserPrincipalName = tolower(UserPrincipalName),
    UserType,
    riskuserRiskLevelAggregated,
    RiskLevelDuringSignIn,
    ["Device trust type"],
    ["Device is compliant"],
    riskResultType,
    riskResultDescription
| summarize
    Applications=make_set(AppDisplayName),
    resulttype = make_set(ResultType),
    ["User risk level"]=make_set(riskuserRiskLevelAggregated),
    ["Sign-in risk level"] = make_set(RiskLevelDuringSignIn),
    ["Device trust type"] = make_set(["Device trust type"]),
    ["Device is compliant"] = make_set(["Device is compliant"])
    by UserDisplayName, UserPrincipalName, UserType
| where resulttype !contains "50142"
| distinct UserPrincipalName = tolower(UserPrincipalName)
| summarize count(UserPrincipalName)

3. Users that changed password

SigninLogs 
| where AuthenticationRequirementPolicies has "riskBasedPolicy" 
| where ResultType == 50142
| distinct UserPrincipalName = tolower(UserPrincipalName)
| summarize count(UserPrincipalName)

4. High risk users not successfully signing-in

SigninLogs 
| where ResultType !in (0, 50074, 50140, 50097, 50055) 
//| where AppDisplayName <> "Microsoft Authentication Broker"   
| where (RiskState !in ("dismissed", "remediated") and RiskLevelAggregated in ("high")) or ResultType == 530032
| extend authRequirement = tostring(parse_json(AuthenticationRequirementPolicies)[1].requirementProvider)
| where authRequirement <> "riskBasedPolicy"
| distinct UserPrincipalName = tolower(UserPrincipalName)
| summarize count(UserPrincipalName)

5. User risk remediated by on-premise password reset

SigninLogs  
| where RiskState == "remediated"
| where ResultType in (0) 
//| where AppDisplayName <> "Microsoft Authentication Broker" 
| where RiskDetail == "userChangedPasswordOnPremises"
| project
    TimeGenerated,
    UserPrincipalName,
    UserType,
    RiskLevelAggregated,
    RiskLevelDuringSignIn,
    RiskState,
    RiskDetail,
    RiskEventTypes_V2
| distinct UserPrincipalName = tolower(UserPrincipalName)
| summarize count(UserPrincipalName)

6. User risk remediated by password reset

SigninLogs  
| where RiskState == "remediated" 
| where ResultType in (0) 
//| where AppDisplayName <> "Microsoft Authentication Broker" 
| where RiskDetail in ("userPerformedSecuredPasswordReset", "userPerformedSecuredPasswordChange") 
| project
    TimeGenerated,
    UserPrincipalName,
    UserType,
    RiskLevelAggregated,
    RiskLevelDuringSignIn,
    RiskState,
    RiskDetail,
    RiskEventTypes_V2
| distinct UserPrincipalName = tolower(UserPrincipalName)
| summarize count(UserPrincipalName)

| spath input=AuthenticationRequirementPolicies output=authRequirement path="{1}.requirementProvider"
| where authRequirement != "riskBasedPolicy"