The good old asof clause in WIQL queries for Azure DevOps

Pieter Gheysens
Jan 10, 2020 · 4 min read

Last week I was at a customer with a colleague to evaluate their recent switch to Azure DevOps (coming from TFS 2017 on-prem) and to take the next steps in the Azure DevOps roadmap. One of the important changes was to get started with a new Azure DevOps Team Project and to use this Team Project as the big container project for all future activities. If you want to know more why this strategy is recommended, you can read more about the One Team Project in the blog post by Colin Dembovsky, a fellow Azure DevOps MVP. I have been using this approach for multiple years now and Colin does a good job in explaining why it’s a good idea to consider this strategy.

Photo by Kaleidico on Unsplash

While being at a customer we want to make it as valuable as possible and while I was doing some demos about the power of Git in Azure DevOps, my colleague was already looking into some options to move work item info from one Team Project to another (new) Team Project for some quick consolidation. There are quite a few different options available to move/copy work item info around but for this particular scenario we have chosen to use the built-in Move a work item to another project feature. This can be easily done from the results in a specific work item query and it has the advantage that it keeps the full history. The drawback of this method is that it’s a real move of the info from the old Team Project and there’s no second chance to do it again from the original state of the work items in the old Team Project. Once the complexity of the move/migration rises and you want to do a mapping between two Team Projects with a different process template, always consider to use the Azure DevOps Migration Tools which offers a lot more options for various full Team Project migrations.

Anyway, we wanted to perform the move as quickly as possible to get things done and we decided to use the above-mentioned move approach. Afterwards we validated the State of the work items but unfortunately we didn’t zoom into the usage of the Iteration Path which resulted in moving all the items to the root Iteration Path of the new Team Project. The move approach also doesn’t give you the option to keep the existing Area Path.

When all info was moved to the new Team Project, it was required that the new Iteration Path was kept in line with the old Iteration Path value, so we also had to find a way to automate the field changes of 1000+ work items.

Making use of Historical Queries in Azure DevOps

The challenge was to find the Iteration Path value of all work items at a point in time before we moved the work items to the new Team Project. One way was to iterate over the different revisions of the involved work items but this didn’t feel right. In the past I already used the asof clause in WIQL queries to find historical data and again this as of functionality was definitely the best fit to get the job done using the Azure DevOps REST API. You can find my full PowerShell script as a GitHub gist to explore the different steps in the solution.

asof clause in a WIQL query

Above you can see that you can inject the asof clause in a WIQL query to fetch historical data. If I would run this query without asof, it would return 0 work items … but with this asof clause it provides me the results as I would have run the query exactly on January 8 at 10AM. The result of this REST API call only returns the list of work item ids that match your query. To fetch the Iteration Path value, another call is required to fetch the full work item. As you see below, this asof parameter is also supported in the REST API call for getting the individual work item.

asof also supported in the REST API call for getting individual work items

Once the old Iteration Path value has been mapped to the correct Iteration Path values in the target Team Project, we can quickly perform an update (PATCH) of the work item in the target Team Project.

Update the current work item with the mapped value of the Iteration Path value

Always nice to rely on some automation to save the day. :-)

Into ALM

Blogging About Application Lifecycle Management with…

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store