Query Details
Identity CA Policies Notin Use
Query
//Find any CA policies that are not actively in use (no success or failure events)
//Data connector required for this query - Azure Active Directory - Signin Logs
//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(180d)
| where UserType == "Member"
| mv-expand todynamic(ConditionalAccessPolicies)
| extend CAResult=tostring(ConditionalAccessPolicies.result), CAName=tostring(ConditionalAccessPolicies.displayName)
| summarize TotalCount=count(),ResultSet=make_set(CAResult) by CAName
| where not(ResultSet has_any ("success","failure"))
| sort by CAName asc
Explanation
This query is searching for conditional access (CA) policies in Azure Active Directory that are not actively being used. It looks at signin logs from the past 180 days and filters for users who are members. It expands the conditional access policies and creates two new columns: CAResult (the result of the policy) and CAName (the display name of the policy). It then summarizes the data by counting the total number of policies and creating a set of unique policy results for each policy name. It filters out policies that have any "success" or "failure" results and sorts the results by policy name in ascending order.
Details
Matt Zorich
Released: January 24, 2024
Tables
SigninLogs
Keywords
SigninLogs,TimeGenerated,UserType,ConditionalAccessPolicies,CAResult,CAName,TotalCount,ResultSet
Operators
whereago==mv-expandextendtostringsummarizecountmake_setbynothas_anysort by