Reporting Conditional Access Policy Activity from Sign-in Logs with PowerShell and Kusto

In this article

Conditional Access policies are at the heart of Entra ID security. They decide who can sign in, from where, on which devices, and under what conditions. The problem isn’t building policies – it’s understanding what they actually do once they’re deployed.

The Entra admin center gives you views into sign-in logs and there’s also the What If tool, but both have limits:

The What If tool shows simulated results, not what really happened in production.
Browsing sign-in logs is slow and clumsy when you’re trying to follow a specific policy across many users and apps.

If you’re testing a new Conditional Access policy or monitoring changes to an existing one, you really want fast answers from real data.

This script does exactly that. It queries the SigninLogs table in Log Analytics, expands the ConditionalAccessPolicies data, filters by policy name, and gives you:

A summary of results by outcome (success, failure, not applied, report-only variants).
A top N list of matching sign-ins for quick inspection.
Optionally, a full CSV export when you need to analyse everything in Excel or Power BI.

Use cases

Typical scenarios where this script shines:

You’ve deployed a new Conditional Access policy in report-only mode and want to see:
You’ve enabled a policy and want to confirm:
You’re troubleshooting:

Because the script uses Kusto queries against SigninLogs, it’s significantly faster and more scalable than clicking around in the portal, and it’s based on actual events, not simulations.

Parameters and modes

The script exposes a small set of parameters focused on a single task: reporting how a single Conditional Access policy behaves over time.

Key parameters:

-PolicyName (mandatory)
The display name of the Conditional Access policy, exactly as it appears in Entra ID.
-HoursBack
How far back to look in the sign-in logs. Default is 4 hours.
-ReportOnly
Restrict results to report-only evaluations. That’s useful when you’re testing a policy before turning it on.
-EnabledOnly
Restrict results to fully enabled evaluations (everything that is not report-only).

You can’t use -ReportOnly and -EnabledOnly together – the script validates this and throws an error if you try.

-ExportCSV
When present, the script exports the full result set to CSV. If you don’t specify it, the script only prints summary + top N rows.
-Top
How many rows to show when not exporting. Default: 25.
-OutputPath
Custom path for the CSV file. By default, it uses .\CA_Report_<timestamp>.csv.

Example runs:

# Quick look at report-only evaluations in the last 4 hours
.\Get-CaPolicyReport.ps1 -PolicyName "Block legacy auth" -ReportOnly

# Enabled policy, last 8 hours, full export
.\Get-CaPolicyReport.ps1 -PolicyName "Require compliant device" -EnabledOnly -HoursBack 8 -ExportCSV

# Custom output path for Excel/Power BI
.\Get-CaPolicyReport.ps1 -PolicyName "MFA for admins" -ExportCSV -OutputPath "C:\Reports\MFA_Admins_CA.csv"

# Quick look at report-only evaluations in the last 4 hours

.\Get-CaPolicyReport.ps1 -PolicyName "Block legacy auth" -ReportOnly

# Enabled policy, last 8 hours, full export

.\Get-CaPolicyReport.ps1 -PolicyName "Require compliant device" -EnabledOnly -HoursBack 8 -ExportCSV

# Custom output path for Excel/Power BI

.\Get-CaPolicyReport.ps1 -PolicyName "MFA for admins" -ExportCSV -OutputPath "C:\Reports\MFA_Admins_CA.csv"

Azure context and subscription handling

The script uses the Az modules, so it needs an Azure context. Instead of assuming you’ve already connected to the right subscription, it checks and fixes things for you.

It tries Get-AzContext.
It checks whether a subscription is selected.

This avoids the classic “No subscription found” or “wrong tenant” errors when querying Log Analytics.

Automatically finding the right Log Analytics workspace

Many tenants have more than one Log Analytics workspace. You might not remember which one holds the SigninLogs table. The script takes care of that.

$workspaces = Get-AzOperationalInsightsWorkspace
$selectedWorkspace = $null

foreach ($ws in $workspaces) {
    try {
        $probe = Invoke-AzOperationalInsightsQuery -WorkspaceId $ws.CustomerId -Query "SigninLogs | take 1"
        if (-not $probe.Error -and $probe.Results.Count -gt 0) {
            $selectedWorkspace = $ws
            break
        }
    } catch {}
}

$workspaces = Get-AzOperationalInsightsWorkspace

$selectedWorkspace = $null

foreach ($ws in $workspaces) {

try {

$probe = Invoke-AzOperationalInsightsQuery -WorkspaceId $ws.CustomerId -Query "SigninLogs | take 1"

if (-not $probe.Error -and $probe.Results.Count -gt 0) {

$selectedWorkspace = $ws

break

}

} catch {}

}

It loops through all workspaces in the subscription and executes a tiny Kusto query (SigninLogs | take 1) against each. The first workspace that returns a result is assumed to be the one with SigninLogs, and the script uses it for the main query.

If no workspace contains the table, it stops with a clear error:

Could not find a workspace containing the SigninLogs table.

This is a lot more convenient than hardcoding workspace IDs or forcing the admin to remember them.

Building the Kusto query for Conditional Access policies

The core of the solution is a Kusto query that focuses on Conditional Access evaluation per sign-in. The script dynamically builds it, including filters for report-only or enabled results.

The base query:

SigninLogs
| where TimeGenerated > ago(${HoursBack}h)
| mv-expand cap = ConditionalAccessPolicies
| where cap.displayName == '$PolicyName'

SigninLogs

| where TimeGenerated > ago(${HoursBack}h)

| mv-expand cap = ConditionalAccessPolicies

| where cap.displayName == '$PolicyName'

Key points:

It filters sign-ins within the lookback window (HoursBack).
It expands the ConditionalAccessPolicies array into separate rows with mv-expand. Each row represents the evaluation of a single CA policy.
It matches the policy by display name (cap.displayName == '$PolicyName').

Filtering report-only vs enabled

The script uses an additional filter snippet, depending on the switches:

if ($ReportOnly) {
    $capResultFilter = "| where cap.result startswith 'reportOnly'"
}
elseif ($EnabledOnly) {
    $capResultFilter = "| where cap.result !startswith 'reportOnly'"
}

if ($ReportOnly) {

$capResultFilter = "| where cap.result startswith 'reportOnly'"

}

elseif ($EnabledOnly) {

$capResultFilter = "| where cap.result !startswith 'reportOnly'"

}

The cap.result field contains values like:

reportOnlySuccess
reportOnlyFailure
reportOnlyNotApplied
success
failure
notApplied

Using startswith keeps the KQL simple and robust.

The final query projects a set of fields that are usually interesting when testing CA:

| project
    TimeGenerated,
    UserPrincipalName,
    AppDisplayName,
    cap_displayName = cap.displayName,
    cap_result      = cap.result,
    IPAddress,
    Device_OS       = DeviceDetail.operatingSystem,
    Device_Trust    = DeviceDetail.trustType
| order by TimeGenerated desc

| project

TimeGenerated,

UserPrincipalName,

AppDisplayName,

cap_displayName = cap.displayName,

cap_result = cap.result,

IPAddress,

Device_OS = DeviceDetail.operatingSystem,

Device_Trust = DeviceDetail.trustType

| order by TimeGenerated desc

So for each sign-in where the policy was evaluated, you see when, who, which app, result, IP address, and basic device information.

Summary and interactive output

After running the query via Invoke-AzOperationalInsightsQuery, the script checks for errors and then processes $result.Results.

If no rows are returned, it prints:

No results found for policy ‘PolicyName’ in last X hour(s).

Otherwise, it starts with a short summary by result type:

$rows |
    Group-Object cap_result |
    Select-Object Name, Count |
    Sort-Object Name |
    Format-Table -AutoSize

$rows |

Group-Object cap_result |

Select-Object Name, Count |

Sort-Object Name |

Format-Table -AutoSize

This gives a quick breakdown such as:

failure – 5
notApplied – 42
success – 128
reportOnlySuccess – 312

If you’re not exporting to CSV, the script then prints the top N entries (controlled by -Top):

$rows |
    Select-Object TimeGenerated, UserPrincipalName, AppDisplayName, cap_result, IPAddress |
    Select-Object -First $Top |
    Format-Table -AutoSize

$rows |

Select-Object TimeGenerated, UserPrincipalName, AppDisplayName, cap_result, IPAddress |

Select-Object -First $Top |

Format-Table -AutoSize

This mode is ideal when you just want to sanity-check how a policy behaves without generating a full report file.

CSV export for deeper analysis

If you include -ExportCSV, the script writes the full dataset to a CSV file:

$rows | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8

1	$rows \| Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8

This gives you every row returned by the Kusto query – not only the columns displayed in the console, but the entire object set as returned from Log Analytics. That’s perfect for:

Excel pivot tables
Power BI reports
Sharing with security/compliance teams

The default filename includes a timestamp so multiple runs don’t overwrite each other:

CA_Report_20251130_1030.csv

1	CA_Report_20251130_1030.csv

Requirements

To use the script, you need:

Az.Accounts and Az.OperationalInsights modules
Access to a Log Analytics workspace where SigninLogs are stored
Sufficient permissions to:

You also need Conditional Access sign-in logs configured to send data to that workspace – which is the standard setup in many tenants using advanced auditing and monitoring.

Script Source

The complete script is available on the Azure365Addict GitHub.
Feel free to download, customize it to your specific needs, and improve your mailbox management processes.

If you have any questions or need further assistance, feel free to reach out!

Happy scripting!