Azure Logic App- Fetch Secret Expiry Date & Other Metadata into a CSV

Use logic app to read and export expiry date of a secret along with all the other key information into a csv file.

Credit — https://securityintelligence.com/the-inconvenient-truth-about-your-eight-character-password/

In a previous article of mine, I explained how we can configure an azure logic app to read a secret’s value. That article showed what steps are required to authenticate a logic app to a key vault for it to read secrets.

We need to complete those steps as a prerequisite for this article.

Azure — Fetch Key Vault Secrets Using Logic Apps

How to fetch a secret from Azure Key vault using logic apps

medium.com

Now, as a data engineer, let’s create a use case where instead of a secret’s value, we need to get the expiry date of a secret to check if any secret values are nearing expiration and need renewal. We will also throw in additional metadata for the secrets.

We need to export all the metadata for all the secrets in the key vault. Metadata includes the following information- 1. Name of secret 2. Status 3. Expiration date 4. Additional information which you will see at the end of the article.

As an example, I created a sample key vault that contains 3 sample secrets as shown below-

The Design

The trigger for this logic app will be a schedule that runs every week. Next step includes getting a list of all the secrets from the key vault. For this, we need to use List secrets activity.

Since we are using managed identity to authenticate logic app (steps described in the article referenced at the top), logic apps would automatically detect the managed identity authenticator.

After the above step, search for compose activity and add it to the logic app workflow.

The input to the compose activity will be the value array that is received in the output of the list secrets activity. The value array contains metadata about all the secrets in the key vault. Compose helps to create a single output from multiple inputs.

To check the output of list secrets, you can choose not to add compose activity and run the logic app only until list secrets to check the output.

Finally, we can add Create CSV table activity to format the output of compose action into a CSV file.

The input to this activity is the output of the compose activity. I have kept the columns to be automatically identified by logic app. You can also change the selection from automatic to custom in case you need to rename the columns or create a synthetic column.

The Result

At this step, we have successfully created a CSV table in logic app with all the secrets metadata. Running the pipeline gives us the below output —

Looking at the output, logic app provided us with additional columns that were not visible in the key vault such as version, createdTime, lastUpdatedTime and validityStartTime.

If needed, we can export the csv output to an azure blob storage by using the create blob activity.

What the csv will not contain are the secret values for any of the secrets.

Before Wrapping up

As a final step, you can add an automation script that reads the data from csv files stored in blob storage to analyze which secrets’ expiry date is nearby and alert the data engineering team to renew those secrets.