Visualize upcoming Azure App Service certificate expiration using Azure Resource Graph and the Kusto Query Language, KQL.

Quickly discover any expiring certificates for Azure App Services

Discover expiring Azure App Service certificates using Azure Resource Graph queries.

1 Nov 2021

Operating distributed cloud solutions across many subscriptions and clouds can be quite a task. To achieve operational excellence and reliable service, there are many things to keep track of.

There are multiple ways to monitor certificates, both using built-in tools and third-party tools. I want to bring an easy way to tackle the common challenge of ensuring your SSL certificates do not expire.

In this post, I am making use of the Azure Resource Graph to make cross-subscription queries to see if I have any upcoming expiring certificates.

To learn more about using the Azure Resource Graph, read this post.
Get all the KQL queries on GitHub.

Azure Resource Graph queries

Let's break down this into a few simple queries that can come in handy.

Get all web app certificates

resources
| where type == "microsoft.web/certificates"

This will get you a list of all the app service certificates you have in your subscription(s).

You can view the details of each, and it will contain some information, including what you can see here:

Viewing the details of an Azure Graph Explorer query using KQL (Kusto Query Language) to retrieve any expiring certifications of app services.

Getting a raw list of all certificates can be beneficial if you want to export them to another tool for continued work. However, I like the capabilities of KQL (Kusto Query Language), and so we'll take a look at some further enhancements to our query below.

To simplify the overview, we can limit the properties we return:

resources
| where type == "microsoft.web/certificates"
| project resourceGroup, name, subscriptionId, properties.expirationDate, properties.thumbprint, properties.subjectName, properties.issuer

Get the Expiration Date of certificates

Now, I want to extend the property properties.expirationDate, which will allow me to filter and sort in various ways based on when the certificate expires.

There are a few interesting things we can do by extending the expiration date, for example:

Order By expiration date 💡
Group by or filter by the expiration year, month, etc.
Display the number of days until a certificate expires (more user friendly than displaying just the date)

Order by the expiration date.

resources
| where type == "microsoft.web/certificates"
// Extend the expiration date, enabling us to easier sort and filter by it.
| extend ExpirationDate = todatetime(properties.expirationDate)
| project ExpirationDate, resourceGroup, name, subscriptionId, properties.expirationDate, properties.thumbprint, properties.subjectName, properties.issuer
| order by ExpirationDate asc

Get certificates that expire soon.

Build on to the same query by adding the where clause, allowing us to select only the certificates that expire in the next X days. In my example, we're looking at the next 60 days.

resources
| where type == "microsoft.web/certificates"
// Extend the expiration date, enabling us to easier sort and filter by it.
| extend ExpirationDate = todatetime(properties.expirationDate)
| project ExpirationDate, resourceGroup, name, subscriptionId, properties.expirationDate, properties.thumbprint, properties.subjectName, properties.issuer
| where ExpirationDate < now() + 60d
| order by ExpirationDate asc

Get the number of days until expiration displayed for readability.

Additionally, we can define a variable for days until expiration, to make this easier to plot on charts and for readability, or just have it handy for your internal reviews and reports.

resources
| where type == "microsoft.web/certificates"
| extend ExpirationDate = todatetime(properties.expirationDate)
| extend DaysUntilExpiration = datetime_diff("day", ExpirationDate, now())
| project DaysUntilExpiration, ExpirationDate, resourceGroup, name, subscriptionId, properties.expirationDate, properties.thumbprint, properties.subjectName, properties.issuer
| where ExpirationDate < now() + 60d
| order by DaysUntilExpiration

See the Days until Expiration of certificates for Azure App Services.

Group by month for easy visualization

I've got a favorite query that I can easily plug into any of my Dashboards in Azure. This groups all expiring certificates by month and sorts them accordingly.

resources
| where type == "microsoft.web/certificates"
| extend ExpirationDate = todatetime(properties.expirationDate)
| extend ExpirationYear = getyear(ExpirationDate)
| extend ExpirationMonth = format_datetime(ExpirationDate, 'yyyy-MM')
| extend DaysUntilExpiration = datetime_diff("day", ExpirationDate, now())
| summarize count() by ExpirationMonth
| order by ExpirationMonth asc

This makes it easy to visualize the information:

Visualized App Service Certificate expiration by Month.

Clearly, I have some work to do in one of my subscriptions before April 🚀

You can also pin these results, and visualizations, to your Azure Dashboards:

Azure Dashboard of an application, now including expiring Azure App Certificates.

Summary and resources

In this post, we took a look at how to quickly make queries across your subscriptions to identify any upcoming expirations of Azure App Service SSL certificates. No rocket science, just something that is good to know about.

You can easily extend these queries beyond the basic examples I've shown here. I have additional queries that are beneficial when you manage a lot of applications across many subscriptions. Some use cases that come to mind:

Summarize by subscription
Summarize by management group(s)
Summarize by resource groups
Get already expired certificates (💣)
... really, anything you can think of. KQL is powerful.

There are multiple ways to keep track of expiring SSL certificates. For additional methods, here is some interesting reading.

Set up Application Insights with alerts for expiring SSL certificates:

Use a third-party tool for monitoring SSL certificates. This is a popular option with public-facing endpoints that do not require any advanced authentication: