rezamt · June 26, 2025 01:57 · Jun 26, 2025 · Jun 26, 2025 · Jun 26, 2025 · Jun 25, 2025
diff --git a/Federated sign-in risk scenarios.md b/Federated sign-in risk scenarios.md
@@ -0,0 +1,28 @@
+### 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)
+```
+
+
+
diff --git a/High risk users not being blocked.kql b/High risk users not being blocked.kql
@@ -1,37 +0,0 @@
-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)

diff --git a/Legacy Identity Protection policies.md b/Legacy Identity Protection policies.md
@@ -0,0 +1,22 @@
+### 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)
+```
diff --git a/Sign-in risk & trusted network scenarios.md b/Sign-in risk & trusted network scenarios.md
@@ -0,0 +1,64 @@
+### 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)
+```
diff --git a/User risk scenarios.md b/User risk scenarios.md
@@ -0,0 +1,167 @@
+### 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)
+```
diff --git a/High risk users not being blocked.kql b/High risk users not being blocked.kql
@@ -0,0 +1,37 @@
+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)