Azure AD app registrations are at the heart of the Microsoft Identity Platform, and Microsoft recommend you rotate secrets on them often. However, there is currently no native way to alert on secrets that are due to expire. An expired secret means the application will no longer authenticate, so you may have systems that fail when the secret expires. A number of people have come up with solutions, such as using Power Automate or deploying an app that you can configure notifications with. Another solution is using Logic Apps, KQL and Microsoft Sentinel to build a very low cost and light weight solution.
We will need two Logic Apps for our automation – the first will query Microsoft Graph to retrieve information (including password expiry dates) for all our applications, our Logic App will then push that data into a custom table in Sentinel. Next we will write a Kusto query to find apps with secrets due to expire shortly, then finally we will build a second Logic App to run our query on a schedule for us, and email a table with the apps that need new secrets.
Both our Logic Apps are really simple, the first looks as follows –
The first part of this app is to connect to the Microsoft Graph to retrieve a token for re-use. You can set your Logic App to run on a recurrence for whatever schedule makes sense, for my example I am running it daily, so we will get updated application data into Sentinel each day. Then we will need the ClientID, TenantID and Secret for an Azure AD App Registration with enough permission to retrieve application information using the ‘Get application‘ action. From the documentation we can see that Application.Read.All is the lowest privilege access we will need to give our app registration.
Our first 3 actions are just to retrieve those credentials from Azure Key Vault, if you don’t use Azure Key Vault you can just add them as variables (or however you handle secrets management). Then we call MS Graph to retrieve a token.
Put your TenantID in the URI and the ClientID and Secret in the body respectively. Then parse the JSON response using the following schema.
{
"properties": {
"access_token": {
"type": "string"
},
"expires_in": {
"type": "string"
},
"expires_on": {
"type": "string"
},
"ext_expires_in": {
"type": "string"
},
"not_before": {
"type": "string"
},
"resource": {
"type": "string"
},
"token_type": {
"type": "string"
}
},
"type": "object"
}Now we have our token, we can re-use it to access the applications endpoint on Microsoft Graph to retrieve the details for all our applications. So use a HTTP action again, and this time use a GET action.
We connect to https://graph.microsoft.com/v1.0/applications?$select=displayName,appId,passwordCredentials
In this example we are just retrieving the displayname of our app, the application id and the password credentials. The applications endpoint has much more detail though if you wanted to include other data to enrich your logs.
One key note here is dealing with data paging in Microsoft Graph, MS Graph will only return a certain amount of data at a time, and also include a link to retrieve the next set of data, and so on until you have retrieved all the results – and with Azure AD apps you are almost certainly going to have too many to retrieve at once. Logic Apps can deal with this natively thankfully, on your ‘Retrieve App Details’ action, click the three dots in the top right and choose settings, then enable Pagination so that it knows to loop through until all the data is retrieved.
Then we parse the response once more, if you are just using displayname, appid and password credentials like I am, then the schema for your json is.
{
"properties": {
"value": {
"items": {
"properties": {
"appId": {
"type": "string"
},
"displayName": {
"type": "string"
},
"passwordCredentials": {
"items": {
"properties": {
"customKeyIdentifier": {},
"displayName": {
"type": [
"string",
"null"
]
},
"endDateTime": {
"type": [
"string",
"null"
]
},
"hint": {
"type": [
"string",
"null"
]
},
"keyId": {
"type": [
"string",
"null"
]
},
"secretText": {},
"startDateTime": {
"type": [
"string",
"null"
]
}
},
"required": [
"customKeyIdentifier",
"displayName",
"endDateTime",
"hint",
"keyId",
"secretText",
"startDateTime"
],
"type": [
"object",
"null",
"array"
]
},
"type": "array"
}
},
"required": [
"displayName",
"appId",
"passwordCredentials"
],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}Then our last step of our first Logic App is to send the data using the Azure Log Analytics Data Collector to Microsoft Sentinel. So take each value from your JSON and send it to Sentinel, because you will have lots of apps, it will loop through each one. For this example the logs will be sent to the AzureADApps_CL table.
You can then query your AzureADApps_CL table like you would any data and you should see a list of your application display names, their app ids and any password credentials. If you are writing to that table for the first time, just give it 20 or so minutes to appear. So now we need some KQL to find which ones are expiring. If you followed this example along you can use the following query –
AzureADApps_CL
| where TimeGenerated > ago(7d)
| extend AppDisplayName = tostring(displayName_s)
| extend AppId = tostring(appId_g)
| summarize arg_max (TimeGenerated, *) by AppDisplayName
| extend Credentials = todynamic(passwordCredentials_s)
| project AppDisplayName, AppId, Credentials
| mv-expand Credentials
| extend x = todatetime(Credentials.endDateTime)
| project AppDisplayName, AppId, Credentials, x
| where x between (now()..ago(-30d))
| extend SecretName = tostring(Credentials.displayName)
| extend PasswordEndDate = format_datetime(x, 'dd-MM-yyyy [HH:mm:ss tt]')
| project AppDisplayName, AppId, SecretName, PasswordEndDate
| sort by AppDisplayName desc You should see an output if any applications that have a secret expiring in the next 30 days (if you have any).
Now we have our data, the second Logic App is simple, you just need it to run on whatever scheduled you like (say weekly), run the query for you against Sentinel (using the Azure Monitor Logs connector), build a simple HTML table and email it to whoever wants to know.
If you use the same Kusto query that I have then the schema for your Parse JSON action is.
{
"properties": {
"value": {
"items": {
"properties": {
"AppDisplayName": {
"type": "string"
},
"AppId": {
"type": "string"
},
"PasswordEndDate": {
"type": "string"
},
"SecretName": {
"type": [
"string",
"null"
]
}
},
"required": [
"AppDisplayName",
"AppId",
"SecretName",
"PasswordEndDate"
],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}Now that you have that data in Microsoft Sentinel you could also run other queries against it, such as seeing how many apps you have created or removed each week, or if applications have expired secrets and no one has requested a new one; they may be inactive and can be deleted.
Microsoft Sentinel 101 