Using a Logic App to send a recurring report for App Registration Certificates & secrets expiry

As a follow up to my previous post:

Using PowerShell to send custom log data to Log Analytics for Azure Monitor alerting and Kusto Query
https://blog.terenceluk.com/2022/03/using-powershell-to-send-custom-log.html

I recently looked into how the Certificates & secrets configured for Azure AD App Registrations could be monitored so administrators could be warned ahead of the expiry because of a recommendation I proposed for a client where their Azure team would experience outages due to expired credentials. This client had hundreds of partners that authenticate against their Azure AD and the unplanned downtimes were a major pain point.

As indicated in my previous post and for those who have attempted this will know that Azure does not provide a native built-in method for monitoring them but there are two solutions provided by the following professionals:

App Registration Expiration Monitoring and Notifications – By Christopher Scott
https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/app-registration-expiration-monitoring-and-notifications/ba-p/2043805

Use Power Automate to Notify of Upcoming Azure AD App Client Secrets and Certificate Expirations – By Russ Rimmerman
https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/use-power-automate-to-notify-of-upcoming-azure-ad-app-client/ba-p/2406145

I liked the solution that Christopher Scott created so I went ahead and tested it but ran into an issue caused by the updated Az.Resources module as described in this post:

Attempting to use Chris Scott’s App Registration Expiration Monitoring and Notifications displays data with “DaysToExpiration” set to “-738,241”
https://blog.terenceluk.com/2022/03/attempting-to-use-chris-scotts-app.html

I also noticed that the kusto query he provided did not cover App Registrations that had more than one secret or certificate configured. Lastly, I preferred to have a pre-scheduled report sent out with a list of expiring certificates and secrets so this post serves to outline the configuration and some minor tweaks to this great solution.

Create Log Analytics Workspace

Begin by creating a Log Analytics Workspace that will store the App Registration information collected by an Automation Account Powershell Runbook:

With the Log Analytics workspace created, navigate to the Agents management blade to collect the following information:

1. Workspace ID
2. Workspace Primary ID

These two parameters will be required for setting up the Automation Account so it can send custom Log Analytics data to this workspace.

Create and Configure App Registration for Automation Account Runbook

The Automation Account Runbook that we’ll be creating shortly will be using a service principal to execute a PowerShell script that will collect every App Registration and Enterprise Applications / Service Principal along with their configured certificates and secrets. This service principal will need Global Reader rights to obtain the information.

**Note that we can also use a Managed Identity for the execution of the PowerShell script as shown in one of my previous blog posts: https://blog.terenceluk.com/2022/02/using-automation-account-to-monitor-vms.html

With the App Registration created, document the following information:

1. Application (client) ID <- this represents the service principal’s user name
2. Directory (tenant) ID

Proceed to create a client secret in the Certificates & secrets blade and document the value of the secret before navigating away from the page as it would not be displayed again:

Next, navigate to the Roles and administrators blade for the Azure AD tenant, search for Global Reader and add the service principal to the role:

Create Automation Account

With the prerequisites for the Automation Account configured, the next is to create the new Automation Account that will use a PowerShell script to extract the App Registrations along with the configured certificates and secrets, and their expiry dates to send into the Log Analytics Workspace:

Configure Variables and Credential Automation Account

A runbook using a PowerShell script will be used to obtain the certificates and secrets of App Registrations by authenticating against Azure AD and accessing the Log Analytics Workspace to send data. It is best practice to store variables and credentials outside of the script so we’ll store them securely within the Variables and Credentials blade of the Automation Account.

Navigate to the Variables blade and configure the following 3 variables:

1. MonitoredTenantID
2. WorkspaceID
3. WorkspacePrimaryKey

It is possible to store these variables as encrypted to further protect the values and for the purpose of this example, I will only store the Workspace Primary Key as encrypted:

Navigate to the Credentials blade and configure the App Registration / Service Principal credential that the PowerShell script will use to authenticate against Azure AD:

Create and Configure an Automation Account Runbook

With the credentials and variables for the PowerShell script configured, navigate to the Runbooks blade and create a new runbook:

Provide the following:

Name: <name of choice>
Runbook type: <PowerShell>
Runtime version: <5.1>
Description: <description of choice>

You will be brought into Edit window of the Runbook after creating it:

Proceed to paste the following PowerShell script into the window: https://github.com/terenceluk/Azure/blob/main/PowerShell/Get-AppRegistrationExpirationAutomation.ps1

A more detailed breakdown of what the script does can be found in my previous blog post: https://blog.terenceluk.com/2022/03/using-powershell-to-send-custom-log.html

I will also include the PowerShell script at the end of this blog post.

Note how the script references the credentials and the variables we defined earlier for the Automation Account:

Use the Test pane feature to execute a test and verify that the script runs without any errors:

A return code of 200 is what we’re looking for:

Proceed to publish the script:

Next, schedule the Runbook to run accordingly to a day, time, and frequency of your choice:

With the runbook configured, proceed to test executing the Runbook and confirm that the desired data is being sent to the Log Analytics Workspace:

Set up reporting with Logic Apps

With the Automation Account and Runbook successfully set up and verified, proceed to create a new Logic App that will query the data in the Log Analytics Workspace, generate a HTML report and send it to the desired email address.

Navigate into the Logic app and create a new Logic App:

Once the Logic App has been created, click on Logic app designer:

We’ll be creating 3 steps for this Logic App where:

1. Recurrence: This will configure a recurring schedule for this Logic App to execute
2. Run query and visualize results: This will allow us to run the Kusto query, set a Time Range and specify a Chart Type
3. Send an email (V2): This will allow us to send the Kusto query results via email

Recurrence:

Configure the desired frequency of this Logic App:

Run query and visualize results:

Fill in the following fields:

Subscription: <your subscription>
Resource Group: <desired resource group>
Resource Type: Log Analytics Workspace
Resource Name: The Log Analytics Workspace containing the custom log data>
Query: See the query in my GitHub: https://github.com/terenceluk/Azure/blob/main/Kusto%20KQL/Get%20Expiring%20and%20Expired%20App%20Reg%20Certs%20and%20Secrets.kusto

AppRegistrationExpiration_CL

| summarize arg_max(TimeGenerated,*) by KeyId_g // arg_max is used to return the latest record for the time range selected and the * is to return all columns, records with unique KeyId_g will be returned so expired and multiple credentials are returned

| where DaysToExpiration_d <= 1000 or Status_s == “Expired” // this specifies a filter for the amount of days before expiry

//| where TimeGenerated > ago(1d) // the TimeGenerated value must be within a day

| project TimeGenerated, Display_Name = DisplayName_s, Application_Client_ID = ApplicationId_Guid_g, Object_ID = ObjectId_g, Cert_or_Secret_ID = KeyId_g, Credential_Type = Type_s, Start_Date = StartDate_value_t, End_Date = EndDate_value_t, Expiration_Status = Status_s, Days_To_Expire = DaysToExpiration_d, Directory_Tenant_ID = TenantId // the columns have been renamed to easier to understand headings

Time Range: Last 24 hours
Chart Type: HTML Table

Let’s break down the Kusto query line by line:

Look up data in the following log:
AppRegistrationExpiration_CL

Retrieve only the latest records based on the TimeGenerated field using the unique KeyID_g field that represents the certificate or secret ID as a filter (this will cover App Registrations that have multiple certificates or secrets configured):
| summarize arg_max(TimeGenerated,*) by KeyId_g // arg_max is used to return the latest record for the time range selected and the * is to return all columns, records with unique KeyId_g will be returned so expired and multiple credentials are returned

Only list records where the certificate or secret will be expiring in 50 days or if it has already expired:
| where DaysToExpiration_d <= 50 or Status_s == “Expired” // this specifies a filter for the amount of days before expiry

Only list records with the TimeGenerated field within a day:
//| where TimeGenerated > ago(1d) // the TimeGenerated value must be within a day

Project the fields of interest for the report and rename them to more meaningful names:
| project TimeGenerated, Display_Name = DisplayName_s, Application_Client_ID = ApplicationId_Guid_g, Object_ID = ObjectId_g, Secret_ID = KeyId_g, Credential_Type = Type_s, Start_Date = StartDate_value_t, End_Date = EndDate_value_t, Expiration_Status = Status_s, Days_To_Expire = DaysToExpiration_d, Directory_Tenant_ID = TenantId // the columns have been renamed to easier to understand headings

Send an email (V2):

The report will include the Attachment Content and Attachment Name derived from the query with the subject Certificate & Secrets Expiry Report. The email will look pretty barebone so you are free to add HTML code to pretty it up.

Proceed to save the Logic App and use the Run Trigger to test the Logic App and confirm that an email is sent:

**Note that I have set the kusto query to return records with an expiry of 1000 days or less so more records would return.

Hope this helps anyone who may be looking for a way to set up a Logic App for sending recurring reports of expiring App Registrations’ Certificates and Secrets information that were sent to a custom Log Analytics data.