Last active
June 26, 2025 01:57
-
-
Save rezamt/1fdf7dccc81e47e89c0e69c8c8efe22e to your computer and use it in GitHub Desktop.
Microsoft Entra Workbook
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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) |
Author
Author
| spath input=AuthenticationRequirementPolicies output=authRequirement path="{1}.requirementProvider"
| where authRequirement != "riskBasedPolicy"
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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