Using Azure Sentinel Kusto (KQL) Scripting to retrieve logs for Reports on Conditional Access
The following process and KQL Script provide a way to review conditional access policies in Azure AD reports.?As always, remember these scripts can be configured as Alerts, Automated reports, (using Azure Logic Apps) and added to Azure Sentinel for SOC / SIEM Dashboard monitoring.
Thankfully this process has become a tad less complicated with the ability to connect Azure Log Analytics / Sentinel to Azure monitor, giving us the ability to review conditional access policies reports before going live.
To grab an export of all sign-ins that are reported from a “report-only” enabled policy, like ‘Block Legacy Authentication’, we can use the below.
Note: that this process depends on having set up log-analytics streaming of?Azure AD & Azure Monitor.
?
The KQL query grabs all sign-ins that have failed a ‘report-only’ conditional access policy, and outputs the sign-in data alongside information about the policy, User, service or processes in question:
?
?
?
Here’s the KQL query code:
//?Conditional Access policies – Report-Only Mode- Report
SigninLogs
| mvexpand ConditionalAccessPolicies
| where ConditionalAccessPolicies["result"] == "reportOnlyFailure"
| project TimeGenerated, Identity, UserPrincipalName, AzureADApplication = AppDisplayName, ClientApplication = ClientAppUsed, ClientBrowser = DeviceDetail.browser, ClientOperatingSystem = DeviceDetail.operatingSystem, ClientIPAddress = IPAddress , ClientUserAgent = UserAgent , ConditionalAccessPolicyName = ConditionalAccessPolicies["displayName"], ConditionalAccessPolicyID = ConditionalAccessPolicies["id"]
To explain what the query does:
1.????Retrieves all sign-in logs associated with the CA Policies.
2.????Expands the ‘ConditionalAccessPolicies’ collection that’s included along with each sign-in’s data and pertinent information (Drillable).
3.????Narrows down the list to only sign-ins where the result of a policy was a “reportOnlyFailure” This can be changed or edited. Have Fun !
4.?????
Now we can export the data to CSV and report to the client.
Happy Hunting ??