Federation Applications : Certificate Expiry Report (CSV/HTML)

 When using Entra you will have applications that fall into these two categories:

1. Enterprise Application - SAML based applications with token signing certificates
2. App Registrations - OpenID based applications with secret keys that expire

It is always best to keep track of these applications and when they expire, for SAML and the token signing certificates you can get an e-mail notification when this is about to occur as shown below:

You can see the expiry date and also who gets the notification email when this certificate is about to expire which is convenient, however when it comes to client secret you have no such notification at all, you need to set a calendar entry to ensure you do not miss the expiry - unless you could script this to produce a website of such information.

Yes, that sounds like a plan, let get started.....

Pre-Flight Requirements

Note : You need to have an App Registration setup for this to work, for the basics on that you can follow the article here

You will then require the following application permissions for this script to work, how to use these permissions will be in the link above:

Microsoft.Graph/AuditLog.Read.All

Microsoft.Graph/Application.Read.All

Microsoft.Graph/Directory.Read.All

Obtain Data from Entra

Once this is completed we can move onto the script where you will need the relevant values from this newly create App Registration.

Script : FederationExpiry.ps1

# Import required modules

Import-Module Microsoft.Graph.Applications

Import-Module Microsoft.Graph.Authentication 

# App registration details

$tenantId = "<tenant_id>"

$clientId = "<client_id>"

$clientSecret = "<client_secret>"

# Create credential

$clientSecureString = ConvertTo-SecureString -String $clientSecret -AsPlainText -Force

$clientSecretCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $clientId, $clientSecureString

# Connect to Graph

Connect-MgGraph -TenantId $tenantId -ClientSecretCredential $clientSecretCredential

# Create Exports folder if it doesn't exist

if (-not (Test-Path "Exports")) {

    New-Item -ItemType Directory -Path "Exports"

    Write-Verbose "Created Exports directory"

}

# Arrays to store application details

$samlAppsDetails = @()

$openIDAppsDetails = @()

# Get enterprise applications (SAML)

$enterpriseApps = Get-MgServicePrincipal -All | Where-Object {

    $_.PreferredSingleSignOnMode -eq "saml"

}

foreach ($app in $enterpriseApps) {

    $keyCredentials = $app.KeyCredentials

    

    if ($keyCredentials) {

        foreach ($cert in $keyCredentials) {

            $samlAppsDetails += [PSCustomObject]@{

                ApplicationName = $app.DisplayName

                CertificateEndDate = $cert.EndDateTime

                CertificateThumbprint = ([System.BitConverter]::ToString($cert.CustomKeyIdentifier)).Replace("-","")

            }

        }

    } else {

        $samlAppsDetails += [PSCustomObject]@{

            ApplicationName = $app.DisplayName

            CertificateEndDate = "No certificate found"

            CertificateThumbprint = "No certificate found"

        }

    }

}

# Get application registrations with client secrets

$appRegistrations = Get-MgApplication -All

foreach ($app in $appRegistrations) {

    $appInfo = Get-MgApplication -ApplicationId $app.Id

    if ($appInfo.Web.ImplicitGrantSettings.EnableIdTokenIssuance) {

        $secrets = $appInfo.PasswordCredentials

        

        if ($secrets) {

            foreach ($secret in $secrets) {

                $openIDAppsDetails += [PSCustomObject]@{

                    ApplicationName = $app.DisplayName

                    SecretEndDate = $secret.EndDateTime

                    SecretId = $secret.KeyId

                }

            }

        } else {

            $openIDAppsDetails += [PSCustomObject]@{

                ApplicationName = $app.DisplayName

                SecretEndDate = "No client secret found"

                SecretId = "No client secret found"

            }

        }

    }

}

# Export to CSV files

$samlAppsDetails | Export-Csv -Path "Exports\SAML_Details.csv" -NoTypeInformation

$openIDAppsDetails | Export-Csv -Path "Exports\OpenID_Details.csv" -NoTypeInformation

Write-Host "Exported SAML applications details to Exports\SAML_Details.csv"

Write-Host "Exported OpenID applications details to Exports\OpenID_Details.csv"

Write-Host "SAML Applications: $($samlAppsDetails.Count)"

Write-Host "OpenID Applications: $($openIDAppsDetails.Count)"

Disconnect-MgGraph

Output Folders and Files

When this script is run you will end up with a Exports folder being created and inside that folder you will see two files which contain all your OpenID and SAML based applications:

The headers for the files are as follows:

OpenID : "ApplicationName","SecretEndDate","SecretId"
SAML : "ApplicationName","CertificateEndDate","CertificateThumbprint"

This means you have all the "raw" data in a CSV file, but that mean Excel and spreadsheets and nobody wants that, so lets beautify that basic and boring report and bring it alive with some modern HTML to extrapolate that data into a graphical format.

Beautify the data with HTML

We now need to rad those files exported earlier and make a HTML website from that data, but this all needs to be done in Powershell so when this script is run it will be updated and dynamic.

Script : BeautifyHTML.ps1

# Read the CSV files
$samlApps = Import-Csv -Path "Exports\SAML_Details.csv"
$openIDApps = Import-Csv -Path "Exports\OpenID_Details.csv"
function Parse-Date {
    param (
        [string]$dateString
    )   
    if ($dateString -eq "No certificate found" -or $dateString -eq "No client secret found") {
        return $null
    }   
    try {
        return [DateTime]::ParseExact($dateString, "dd/MM/yyyy HH:mm:ss", [System.Globalization.CultureInfo]::InvariantCulture)
    }
    catch {
        try {
            return [DateTime]::Parse($dateString, [System.Globalization.CultureInfo]::InvariantCulture)
        }
        catch {
            Write-Warning "Could not parse date: $dateString"
            return $null
        }
    }
}

# Group applications and process certificates
$groupedSamlApps = $samlApps | Group-Object -Property ApplicationName | ForEach-Object {
    $appName = $_.Name
    $certificates = $_.Group | Group-Object -Property CertificateThumbprint | ForEach-Object {
        $latestCert = $_.Group | ForEach-Object {
            @{
                Date = Parse-Date $_.CertificateEndDate
                Cert = $_
            }
        } | Where-Object { $_.Date -ne $null } | Sort-Object Date -Descending | Select-Object -First 1       
        if ($latestCert) {
            $latestCert.Cert
        } else {
            $_.Group | Select-Object -First 1
        }
    }
    @{
        Name = $appName
        Certificates = $certificates
    }
}
$groupedOpenIDApps = $openIDApps | Group-Object -Property ApplicationName
$html = @"
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Application Authentication Report</title>
    <style>
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            line-height: 1.6;
            margin: 0;
            padding: 20px;
            background-color: #f5f5f5;
        }
        .container {
            max-width: 1200px;
            margin: 0 auto;
            background-color: white;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);       
}
        h1 {
            color: #2c3e50;
            margin-bottom: 30px;
            padding-bottom: 10px;
            border-bottom: 2px solid #eee;
        }
        .tabs {
            margin-bottom: 20px;
            border-bottom: 2px solid #eee;
        }
        .tab-button {
            background: none;
            border: none;
            padding: 10px 20px;
            font-size: 16px;
            cursor: pointer;
            outline: none;
            position: relative;
            color: #666;
        }
        .tab-button.active {
            color: #2c3e50;
            font-weight: bold;
        }
        .tab-button.active::after {
            content: '';
            position: absolute;
            bottom: -2px;
            left: 0;
            width: 100%;
            height: 2px;
            background-color: #2c3e50;
        }
        .tab-content {
            display: none;
        }
        .tab-content.active {
            display: block;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 30px;
            background-color: white;
        }
        th {
            background-color: #f8f9fa;
            padding: 12px;
            text-align: left;
            border-bottom: 2px solid #dee2e6;
        }
        td {
            padding: 12px;
            border-bottom: 1px solid #dee2e6;
        }
        tr:hover {
            background-color: #f8f9fa;
        }
        .cert-list {
            list-style: none;
            padding: 0;
            margin: 0;
        }
        .cert-item {
            margin: 5px 0;
            padding: 5px;
            border-radius: 4px;
        }
        .status-badge {
            padding: 4px 8px;
            border-radius: 4px;
            font-size: 0.85em;
            margin-left: 8px;
        }
        .status-ok {
            background-color: #d4edda;
            color: #155724;
        }
        .status-warning {
            background-color: #fff3cd;
            color: #856404;
        }
        .status-expired {
            background-color: #f8d7da;
            color: #721c24;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>Federation Certificate Expiry Report</h1>       
        <div class="tabs">
            <button class="tab-button active" onclick="showTab('saml')">SAML Applications</button>
            <button class="tab-button" onclick="showTab('openid')">OpenID Applications</button>
        </div>
        <div id="saml" class="tab-content active">
            <table>
                <thead>
                    <tr>
                        <th>Application Name</th>
                        <th>Certificates</th>
                    </tr>
                </thead>
                <tbody>
"@
foreach ($app in $groupedSamlApps) {
    $certificateList = "<ul class='cert-list'>"
    foreach ($cert in $app.Certificates) {
        $endDate = Parse-Date $cert.CertificateEndDate       
        $status = if ($null -eq $endDate) {
            '<span class="status-badge status-warning">No Certificate</span>'
        } elseif ($endDate -lt (Get-Date)) {
            '<span class="status-badge status-expired">Expired</span>'
        } elseif ($endDate -lt (Get-Date).AddDays(30)) {
            '<span class="status-badge status-warning">Expiring Soon</span>'
        } else {
            '<span class="status-badge status-ok">Valid</span>'
        }
        $formattedDate = if ($null -ne $endDate) {
            $endDate.ToString("yyyy-MM-dd")
        } else {
            "No certificate found"
        }
        $certificateList += "<li class='cert-item'>Expiry: $formattedDate | Thumbprint: $($cert.CertificateThumbprint) $status</li>"
    }
    $certificateList += "</ul>"
    $html += @"
                    <tr>
                        <td>$($app.Name)</td>
                        <td>$certificateList</td>
                    </tr>
"@
}
$html += @"
                </tbody>
            </table>
        </div>
        <div id="openid" class="tab-content">
            <table>
                <thead>
                    <tr>
                        <th>Application Name</th>
                        <th>Client Secrets</th>
                    </tr>
                </thead>
                <tbody>
"@
foreach ($group in $groupedOpenIDApps) {    $secretList = "<ul class='cert-list'>"
    foreach ($app in $group.Group) {
        $endDate = Parse-Date $app.SecretEndDate       
        $status = if ($null -eq $endDate) {
            '<span class="status-badge status-warning">No Secret</span>'
        } elseif ($endDate -lt (Get-Date)) {
            '<span class="status-badge status-expired">Expired</span>'
        } elseif ($endDate -lt (Get-Date).AddDays(30)) {
            '<span class="status-badge status-warning">Expiring Soon</span>'
        } else {
            '<span class="status-badge status-ok">Valid</span>'
        }
        $formattedDate = if ($null -ne $endDate) {
            $endDate.ToString("yyyy-MM-dd")
        } else {
            "No client secret found"
        }
        $secretList += "<li class='cert-item'>Expiry: $formattedDate | ID: $($app.SecretId) $status</li>"
    }
    $secretList += "</ul>"
    $html += @"
                    <tr>
                        <td>$($group.Name)</td>
                        <td>$secretList</td>
                    </tr>
"@
}
$html += @"
                </tbody>
            </table>
        </div>
    </div>
    <script>
        function showTab(tabName) {
            document.querySelectorAll('.tab-content').forEach(tab => {
                tab.classList.remove('active');
            });
            document.querySelectorAll('.tab-button').forEach(button => {
                button.classList.remove('active');
            });
            document.getElementById(tabName).classList.add('active');
            event.target.classList.add('active');
        }
    </script>
</body>
</html>
"@
$html | Out-File -FilePath "Exports\ApplicationReport.html" -Encoding UTF8
Write-Host "HTML report generated at Exports\ApplicationReport.html"

This will the produce a report like this which clearly outlines the state of all the certificates (for SAML)

This will give you a key like this, obviously each tag speaks for itself:

This file will be stored in the Exports folder as you can see below: