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)

Guide: This workbook allows you to view the users and sessions impacted if a risk-based access policy were to be enabled without the need for creating any policies or having policies in report-only mode. For example, you can see which users would have been impacted over the past 30 days if you had enabled a risk-based Conditional Access policy where high risk users are blocked. Note: Utilize the filters to manage the amount of historical data evaluated for impact.

There are two main sections in this workbook:

The impact summary section is a high level view of the count of users, sign-ins or IP addresses that would be impacted if the associated policy was enabled in your environment.
The impact details section will list out pertinent details including user name, risk level, application and device compliance.
Prerequisite: The only prerequisite is that you are capturing sign-in logs in Log Analytics.

Additional Resources: To learn more about deployment and configuring policies, please follow the links below:

Step 1: Review existing reports
Step 2: Plan for Conditional Access risk policies
Step 3: Configure your policies
Recommended risk-based conditional access policies.

Common Conditional Access policy: User risk-based password change
Common Conditional Access policy: Sign-in risk-based multifactor authentication
Additional guidance: Impact analysis of risk-based access policies

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