Use Power Automate to update Azure DevOps queries

Erik Reitan

In this blog post, I’ll explain how to set up automation to automatically update Azure DevOps (ADO) queries. Using an automated Power Automate flow, you save time by not having to manually update your queries for each sprint. Everyone using your queries benefits by having data and details that are always current and accurate.

Solution overview

The solution uses a Power Automate flow to update ADO queries. The queries contain a sprint number. For example, the sprint query contains 2303. This number represents the third month (March) of the year 2023. Fortunately, the sprints that I’m working with follow strictly to the month.

The following query is an example of a simple sprint query:

In addition, the previous sprint queries contain 2302, and the next sprint query contains 2304. We use dozens of sprint queries for each sprint, so having an automated update process is a big time saver.

I’ve organized the queries into an overall folder called AllSprints. The AllSprints folder contains the PreviousSprint, CurrentSprint, and NextSprint folders. Each of these folders contains queries.

The flow is set to automatically run on the first of each month. Variables within the flow are created to track query paths, query folders, query numbers, and the Wiql value. The Work Item Query Language (Wiql) value defines your query as a hyperlink and can easily be modified as a string variable.

You use an array to loop through the sprint folders. Within this loop, you’ll determine all the queries that must be updated. Then, you use another loop to step through each query to determine if you need to update it. Updating a query involves changing the sprint number within the Wiql value of the query. Once all queries have been checked and updated, the flow finishes.

Basic query update process

The solution finds each query ID by using the List queries within folder action. It then uses the results to help you GET the details of each query by using the Send HTTP request to Azure DevOps action (shown below). The results allow you to find the Wiql value for each query. You then use a Send an HTTP request to Azure DevOps action again to UPDATE the Wiql value for the related query. All of these steps are detailed in the information below.

Complete query update process

The flow starts with a Recurrence action that is set to trigger at the beginning of each month.

Next, the following variables are created:

• A string variable containing the path of the primary folder containing the sprint subfolders.
• String variables containing the name of the previous, current, and next sprint subfolders.
• An array containing the sprint subfolder names.
• A string variable containing the primary sprint path.
• String variables containing the previous, current, and next sprint numbers.
• String variables containing the former values for the previous, current, and next sprint numbers.
• A string variable for the Wiql value.

To find the queries, you use an Apply to each condition to step through the sprint array created above. Within this loop, you use the List queries within folder action to determine all the queries that you need to update.

Then, you use another Apply to each action to do the main work.

The above Apply to each – Query node steps through the values returned from List queries within folder – Each folder. At this point, you need to get the query details for each query. You use the action Send HTTP request to Azure DevOps to pass the GET method along with URL values containing the ID of the current query.

Next, you must use the Parse JSON action to get the values from the HTTP request.

The schema that I used is the following JSON:

{ 
    "type": "object", 
    "properties": { 
        "id": { 
            "type": "string" 
        }, 
        "name": { 
            "type": "string" 
        }, 
        "path": { 
            "type": "string" 
        }, 
        "queryType": { 
            "type": "string" 
        }, 
        "wiql": { 
            "type": "string" 
        }, 
        "isPublic": { 
            "type": "boolean" 
        } 
    } 
} 

Additionally, you can set the Wiql output to a string variable so you can modify it later:

Next, you must check which sprint is contained in the path of the query by checking if the path contains the string variable for the next sprint (strNextSprintName). For instance, if the current sprint is 2303 and the next sprint is 2304, you must check if the path contains 2304. Note that the path was defined from the above JSON.

Additionally, you must update the Wiql to use the next sprint number, in this case 2304. To do this, you update the strWiql using the replace method.

The replace expression is the following:

replace(uriComponentToString(replace(body('Parse_JSON')?['wiql'],variables('strFormerNextSprintNumber'),variables('strNextSprintNumber'))),'\','\\') 

Note that we used two replace methods to make sure that the Wiql value was properly escaped.

You must follow the same pattern to check the current sprint value and update it, as well as the previous sprint value.

Next, you must use another Condition action to check whether strWiql has changed. If it has, you then use the Send an HTTP request to Azure DevOps action to update the wiql value for the related query.

To complete the loop, you can use a Set variable action to clear strWiql.

Once you have successfully built and saved your Power Automate flow, you can run the Flow checker and Test the flow.

If you’re interested in learning more about Power Automate, see the following resources:

• Overview of cloud flows
• Add multiple actions and advanced options to a cloud flow
• Add a condition to a cloud flow
• Reference guide to workflow expression functions in Azure Logic Apps and Power Automate

Erik Reitan Senior Content Developer, Microsoft Intune

Follow