Querying Resources in Azure Graph

JeremyBrooks

I had a customer reach out requesting how to query all certificates across all subs and figure out when they are expiring. Using Azure Resource Graph I came up with the query below that searches for all app services, find the bindings, and matches those apps up with the Microsoft.web/certificate object. Azure Graph is a powerful tool to query data using a familiar Data Explorer language that is very intuitive.

Happy scripting!

 
Resources
| where type =~ 'Microsoft.Web/sites'
| extend bindings = parse_json(properties.hostNameSslStates)
| mv-expand bindings
| summarize by tostring(bindings.thumbprint), tostring(bindings.name), name, kind, location, resourceGroup, subscriptionId
| project-rename thumbprint = bindings_thumbprint
| join(
Resources 
| where type =~ "Microsoft.Web/certificates"
| summarize by tostring(properties.expirationDate), tostring(properties.thumbprint), tostring(properties.subjectName)
| project-rename thumbprint= properties_thumbprint)
on thumbprint