How-to Manage Queries Automatically

Query creation can be repetitive and time consuming. This guide describes how to manage queries automatically.

Automatic pipelines can export and import sets of queries and apply parameters to define consistent queries across multiple projects, teams, iterations, and areas.

Intended for

Configuration managers, quality control managers, release owners, and anyone who needs to create queries repeatedly.

Activities

Export queries

Export a set of sample queries to use as a template.

Run pipeline ExportQueries directly from the OpEx project. This pipeline can export queries from any given project, even in a different Azure DevOps (ADO) organization, provided that the selected agent can connect to it.

Tips and tricks: If your project is on premises, create a new branch or a copy of the pipeline to run on one of your agents connected to the ABB network.

When starting the pipeline, specify parameters and variables as follows:

Parameter	Description
Branch	"main" or your own branch
Query Path	Path to the folder of queries you want to export. All subfolders will be exported. E.g. "Shared Queries\Quality"
Organization URL	URL of your organization. This can be different from the organization where the pipeline is running, e.g "https://dev.azure.com/ABB-BCI-PCP"
Name of the ADO project	The name of the ADO project that contains queries, e.g. "PCP".
Variables > exportToken	Token with permissions to read work items from the organization that contains the queries.

The pipeline exports queries and publishes a pipeline artifact that contains queries and queries folders definitions in json format.

• ExportedFolders.json contains the subfolder definition, if any.
• ExportedQueries.json is the original verbose export of query details of all queries in all subfolders.
• ExportedQueries_Simplified.json is a shorter version of ExportedQueries.json, with all the necessary information to be able to import queries later. It is in a more readable format and easier to edit than ExportedQueries.json.

Define queries template

Download the json file ExportedQueries_Simplified.json. If desired, edit it to refine queries or define any conventional names or placeholders for parameter replacement.

For example, use consistent naming in titles, subfolders, or query names, consistent list of fields to display in the results, and area path criteria. Changes depend on the query purpose. In general, the more consistently defined the template queries are, the easier it is to create a new instance of the query set for a different project or team or iteration.

Tips and tricks: To be able to import queries, all folders must be defined first. Add the list of folders and subfolders at the beginning of the json file you are preparing as a template.

Store the json template in a Git repository, e.g. if you are using the pipeline from the Opex project https://dev.azure.com/ABB-BCI-PCP/OpEx/_git/ConfigurationManagement?path=/ConfigurationAsCode/Queries.
If you have your own copy of the pipeline, place the json file in the same repository where your pipeline is defined. Define the file name as desired, there is no fixed name.

Import queries

Run pipeline ImportQueries directly from the OpEx project. This pipeline can import queries to any given project, even in a different ADO organization, provided that the selected agent can connect to it.

Tips and tricks: If your project is on premises, create a new branch or a copy of the pipeline to run on one of your agents connected to the ABB network.

When starting the pipeline, specify parameters and variables as follows:

Parameter	Description
Branch	"main" or your own branch
Path of the json file that contains the queries to bulk edit and create	This is the path to your json file within the same repository as the pipeline. E.g. "ConfigurationAsCode/Queries/QueriesSample.json"
Source Query Path	Folder where the queries are defined in the template json file. "Source Query Path" will automatically be replaced with "Target Query Path"
Target Query Path	Folder where the queries will be created. "Source Query Path" will automatically be replaced with "Target Query Path"
Target Organization URL	URL of the organization where queries will be created. This can be different from the organization where the pipeline is running. E.g "https://dev.azure.com/ABB-BCI-PCP"
Name of the ADO project	The name of the ADO project where queries will be created. E.g. "PCP".
Source Iteration Path	"Source Iteration Path" will automatically be replaced with "Target Iteration Path". Important: use "\" as iteration path separator as in the pipeline example.
Target Iteration Path	"Source Iteration Path" will automatically be replaced with "Target Iteration Path". Important: use "\" as iteration path separator as in the pipeline example.
SourceReplace	Text in the source json file that will automatically be replaced with the content of parameter "TargetReplace".
TargetReplace	Text that will replace the content of parameter "SourceReplace" in the source json file.
Variables > importToken	Token with permissions to create queries in the target organization.

This pipeline applies all the replacements of query path, project, iteration path, area path, text as defined by parameters, then it creates folders and queries.

Details

In some cases, more advanced replacements are required, for example:

• Replace more items than the ones available as parameters.
• "Multiply" queries by work item type, team, or other criteria.

A possible approach is to edit the pipeline in a new branch and introduce more parameters or a custom script to achieve the desired result.