Power Automate Flow to fetch LCS environment metadata

In my first post we utilized the LCS API to start and stop cloud hosted environments via power automate. Since the API has evolved and we can now get environment metadata, environment history or the environment RSAT certificate, I decided to make another one. This time it’ll cover fetching the environment metadata through a power automate flow and write the results into an Excel sheet.

Prerequisities

First we need to create an Azure AD application registration. You can refer to the official documentation Database movement API – Authentication – Finance & Operations | Dynamics 365 | Microsoft Docs. For the authentication part we need to write down the client secret and the application id.

Flow to fetch metadata for all environments

We’ll start with fetching all the metadata for all the environments in our project. For demonstration purposes I choose to write the information into a simple Excel file which is located in my OneDrive. Here you can see all the data that is available through the API. Just insert the desired information as table headers or download the sample Excel file from my GitHub.

Save it to your OneDrive (for Business) and we’re ready to switch over to the Power Automate flow.

You can have a look at the overall flow here:

The flow is triggered by a manual action, then we authenticate us, initialize some variables and after that we call the LCS API to get the metadata from our project. With the response we’re going to update the Excel file.

Create a new instant cloud flow, add a new action and select the HTTP type. Insert the missing information and add another action to parse the JSON response. You can get the schema from a Postman call or download it here.

With these two steps we finished the authentication part already. Basically we’re calling the LCS API which then returns a bearer token that we can use to authenticate against the API.

Now we initialize two variables, “HasMorePages” and “Page”, with these variables we can fetch all the pages that are available.
The API call returns a boolean value “ResultHasMorePages“, which indicates if there are more results available. With the help of the “page” variable we can select which page we want to fetch.

Next step is a “Do until” – action. We’ll execute the API call, until the response says, that there aren’t any more pages available. I set the counter to 10, but in my example project I only needed two calls, as there were eight environments in the project and five were returned in the first API call.
Inside the “Do until” – action we start with another http-request. This time it’ll call the metadata URI: “https://lcsapi.lcs.dynamics.com/environmentinfo/v1/detail/project/”YourProjectId”/?page=1“.
We insert the bearer token from the authentication response and with the “page” variable we can select the desired page.

Then we parse the API response, the schema can either be generated by the sample response from the docs, or you can download it from GitHub.

After this step, we insert an “Apply to each” – action, the output from the previous step is the “Data” tag. Inside this action we place an Excel “Get a row” – action. We select the desired file and table, as well as the key column and the value with which we’ll search for an existing record in the file. We’ll go with the environment id as the id should remain the same when other values are changing.

As the search for an existing row can fail, we need to add two parallel actions. One to update the row, if the environment was already inserted in the file (left action). And one action to insert the information for an environment that is not yet present in the file (right action).

In both actions we simply select the data we want to insert in the columns that are present in the Excel file.
To switch between the actions, we need to set the “configure run after” – options on the action. Click on the three dots in the upper right corner and select the option.

For the successful outcome, we select the “is successful” option, if the flow doesn’t find a row, we select the “has failed” option.
For the successful outcome, we select the “is successful” option, if the flow doesn’t find a row, we select the “has failed” option.

Disclaimer: The Excel step is not part of my template, as I wasn’t able to hide my Excel credentials while exporting the flow.

Last step is to update our variables. The HasMorePages variable gets updated from the response. The page variable gets incremented by one. If there are more pages, the flow stays in the “Do until” – action, as long as there aren’t anymore pages.

When we start the flow, the Excel file will be updated with the environment information.

Normally we should also implement the 429 response – but to keep it simple I skipped it. Also because in my sample project, there were only two result pages. Calling the API twice is inside the limitations.

You can also use this example to get the history for an environment. Basically we just need to change the API call to call the history URI, change the response schema and of course change the Excel input.

Head over to GitHub to download the sample: LCSMetadataTemplate.zip

Original Post https://d365fostuff.wordpress.com/2021/09/13/power-automate-flow-to-fetch-lcs-environment-metadata/