Work Item Query Language

WIQL – The Wonder Query Language

As previously discussed, one of the ways to access Work Items is via WIQL query. Despite the fact that it is somewhat awkward (for example, you always have to specify a from clause, but you can only select from one table – WorkItems), It is actually rather powerfull, allowing a user many more options than the GUI query builder. It has all sorts of cool options such as:

  • In Group / Not In Group – for filtering according to predefined categories.
  • Was Ever / Not Ever – this is probably one of the coolest options it allows you to ask for a WI that once had a field set to a particular value. For example, you can have a condition like WHERE [System.State] = ‘Active’ and [System.State] ever ‘Closed’ which is effectively a way of asking for re-opened items.
  • Under / Not Under – for filtering on area paths and iterations.
  • AsOf – TFS time machine allows you to request the state of an item as of a particular date. You can’t do this in Visual Studio.

Language itself is fairly primitive. It only has five parts:

  • select clause – here you can specify fields that interets you. Notice no top operator. More on that in a bit.
  • from clause – a dummy clause, really, as I mentioned only one table is available for selecting – WorkItems
  • where clause – here is where all the searching magic happens.
  • order by clause – you can also sort results
  • asof clause – don’t know why this is considered as a separate clause. IMHO it should have been part of the where clause. But MS didn’t ask me, so here it is.

As far as I’m concerned the trickiest part is knowing what fields to select, however MS seems to think that this is pretty self explanatory. In fact they didn’t update the documentation for VS versions above 2008. MS distinguishes between the friendly name and the reference names. I’ve had trouble using friendly names and have given up on them, and in any case, recommended best practice is to use the reference name. Since it’s a property of the field class, it is also easy to get to. Note that “select * from WorkItems” is a supported syntax. Also, don’t forget to load the reference to the WIT assembly:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.WorkItemTracking.Client")

If you want to know what fields a particular WI has you can run:

$tfsServer="http://DefaultServer:8080/tfs"
$tfs=get-tfsserver $tfsServer
$WIT = $tfs.GetService([Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore])
$WIT.GetWorkItem(123).Fields | %{write-host $_.Name"->"$_.ReferenceName}

or to see all possibilities:

 $WIT.FieldDefinitions | %{write-host $_.Name"->"$_.ReferenceName}

this can be a long list, so you may want to filter it.

$WIT.FieldDefinitions | ?{$_.ReferenceName -match "assign"} |%{write-host $_.Name"->"$_.ReferenceName }

don’t feel like writing out the whole query from scratch? You can get a sample to get you started from queries already saved on the system:

$WIT.projects["myproj"].StoredQueries | %{write-host $_.Name"--->" $_.QueryText"`r`n"}

That’s about all there is to it. In the next post we will come back to the Work Item model and will find out how to get history and changes associated with a WI.

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you #CuriousAboutData? This is a members’ only discussion forum for any CTO, Developer, DBA, Data Engineer/Scientist — or anyone who just wants to know more about using and managing data. Members enjoy open debate, ask/ answer questions, offer opinions, expertise, and support. If your curiosity has no limits, this group is for you!