If you are using Azure DevOps queries to track work, you might have noticed that there is no direct way to query the historical data from Azure DevOps.
The query results provide the data as of today.
There are cases where we would need to know about the historical data (value of certain fields as on a past date).
Today, we will explore a hidden feature in Azure DevOps that will help us to query historical data.
Create Query:
navigate to Boards → Queries.
Create a simple query as given below
History Tab:
The only way to know about how a work item evolved is to view the history tab. The history tab of the work item provides good traceability of what has happened with the workitem. However, this option requires us to open each work item to examine the history tab, which may not be a practical solution. Now let's explore an option using Work Item Query Language(WIQL)
Install WIQL extension:
navigate to Organization Settings → Extensions.
search for wiql and install the below extension
Edit Query WIQL:
Navigate to the queries screen to see that we have a new option "Edit query wiql"
once you click on that option, a popup of the query will open
In the query notice that the clauses that we created earlier using the GUI are available in query format.
Add ASOF clause:
Here comes the trick of using ASOF clause to query historical data, add an ASOF clause at the end as shown below
click the "save query" button.
The query will display the data of the work item as on the mentioned date, use the "Column options" to introduce the columns you want to view the data for.
Summary:
We learned a simple trick to query historical data from Azure DevOps.
Typical use cases are
- Finding delta updates (work that happened between a given period)
- How are work items moved between iterations ?
- what was the priority of a work item on the said date and how it has changed over time?
Thanks for reading, Goodbye until next week!