Disclaimer
The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.
Are you having challenges keeping up with all your Azure Active Directory Enterprise Application client secrets and certificates and their associated expiration dates? My first solution created to solve this challenge was blogged here and leverages Power Automate. Since this original blog post has now received over 18k views (to date) and I've been asked by many to create a Logic Apps based version, I'm now ready to share my ARM template to enable you to not only automatically create the resources in your subscription, but also to leverage Azure Key Vault to store the client secret, tenant and client ID securely. You can find my original blog post using Power Automate here.
A peer of mine recently asked me if I could help him make a Logic App out of the Flow for his customer. Thanks to a friend and peer Norman Drews for his CSS & HTML expertise, and Asraf CH and Puspraj Jaiswal for providing additional testing of my Logic App and helping me turn it into an ARM template. The ARM template automatically creates the Logic App, API connections, and an Azure Key Vault to store the client ID, tenant ID, and client secret of the AAD app that that you’ll create which is granted the needed access to Microsoft Graph. This Logic App version of the automation also checks to see if there is an application owner identified on each application, and if there is less than 15 days (configurable) until the secret or certificate expires, it will also send an e-mail to the application owner.
PLEASE take a moment to provide your feedback on this solution! https://forms.office.com/r/b7hHXLb7R1
Perform the following steps to implement this solution:
- Download the file armTemplate.json from my gitHub repository here.
- Create (or use an existing) Azure AD app registration that has ONE of the following Application Permissions to Microsoft Graph (starting from the least and ending with the most restrictive option) - Application.Read.All, Application.ReadWrite.All, Directory.Read.All, or Directory.AccessAsUser.All.
- Create an Azure Key Vault by:
- Logging into your Azure Portal and search for “Key Vault”
- Click “Create”.
- Choose the desired Azure Subscription, Resource Group, and enter a Key Vault name.
- Select the region
- Click “Review + create” and then “Create”
- Click “Go to resource” to go to the Key Vault you created.
- Click “Secrets”
- Click “Generator/import” to create 3 secrets, one to hold your tenant ID (name it “tenant-id”), one for the client ID (named “client-id”), and one for the client secret (name it “client-secret”). If you don’t want to use these names, you will need to modify the JSON template to reflect the names you use for each.
- Login to Azure and search for “Template deployment (deploy using custom templates)” in the Marketplace.
- Click “Build your own template in the editor”
- Open the armTemplate.JSON file from step 1 and edit line 290 to reflect the e-mail addresses to send the notification to, separated by semi-colons. If you modified the Key Vault secret names, you’ll also need to edit Lines 41, 66, and 324 to reflect the new name.
- Paste the content of the armTemplate.JSON to the editor (overwriting the sample text) and click save.
- Choose the same subscription, resource group, and enter the Azure Key Vault name you created in step 2 in “Keyvault-name”. Leave “Office365-connection” and “Keyvault-connection” as they are.
- Click Review + Create -> Create -> Create to deploy the template.
- Click “Go to resource”
- Open the newly created Logic App named “azure-application-notification” and click “API Connection”.
- Click “Office365”
- Click “Edit API Connection”
- Click “Authorize”, login when prompted, and cilck “Save”
- Do the same steps for the keyvault API connection.
- Click “Logic App Designer”
- Open the very step named “Send the list of applications”
- Select the office365 connection and sign in.
- Click “Run Trigger” to test it.
Here’s the full Logic App with explanation of each step:
Recurrence – How often the Logic App will run – this is set to 1 day by default.
There are 3 Key Vault steps to gather the Tenant ID, Client ID, and Client Secret of the Azure AD application you created with Microsoft Graph permissions.
Initialize variable (String) – appId – this is the appID of the application.
Initialize variable (String) – displayName – this will be used to identify the display name of the application.
Initialize variable (Array) – passwordCredentials – this variable will be used to populate the client secrets of each Azure AD application.
Initialize variable (Array) – keyCredentials – this variable will be used to populate the certificate properties of each Azure AD application.
Initialize variable (Object) – styles – this is some CSS styling to highlight Azure AD app secrets and expirations that are going to expire in 30 days (yellow) vs 15 days (red). You can adjust these values accordingly to meet your needs.
Content of this step:
{
"tableStyle": "style=\"border-collapse: collapse;\"",
"headerStyle": "style=\"font-family: Helvetica; padding: 5px; border: 1px solid black;\"",
"cellStyle": "style=\"font-family: Calibri; padding: 5px; border: 1px solid black;\"",
"redStyle": "style=\"background-color:red; font-family: Calibri; padding: 5px; border: 1px solid black;\"",
"yellowStyle": "style=\"background-color:yellow; font-family: Calibri; padding: 5px; border: 1px solid black;\""
}
Initialize variable (String) – html – this creates the table headings and rows that will be populated with each of the Azure AD applications and associated expiration info.
Content of this step:
<table @{variables('styles').tableStyle}><thead><th @{variables('styles').headerStyle}>Application ID</th><th @{variables('styles').headerStyle}>Display Name</th><th @{variables('styles').headerStyle}>Days until Expiration</th><th @{variables('styles').headerStyle}>Type</th><th @{variables('styles').headerStyle}>Expiration Date</th></thead><tbody>
Initialize variable (Float) – daysTilExpiration – this is the number of days prior to client secret or certificate expiration to use to be included in the report
Get Auth Token - Requests an authentication token using our tenant-Id, client-Id, and client-secret gathered from the Key Vault.
The Parse JSON step will parse all the properties in the returned token request.
The JSON schema to use is as follows:
{
"type": "object",
"properties": {
"token_type": {
"type": "string"
},
"expires_in": {
"type": "integer"
},
"ext_expires_in": {
"type": "integer"
},
"access_token": {
"type": "string"
}
}
}
Initialize variable (String) – NextLink – This is the Graph API URI to request the list of Azure AD applications. The $select only returns the appId, DisplayName, passwordCredentials, and keyCredentials, and since graph API calls are limited to 100 rows at a time, I bumped my $top up to 999 so it would use less API requests (1 per 1000 apps vs 10 per 1000 apps).
Next, we enter the Until loop. It will perform the loop until the NextLink variable is empty. The NextLink variable will hold the @odata.nextlink property returned by the API call. When the API call retrieves all the applications in existence, there is no @odata.nextlink property. If there are more applications to retrieve, the @odata.nextlink property will store a URL containing the link to the next page of applications to retrieve.
The next step in the Until loop uses the HTTP action to retrieve the Azure AD applications list. The first call will use the URL we populated this variable within step 15.
A Parse JSON step is added to parse the properties from the returned body from the API call.
The content of this Parse JSON step is as follows:
{
"type": "object",
"properties": {
"@@odata.context": {
"type": "string"
},
"value": {
"type": "array",
"items": {
"type": "object",
"properties": {
"appId": {
"type": "string"
},
"displayName": {
"type": "string"
},
"passwordCredentials": {
"type": "array",
"items": {
"type": "object",
"properties": {
"customKeyIdentifier": {},
"displayName": {},
"endDateTime": {},
"hint": {},
"keyId": {},
"secretText": {},
"startDateTime": {}
},
"required": []
}
},
"keyCredentials": {
"type": "array",
"items": {
"type": "object",
"properties": {
"customKeyIdentifier": {},
"displayName": {},
"endDateTime": {},
"key": {},
"keyId": {},
"startDateTime": {},
"type": {},
"usage": {}
},
"required": []
}
}
},
"required": []
}
},
"@@odata.nextLink": {
"type": "string"
}
}
}
A Get future time action will get a date in the future based on the number of days or months you’d like to start receiving notifications prior to expiration of the client secrets and certificates. This is set to 1 month by default in the template.
Next a Foreach – apps loop will use the value array returned from the Parse JSON step of the API call to take several actions on each Azure AD application.
Set variable (String) – appId – uses the appId variable we initialized in step 3 to populate it with the application ID of the current application being processed.
Set variable (String) – displayName – uses the displayName variable we initialized in step 4 to populate it with the displayName of the application being processed.
Set variable (String) – passwordCredentials – uses the passwordCredentials variable we initialized in step 8 to populate it with the client secret details of the application being processed.
Set variable (String) – keyCredentials – uses the keyCredentials variable we initialized in step 9 to populate it with the certificate details of the application being processed.
A foreach will be used to loop through each of the client secrets within the current Azure AD application being processed.
The output from the previous steps to use for the foreach input is the passwordCreds variable.
A condition step is used to determine if the Future time from the Get future time step 19 is greater than the endDateTime value from the current application being evaluated.
If the future time isn’t greater than the endDateTime, we leave this foreach and go to the next one.
If the future time is greater than the endDateTime, we first convert the endDateTime to ticks. Ticks is a 100-nanosecond interval since January 1, 0001 12:00 AM midnight in the Gregorian calendar up to the date value parameter passed in as a string format. This makes it easy to compare two dates, which is accomplished using the expression ticks(item()?[‘endDateTime’]).
Next, use a Compose step to convert the startDateTime variable of the current time to ticks, which equates to ticks(utcnow()).
Next, use another Compose step to calculate the difference between the two ticks values, and re-calculate it using the following expression to determine the number of days between the two dates.
This equates to the following equation:
(Start Time – End Time) X 100 (ns) / 1000000000 (s) / 3600 (minutes) / 24 (hours)
This translates into the following Logic App expression:
div(div(div(mul(sub(outputs('EndTimeTickValue'),outputs('StartTimeTickValue')),100),1000000000) , 3600), 24)
Set the variable daystilexpiration to the output of the previous calculation.
Another http call is made to get the Azure AD application owner, if there is one.
A condition is set to check if the length of the owner is blank/empty by comparing it against the expression int(0). If it’s not, it will append a mailto: tag with the owner’s e-mail address to the HTML and show the DisplayName of the owner as the text with a clickable link to their e-mail. Otherwise, it will append “No Owner” to it. If there is an owner identified, a Compose step is used to build the e-mail which will be sent to the AAD app owner.
Set variable (String) – html – creates the HTML table using the CSS styling. This is where you can adjust how many days prior to expiration will be highlighted red and how many will be highlighted in yellow (change the 15 and 30 values within). The content of this step is as follows:
<tr><td @{variables('styles').cellStyle}><a href="https://ms.portal.azure.com/#blade/Microsoft_AAD_RegisteredApps/ApplicationMenuBlade/Credentials/appId/@{variables('appId')}/isMSAApp/">@{variables('appId')}</a></td><td @{variables('styles').cellStyle}>@{variables('displayName')}</td><td @{if(less(variables('daystilexpiration'),15),variables('styles').redStyle,if(less(variables('daystilexpiration'),30),variables('styles').yellowStyle,variables('styles').cellStyle))}>@{variables('daystilexpiration')} </td><td @{variables('styles').cellStyle}>Secret</td><td @{variables('styles').cellStyle}>@{formatDateTime(item()?['endDateTime'],'g')}</td></tr>
Another foreach will be used to loop through each of the certificates within the current Azure AD application being processed. This is a duplication of steps 25 through 33 except that it uses the keyCredentials as its input, compares the future date against the currently processed certificate endDateTime, and the Set variable – html step is as follows (using the example of when there IS an AAD app owner specified):
<tr><td @{variables('styles').cellStyle}><a href="https://ms.portal.azure.com/#blade/Microsoft_AAD_RegisteredApps/ApplicationMenuBlade/Credentials/appId/@{variables('appId')}/isMSAApp/">@{variables('appId')}</a></td><td @{variables('styles').cellStyle}>@{variables('displayName')}</td><td @{if(less(variables('daystilexpiration'), 15), variables('styles').redStyle, if(less(variables('daystilexpiration'), 30), variables('styles').yellowStyle, variables('styles').cellStyle))}>@{variables('daystilexpiration')} </td><td @{variables('styles').cellStyle}>Certificate</td><td @{variables('styles').cellStyle}>@{formatDateTime(item()?['endDateTime'], 'g')} <td @{variables('styles').cellStyle}><a href=\"mailto:@{body('Get_Secret_Owner')?['value'][0]?['userPrincipalName']}\">@{body('Get_Secret_Owner')?['value'][0]?['givenName']} @{body('Get_Secret_Owner')?['value'][0]?['surname']}</a></td></tr>"
Immediately following the foreach – apps loop, as a final step in the Do while loop is a Set NextLink variable which will store the dynamic @odata.nextlink URL parsed from the JSON of the API call.
Append to variable (Array) – html – Immediately following the Do while loop ends, we close out the html body and table by appending <tbody></table> to the variable named html.
Finally, send the HTML in a Send an e-mail (V2) action, using the variable html for the body of the e-mail.
And below is the resulting e-mail received when the flow runs at its scheduled time. Included in the Application ID column is a hyperlink for each application that takes you directly to where you need to update the client secret and/or certificates for each application within the Azure portal, and in the Owner column is the mailto: hyperlink to notify the application owner. They will automatically receive an e-mail as part of this template, but you can optionally use this to send them additional notifications.
PLEASE take a moment to provide your feedback on this solution! I really appreciate it. https://forms.office.com/r/b7hHXLb7R1
Posted at https://sl.advdat.com/3xHUxU8