Azure DevOps 101: Queries

I posted a couple of weeks ago about the dashboards functionality within Azure DevOps. As I mentioned, it's important to understand how to write queries in order to get the most from your dashboards. In this post we look at how queries work.

What are queries?

Queries work in the same way they do in databases. Just like tables in a database, Azure DevOps also has tables behind the scene and just like in a database where you can query those tables, you can do the same in Azure DevOps.

All queries are based on work items, which is actually a huge part of the product anyway, so the amount of data you have access to and have the ability to query is pretty large.

As you can see from the screenshot above, out of the box, there isn't much to see, you get two main queries; Assigned to me and Followed work items. You should also be able to see that queries come in two folders. My Queries and Shared Queries. Queries which are saved in the My Queries folder are only visible to you, so in here you can save queries which only you need to use.

Any queries which you save in Shared Queries will be visible to anyone with permission in the project you are currently working in. Importantly, if you want to add a query to a dashboard, then the query must be saved in Shared Queries.

Creating queries

Creating a new query is very simple. From the Queries window, which you can find under Boards, simply click the New query button on the ribbon.

The query editor has a number of different options, let's look at the key ones:

  1. Save query - This provides you with the option to save the current query you are working on, this is where you have the option to save it to either My Queries or Shared Queries.
  2. Column options - This button lets you select which columns are present in the output of the query, you can add columns as well as customise the order in which they are displayed.
  3. Copy query URL - Rather than talk people through finding your query, you can use this button to copy a URL to the query on your clipboard, you can then send this directly to anyone with access to your project.
  4. Filters - Filters are where you define your search criteria, think of it like your "where" clause in a SQL query. You can group results, use and/or operators and search for data using a number of different data operators.

On the far right side of the query editor, just under the ribbon you will notice a check box which says Query across projects. This option gives you the ability to execute your query across all projects which you have permission to look inside.

Let's now create a simple query. This query will look for user stories which are in the next sprint and do not currently have any story points assigned. This query would be useful to help Scrum Masters ensure all stories have an appropriate value for the number of story points they take in the sprint.

This is actually quite an easy query to write, you only need three field criteria set, let's look at the query in more detail.

Once you are done, click Run query and your results will be displayed in the results pane at the bottom of the window. You can now save your query using the Save query button at the top ribbon. Remember to save in Shared Queries if you want to use it on a dashboard.

Adding to a dashboard

Now we have our query, let's quickly add this to a dashboard, for the full steps, check out my post on Dashboards. Add a Query Tile widget to your dashboard and then configure the widget.

Select your query from the Query drop down, in my example I have changed the default background colour to white as well and added some conditional formatting to show the tile as amber when more than zero results are returned and then red when more than five results are returned.

On your dashboard, you can now see the tile you have configured with the results of your query.