Hello Everyone, :)
In my previous article, I talked about the basic operations we can perform using the APIs of Azure DevOps Service.
In this article, I will talk about Azure DevOps modules such as wiql editor and query editor. I will explain through sample scenarios how we can prepare using the Wiql editor the sql equivalents of complex queries that we have prepared with the query editor, and how we can run these queries with APIs.
I had previously created several User Story type work items and several Tasks for my Azure DevOps Board.
Risk information has been entered for each user story here.
Azure DevOps Query Editor
Thanks to the Query editor in the Azure DevOps, it is possible to list all work items in the backlog in any way we want. Here you can easily create queries, save the queries you have created, update and delete the queries you have previously created.
Follow the steps below to create query.
1. You can create a new query through left menu - Board - Queries - New Query.
2. A sample query: A query for work items where the Work Item Type is User Story, and a Risk Value is 1-High.
So what do we do with this query?
When sending requests to APIs, we will send this query as the request body.
How?
We do this by converting the queries we created in the editor to sql query. An extension (wiql editor) in Azure DevOps is already doing this for us. This makes our work much easier.
WIQL (Work item query language) Editor
Since this editor is not included in Azure DevOps by default, you need to install the WIQL Editor extension on the marketplace.
You can integrate the WIQL Editor Extension into your Azure DevOps by following the steps below.The link below directs you to the download screen for the wiql extension on the marketplace. Through the form on the download screen, it asks the organization you want to add the extension to.
https://marketplace.visualstudio.com/acquisition?itemName=ottostreifel.wiql-editor&serverKey=null
You can choose and install your organization.
The “Proceed to organization” it directs you to Azure DevOps. After the installation process, the Wiql Playground module should be on the Board menu.
We perform the process of returning the written queries to wiql as follows.
In the query we have prepared, if you select Edit query wiql from the top menu, it will give you the ready wiql for this query.
If we want to define WIQL, the definition would be a sql language for the queries we have created to list the work items.
Let us run this query with API now. The API we need to use should be as follows.
POST
https://dev.azure.com/{organization}/{project}/{team}/_apis/wit/wiql?api-version=5.1
When you run the following script on PowerShell, the ids and urls of the work items listed above will return.
$apiadress = "https://dev.azure.com/kadriyetaylann/deneme/_apis/wit/wiql?api-version=5.1"$personalAccessToken ="2ojzcqzxxmkn6hxrt7lhog3lgfgiqxjyi5u37tcn4334xi4ofi3a"$token = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($personalAccessToken)"))$headers = @{authorization = "Basic $token" }$body= @"{"query": "SELECT[System.Id],[System.WorkItemType],[System.Title],[System.AssignedTo],[System.State],[System.Tags]FROM workitemsWHERE[System.TeamProject] = @projectAND [System.WorkItemType] = 'User Story'AND [Microsoft.VSTS.Common.Risk] = '1 - High'"}"@$getWits = Invoke-RestMethod -Method Post -Uri $apiadress -Body $body -ContentType "application/json" -Headers $headers$getWits.workItems
When the script is run, its output should be similar to the output below.
Now let us write the information about these work items on the screen.
In order to get the field information of a work item, you should send a request with the id information of that work item as follows.
GET
https://dev.azure.com/{organization}/{project}/_apis/wit/workitems/{id}?$expand=fields
When you run the script above by adding the script below, Title ('System.Title') values will return for each work item.
foreach ($getWit in $getWits.workItems) {$witFieldsUrl = “https://dev.azure.com/kadriyetaylann/deneme/_apis/wit/workitems/" + $getWit.id + ‘?$expand=fields’$witFields = Invoke-RestMethod -Method Get -Uri $witFieldsUrl -Headers $headers$witFields.fields.’System.Title’}
When the script is run, its output should be similar to the output below.
The strings in this output are the title information of the work items returned at the end of the query above.
To display all field information of a work item, it will be sufficient to write the $ witFields.fields command without specifying the field information at the $ witFields.fields. 'System.Title' step in the script.
Now let us make the risk values of the user story work items with a risk value of "1-High" to "2-Medium".
In my previous article, I explained how a work item field information is updated. Since I have already mentioned all the necessary details, I will skip here and share the ready script with you.
$apiadress = "https://dev.azure.com/kadriyetaylann/deneme/_apis/wit/wiql?api-version=5.1"$personalAccessToken ="2ojzcqzxxmkn6hxrt7lhog3lgfgiqxjyi5u37tcn4334xi4ofi3a"$token = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($personalAccessToken)"))$headers = @{authorization = "Basic $token" }$body= @"{"query": "Select [System.Id], [System.Title], [System.State] from WorkItems where [System.TeamProject] = @project and [System.WorkItemType] = 'User Story'"}"@$getWits = Invoke-RestMethod -Method Post -Uri $apiadress -Body $body -ContentType "application/json" -Headers $headersforeach ($getwit in $getWits.workItems) {$witUrl = "https://dev.azure.com/kadriyetaylann/deneme/_apis/wit/workitems/" + $getwit.id + '?$expand=fields'$witFields = Invoke-RestMethod -Method Get -Uri $witUrl -Headers $headersif($witFields.fields.'Microsoft.VSTS.Common.Risk'.Contains('High')){$jsonBody = @(@{op = 'replace'path = '/fields/Microsoft.VSTS.Common.Risk'value = '2 - Medium'})$JSON = ConvertTo-Json $jsonBody$patchUrl = "https://dev.azure.com/kadriyetaylann/deneme/_apis/wit/workitems/" + $getwit.id + '?api-version=5.1'Invoke-RestMethod -Method Patch -Uri $patchUrl -Body $JSON -ContentType "application/json-patch+json; charset=utf-8" -Headers $headers}}
After running the code, we will see the updates when we get the work items again.
Query Editor View:
As seen in the image, our risk fields have been updated.
NOTE: The basic field information of a work item are as follows:
System.Id
System.AreaId
System.AreaPath
System.TeamProject
System.NodeName
System.AreaLevel1
System.Rev
System.AuthorizedDate
System.RevisedDate
System.IterationId
System.IterationPath
System.IterationLevel1
System.WorkItemType
System.State
System.Reason
System.CreatedDate
System.CreatedBy
System.ChangedDate
System.ChangedBy
System.AuthorizedAs
System.PersonId
System.Watermark
System.CommentCount
System.Title
Microsoft.VSTS.Scheduling.RemainingWork
Microsoft.VSTS.Scheduling.OriginalEstimate
Microsoft.VSTS.Scheduling.CompletedWork
Microsoft.VSTS.Common.StateChangeDate
You can also access the Scripts from the GitHub link below.
In this article, using small examples, I tried to answer the questions such as how to create a query in the Query Editor on Azure DevOps, how to install and use the Wiql editor extension, and how to run a query with API. I hope it was a helpful article :)
See you in my next article :)