Skip to content

Export-ConditionalAccessReport

Exports Conditional Access policies to Excel.

Example Output

Export-ConditionalAccessReport output

Syntax

.\Export-ConditionalAccessReport.ps1 [-Tenant <String>] [-Cloud <String>] [-Out <String>]

Description

Retrieves all Conditional Access policies from Microsoft Entra ID, processes them into a readable format, and exports to Excel with multiple worksheets including Named Locations details.

Examples

Example 1

.\Export-ConditionalAccessReport.ps1
# Exports CA policies to an auto-named Excel file.

Example 2

.\Export-ConditionalAccessReport.ps1 -Tenant 'contoso.onmicrosoft.com' -Out 'C:\Reports\CA.xlsx'
# Exports CA policies for the specified tenant.

Parameters

-Tenant

Optional tenant ID or domain for specific tenant connection.

  • Type: String
  • Required: No

-Cloud

Cloud environment to connect to. Defaults to 'Global'. Valid values: Global, USGov, USGovDoD, Germany, China

  • Type: String
  • Required: No
  • Valid values: Global, GCC, GCCH, DoD, Germany, China

-Out

Output file path for the Excel export. If not specified, generates a filename based on tenant name and current timestamp.

  • Type: String
  • Required: No

Notes

Requires ImportExcel module for Excel export functionality.

Full Script

Copy this complete standalone script (includes all helper functions):

#Requires -Version 7.0
#Requires -Modules ImportExcel, Microsoft.Graph.Authentication

<#
.SYNOPSIS
    Exports Conditional Access policies to Excel.
.DESCRIPTION
    Retrieves all Conditional Access policies from Microsoft Entra ID,
    processes them into a readable format, and exports to Excel with
    multiple worksheets including Named Locations details.
.PARAMETER Tenant
    Optional tenant ID or domain for specific tenant connection.
.PARAMETER Cloud
    Cloud environment to connect to. Defaults to 'Global'.
    Valid values: Global, USGov, USGovDoD, Germany, China
.PARAMETER Out
    Output file path for the Excel export. If not specified, generates
    a filename based on tenant name and current timestamp.
.EXAMPLE
    .\Export-ConditionalAccessReport.ps1
    # Exports CA policies to an auto-named Excel file.
.EXAMPLE
    .\Export-ConditionalAccessReport.ps1 -Tenant 'contoso.onmicrosoft.com' -Out 'C:\Reports\CA.xlsx'
    # Exports CA policies for the specified tenant.
.NOTES
    Requires ImportExcel module for Excel export functionality.
#>

[CmdletBinding()]
param(
    [string]$Tenant,

    [ValidateSet('Global', 'GCC', 'GCCH', 'DoD', 'Germany', 'China')

#region Helper Functions

function Write-TimeLog {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory, Position = 0)]
        [string]$Message,

        [Parameter()]
        [ConsoleColor]$Color = 'White'
    )

    Write-Host "[$(Get-Date -Format 'HH:mm')] $Message" -ForegroundColor $Color
}

function Connect-MgGraphIfNeeded {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [string[]]$Scopes,

        [Parameter()]
        [string]$Tenant,

        [Parameter()]
        [ValidateSet('Global', 'GCC', 'GCCH', 'DoD', 'Germany', 'China')]
        [string]$Cloud = 'Global'
    )

    $envMap = @{
        'Global'  = 'Global'
        'GCC'     = 'Global'
        'GCCH'    = 'USGov'
        'DoD'     = 'USGovDoD'
        'Germany' = 'Germany'
        'China'   = 'China'
    }

    try {
        $context = Get-MgContext

        if ($context) {
            $missingScopes = $Scopes | Where-Object { $_ -notin $context.Scopes }
            $tenantMismatch = $false

            if ($Tenant) {
                $currentTenantId = $context.TenantId
                if ($currentTenantId -ne $Tenant) {
                    $org = Get-MgOrganization -ErrorAction SilentlyContinue
                    $tenantDomain = $org.VerifiedDomains | Where-Object { $_.IsDefault } | Select-Object -ExpandProperty Name
                    if ($Tenant -ne $tenantDomain) {
                        $tenantMismatch = $true
                        Write-TimeLog "Current connection is to '$($org.DisplayName)' but '$Tenant' was requested" -Color Yellow
                    }
                }
            }

            if (-not $missingScopes -and -not $tenantMismatch) {
                $org = Get-MgOrganization
                Write-TimeLog "Using existing Graph connection to $($org.DisplayName)" -Color Cyan
                return $org
            }

            if ($tenantMismatch) {
                Write-TimeLog "Reconnecting to switch to tenant: $Tenant" -Color Yellow
            }
            elseif ($missingScopes) {
                Write-TimeLog "Reconnecting to add scopes: $($missingScopes -join ', ')" -Color Yellow
            }
        }

        $connectParams = @{
            Scopes      = $Scopes
            Environment = $envMap[$Cloud]
            NoWelcome   = $true
            ErrorAction = 'Stop'
        }

        if ($Tenant) {
            $connectParams['TenantId'] = $Tenant
        }

        Connect-MgGraph @connectParams
        $org = Get-MgOrganization
        Write-TimeLog "Connected to Graph: $($org.DisplayName)" -Color Green
        $org
    }
    catch {
        Write-TimeLog "Failed to connect to Microsoft Graph: $($_.Exception.Message)" -Color Red
        throw
    }
}

function ConvertTo-FriendlyDate {
    [CmdletBinding()]
    param(
        [Parameter()]
        [string]$InputString
    )

    if ([string]::IsNullOrWhiteSpace($InputString)) {
        return $null
    }

    try {
        $dt = [datetime]::Parse($InputString, [System.Globalization.CultureInfo]::InvariantCulture)
        return $dt.ToString('yyyy-MM-dd HH:mm')
    }
    catch {
        return $null
    }
}

function Get-DefaultExcelParams {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [string]$Path
    )

    @{
        Path         = $Path
        TableStyle   = 'Medium2'
        AutoSize     = $true
        FreezeTopRow = $true
    }
}

function New-ReportPath {
    [CmdletBinding()]
    param(
        [Parameter()]
        [string]$Out,

        [Parameter(Mandatory)]
        [string]$FriendlyName,

        [Parameter(Mandatory)]
        [string]$OrgDisplayName,

        [Parameter()]
        [string]$Extension = 'xlsx'
    )

    if (!$Out) {
        "$OrgDisplayName-$FriendlyName-$(Get-Date -Format 'yyyy-MM-dd_HH-mm').$Extension"
    }
    elseif ($Out -notlike "*.$Extension") {
        "$Out.$Extension"
    }
    else {
        $Out
    }
}

#endregion Helper Functions
]
    [string]$Cloud = 'Global',

    [string]$Out
)

$scopes = @(
    'User.Read.All',
    'Group.Read.All',
    'RoleManagement.Read.Directory',
    'Application.Read.All',
    'Policy.Read.All',
    'Organization.Read.All'
)

$org = Connect-MgGraphIfNeeded -Scopes $scopes -Tenant $Tenant -Cloud $Cloud
$Out = New-ReportPath -Out $Out -FriendlyName 'ConditionalAccess' -OrgDisplayName $org.DisplayName

Write-TimeLog "Fetching conditional access policies and directory roles..."

$policies = Get-MgIdentityConditionalAccessPolicy -All

$directoryRoleTemplates = Get-MgDirectoryRoleTemplate -All
$roleTemplateMap = @{}
foreach ($template in $directoryRoleTemplates) {
    $roleTemplateMap[$template.Id] = $template.DisplayName
}

Write-TimeLog "Fetching users, groups, and apps..."

$allUsers = @{}
$allGroups = @{}
$allApps = @{}

Get-MgUser -All -Property Id, UserPrincipalName | ForEach-Object {
    $allUsers[$_.Id] = $_.UserPrincipalName
}

Get-MgGroup -All -Property Id, DisplayName | ForEach-Object {
    $allGroups[$_.Id] = $_.DisplayName
}

Get-MgApplication -All -Property Id, DisplayName, AppId | ForEach-Object {
    $allApps[$_.AppId] = $_.DisplayName
}

Write-TimeLog "Fetching and processing named locations..."

$namedLocations = Get-MgIdentityConditionalAccessNamedLocation -All
$locationMap = @{}
$locationDetails = @()

foreach ($location in $namedLocations) {
    $locationMap[$location.Id] = $location.DisplayName

    $locationType = 'Unknown'
    $locationValues = @()

    if ($location.AdditionalProperties -and $location.AdditionalProperties['@odata.type']) {
        if ($location.AdditionalProperties['@odata.type'] -eq '#microsoft.graph.countryNamedLocation') {
            $locationType = 'Country'
            $locationValues = $location.AdditionalProperties.countriesAndRegions
        }
        elseif ($location.AdditionalProperties['@odata.type'] -eq '#microsoft.graph.ipNamedLocation') {
            $locationType = 'IP Range'
            $locationValues = $location.AdditionalProperties.ipRanges | ForEach-Object {
                if ($_.cidrAddress) { $_.cidrAddress } else { "$($_.addressPrefix)/$($_.prefixLength)" }
            }
        }
    }

    $locationDetails += [PSCustomObject]@{
        'Location Name' = $location.DisplayName
        'Type'          = $locationType
        'Values'        = $locationValues -join '; '
        'Is Trusted'    = if ($location.AdditionalProperties.isTrusted) { 'Yes' } else { 'No' }
        'Created'       = ConvertTo-FriendlyDate $location.CreatedDateTime
        'Modified'      = ConvertTo-FriendlyDate $location.ModifiedDateTime
    }
}

Write-TimeLog "Processing $($policies.Count) conditional access policies..."

$results = foreach ($policy in $policies) {
    $state = switch ($policy.State) {
        'enabledForReportingButNotEnforced' { 'report-only' }
        default { $policy.State }
    }

    $includedObjects = @()
    $excludedObjects = @()

    # Included users
    if ($policy.Conditions.Users.IncludeUsers) {
        foreach ($userId in $policy.Conditions.Users.IncludeUsers) {
            if ($userId -eq 'All') {
                $includedObjects += 'All Users'
            }
            elseif ($userId -eq 'GuestsOrExternalUsers') {
                $includedObjects += 'Guests or External Users'
            }
            else {
                $includedObjects += $allUsers[$userId] ?? $userId
            }
        }
    }

    # Included guests/external users
    if ($policy.Conditions.Users.IncludeGuestsOrExternalUsers) {
        $types = $policy.Conditions.Users.IncludeGuestsOrExternalUsers.GuestOrExternalUserTypes

        # Only process if GuestOrExternalUserTypes is actually set
        if ($types) {
            $ext = $policy.Conditions.Users.IncludeGuestsOrExternalUsers.ExternalTenants

            $label = "Guests or External Users: $types"

            $extLabel = $null
            if ($ext) {
                if ($ext.PSObject.Properties.Match('MembershipKind').Count -gt 0 -and $ext.MembershipKind) {
                    if ($ext.MembershipKind -eq 'all') { $extLabel = 'ExternalTenants: All' }
                }
                if (-not $extLabel -and $ext.PSObject.Properties.Match('Members').Count -gt 0 -and $ext.Members) {
                    $extLabel = "ExternalTenants: $($ext.Members -join ',')"
                }

                if (-not $extLabel) {
                    $pairs = $ext.PSObject.Properties |
                    Where-Object { $_.Name -ne 'AdditionalProperties' -and $_.Value } |
                    ForEach-Object {
                        if ($_.Value -is [System.Array]) { "$($_.Name)=$($($_.Value -join ','))" }
                        elseif ($_.Value -isnot [System.Collections.IDictionary]) { "$($_.Name)=$($_.Value)" }
                    }
                    $extSummary = ($pairs | Where-Object { $_ }) -join '; '
                    if ($extSummary) { $extLabel = "ExternalTenants: $extSummary" }
                }
            }

            if ($extLabel) { $label = "$label ($extLabel)" }
            $includedObjects += $label
        }
    }

    # Included groups
    if ($policy.Conditions.Users.IncludeGroups) {
        foreach ($groupId in $policy.Conditions.Users.IncludeGroups) {
            $includedObjects += "Group: $($allGroups[$groupId] ?? $groupId)"
        }
    }

    # Included roles
    if ($policy.Conditions.Users.IncludeRoles) {
        foreach ($roleId in $policy.Conditions.Users.IncludeRoles) {
            $roleName = $roleTemplateMap[$roleId] ?? $roleId
            $includedObjects += "Role: $roleName"
        }
    }

    # Excluded users
    if ($policy.Conditions.Users.ExcludeUsers) {
        foreach ($userId in $policy.Conditions.Users.ExcludeUsers) {
            if ($userId -eq 'GuestsOrExternalUsers') {
                $excludedObjects += 'Guests or External Users'
            }
            else {
                $excludedObjects += $allUsers[$userId] ?? $userId
            }
        }
    }

    # Excluded guests/external users
    if ($policy.Conditions.Users.ExcludeGuestsOrExternalUsers) {
        $types = $policy.Conditions.Users.ExcludeGuestsOrExternalUsers.GuestOrExternalUserTypes

        # Only process if GuestOrExternalUserTypes is actually set
        if ($types) {
            $ext = $policy.Conditions.Users.ExcludeGuestsOrExternalUsers.ExternalTenants

            $label = "Guests or External Users: $types"

            $extLabel = $null
            if ($ext) {
                if ($ext.PSObject.Properties.Match('MembershipKind').Count -gt 0 -and $ext.MembershipKind) {
                    if ($ext.MembershipKind -eq 'all') { $extLabel = 'ExternalTenants: All' }
                }
                if (-not $extLabel -and $ext.PSObject.Properties.Match('Members').Count -gt 0 -and $ext.Members) {
                    $extLabel = "ExternalTenants: $($ext.Members -join ',')"
                }

                if (-not $extLabel) {
                    $pairs = $ext.PSObject.Properties |
                    Where-Object { $_.Name -ne 'AdditionalProperties' -and $_.Value } |
                    ForEach-Object {
                        if ($_.Value -is [System.Array]) { "$($_.Name)=$($($_.Value -join ','))" }
                        elseif ($_.Value -isnot [System.Collections.IDictionary]) { "$($_.Name)=$($_.Value)" }
                    }
                    $extSummary = ($pairs | Where-Object { $_ }) -join '; '
                    if ($extSummary) { $extLabel = "ExternalTenants: $extSummary" }
                }
            }

            if ($extLabel) { $label = "$label ($extLabel)" }
            $excludedObjects += $label
        }
    }

    # Excluded groups
    if ($policy.Conditions.Users.ExcludeGroups) {
        foreach ($groupId in $policy.Conditions.Users.ExcludeGroups) {
            $excludedObjects += "Group: $($allGroups[$groupId] ?? $groupId)"
        }
    }

    # Excluded roles
    if ($policy.Conditions.Users.ExcludeRoles) {
        foreach ($roleId in $policy.Conditions.Users.ExcludeRoles) {
            $roleName = $roleTemplateMap[$roleId] ?? $roleId
            $excludedObjects += "Role: $roleName"
        }
    }

    # Process target applications and user actions
    $targets = @()

    # Applications
    if ($policy.Conditions.Applications.IncludeApplications) {
        foreach ($appId in $policy.Conditions.Applications.IncludeApplications) {
            if ($appId -eq 'All') {
                $targets += 'All resources'
            }
            elseif ($appId -eq 'Office365') {
                $targets += 'Office 365'
            }
            else {
                $targets += $allApps[$appId] ?? $appId
            }
        }
    }

    # User actions
    if ($policy.Conditions.Applications.IncludeUserActions) {
        foreach ($action in $policy.Conditions.Applications.IncludeUserActions) {
            $actionName = switch ($action) {
                'urn:user:registersecurityinfo' { 'Register security information' }
                'urn:user:registerdevice' { 'Register or join devices' }
                default { $action }
            }
            $targets += "User Action: $actionName"
        }
    }

    if ($policy.Conditions.Applications.ExcludeApplications) {
        foreach ($appId in $policy.Conditions.Applications.ExcludeApplications) {
            $targets += "Excluded: $($allApps[$appId] ?? $appId)"
        }
    }

    # Process conditions
    $conditions = @()

    # Locations with named location display names
    if ($policy.Conditions.Locations) {
        if ($policy.Conditions.Locations.IncludeLocations) {
            $includeLocNames = @()
            foreach ($locId in $policy.Conditions.Locations.IncludeLocations) {
                if ($locId -eq 'All') {
                    $includeLocNames += 'All locations'
                }
                elseif ($locId -eq 'AllTrusted') {
                    $includeLocNames += 'All trusted locations'
                }
                else {
                    $includeLocNames += $locationMap[$locId] ?? $locId
                }
            }
            $conditions += "Include Locations: $($includeLocNames -join ', ')"
        }
        if ($policy.Conditions.Locations.ExcludeLocations) {
            $excludeLocNames = @()
            foreach ($locId in $policy.Conditions.Locations.ExcludeLocations) {
                if ($locId -eq 'AllTrusted') {
                    $excludeLocNames += 'All trusted locations'
                }
                else {
                    $excludeLocNames += $locationMap[$locId] ?? $locId
                }
            }
            $conditions += "Exclude Locations: $($excludeLocNames -join ', ')"
        }
    }

    # Platforms
    if ($policy.Conditions.Platforms) {
        if ($policy.Conditions.Platforms.IncludePlatforms) {
            $conditions += "Include Platforms: $($policy.Conditions.Platforms.IncludePlatforms -join ', ')"
        }
        if ($policy.Conditions.Platforms.ExcludePlatforms) {
            $conditions += "Exclude Platforms: $($policy.Conditions.Platforms.ExcludePlatforms -join ', ')"
        }
    }

    # Client app types
    if ($policy.Conditions.ClientAppTypes) {
        $conditions += "Client Apps: $($policy.Conditions.ClientAppTypes -join ', ')"
    }

    # Risk levels
    if ($policy.Conditions.UserRiskLevels) {
        $conditions += "User Risk: $($policy.Conditions.UserRiskLevels -join ', ')"
    }
    if ($policy.Conditions.SignInRiskLevels) {
        $conditions += "Sign-in Risk: $($policy.Conditions.SignInRiskLevels -join ', ')"
    }

    # Process grant controls
    $grantBlock = @()
    if ($policy.GrantControls) {
        if ($policy.GrantControls.BuiltInControls) {
            if ($policy.GrantControls.BuiltInControls -contains 'block') {
                $grantBlock += 'Block access'
            }
            else {
                $operator = if ($policy.GrantControls.Operator -eq 'AND') { ' AND ' } else { ' OR ' }
                $controls = $policy.GrantControls.BuiltInControls | ForEach-Object {
                    switch ($_) {
                        'mfa' { 'Require MFA' }
                        'compliantDevice' { 'Require compliant device' }
                        'domainJoinedDevice' { 'Require domain joined device' }
                        'approvedApplication' { 'Require approved app' }
                        'compliantApplication' { 'Require app protection policy' }
                        'passwordChange' { 'Require password change' }
                        default { $_ }
                    }
                }
                $grantBlock += "Grant: $($controls -join $operator)"
            }
        }

        if ($policy.GrantControls.CustomAuthenticationFactors) {
            $grantBlock += "Custom factors: $($policy.GrantControls.CustomAuthenticationFactors -join ', ')"
        }

        if ($policy.GrantControls.TermsOfUse) {
            $grantBlock += 'Terms of use required'
        }
    }

    # Process session controls
    $sessionControls = @()
    if ($policy.SessionControls) {
        if ($policy.SessionControls.ApplicationEnforcedRestrictions.IsEnabled) {
            $sessionControls += 'App enforced restrictions'
        }
        if ($policy.SessionControls.CloudAppSecurity.IsEnabled) {
            $sessionControls += "Cloud App Security: $($policy.SessionControls.CloudAppSecurity.CloudAppSecurityType)"
        }
        if ($policy.SessionControls.SignInFrequency.IsEnabled) {
            $sessionControls += "Sign-in frequency: $($policy.SessionControls.SignInFrequency.Value) $($policy.SessionControls.SignInFrequency.Type)"
        }
        if ($policy.SessionControls.PersistentBrowser.IsEnabled) {
            $sessionControls += "Persistent browser: $($policy.SessionControls.PersistentBrowser.Mode)"
        }
    }

    # Create output object
    [PSCustomObject]@{
        'Policy Name'      = $policy.DisplayName
        'State'            = $state
        'Included Objects' = $includedObjects -join '; '
        'Excluded Objects' = $excludedObjects -join '; '
        'Targets'          = $targets -join '; '
        'Conditions'       = $conditions -join '; '
        'Grant/Block'      = $grantBlock -join '; '
        'Session'          = $sessionControls -join '; '
        'Created'          = ConvertTo-FriendlyDate $policy.CreatedDateTime
        'Modified'         = ConvertTo-FriendlyDate $policy.ModifiedDateTime
    }
}

# Export to Excel
$excelParams = Get-DefaultExcelParams -Path $Out

$results | Export-Excel @excelParams -WorksheetName 'Conditional Access Policies' -Title "$($org.DisplayName) Conditional Access Policies" -TitleBold -TitleSize 14
$excel = $locationDetails | Export-Excel @excelParams -WorksheetName 'Named Locations' -PassThru

# Set column widths
$ws = $excel.Workbook.Worksheets['Conditional Access Policies']
$ws.Column(3).Width = 20  # Included Objects
$ws.Column(4).Width = 20  # Excluded Objects
$ws.Column(5).Width = 20  # Targets
$ws.Column(6).Width = 20  # Conditions
$ws.Column(7).Width = 20  # Grant/Block
$ws.Column(8).Width = 20  # Session
$ws.Column(9).AutoFit()   # Created
$ws.Column(9).Width += 3
$ws.Column(10).AutoFit()  # Modified
$ws.Column(10).Width += 3

$ws = $excel.Workbook.Worksheets['Named Locations']
$ws.Column(3).Width = 50  # Values
$ws.Column(5).AutoFit()   # Created
$ws.Column(5).Width += 3
$ws.Column(6).AutoFit()   # Modified
$ws.Column(6).Width += 3

Close-ExcelPackage $excel

Write-TimeLog "Exported to: $Out" -Color Green