Azure DevOps API WIQL get maximum max work item id

Question

I'm using WIQL to query for a list of work items in Azure DevOps. However, Azure DevOps will return a maximum of 20000 work items in a single query. If the query results contain more that 20000 items, an error code is returned instead of the work items. To get a list of all work items matching my query, I modified the query to filter my ID and then loop to build of a list of work items with multiple queries. The issue is that there is apparently no way to know when I have reached the end of my loop because I don't know the maximum work item ID in the system.

idlist = []
max_items_per_query = 19000
counter = 0
while not done:
  wiql = ("SELECT [System.Id] FROM WorkItems WHERE [System.WorkItemType] IN ('User Story','Bug') 
  AND [System.AreaPath] UNDER 'mypath' AND System.ID >= count AND System.ID < counter + max_items".format(counter, counter + max_items_per_query))

  url = base_url+'wiql'
  params = {'api-version':'4.0'}
  body = {'query':wiql}

  request = session.post(url, auth=('',personal_access_token), params=params, json=body)

  response = request.json()
  newItems = [w['id'] for w in response['workItems']]
  idlist.extend(newItems)

  if not newItems:
    done = True

This works in most cases but the loop exits prematurely if it encounters a gap in work item ids under the specified area path. Ideally, I could make this work if there was a way to query to the max work item ID in the system and then use this number to exit when the counter reaches that value. However, I can't find a way to do this. Is there a way to query for this number or possibly another solution that will allow me to get a list of all work items matching a specific criteria?

score 2 · Accepted Answer · answered Feb 23 '21 at 05:42

You can use $top parameter to get the last one.

Something like below ( This is just sample - you can extend it to your query)

SELECT [System.Id] FROM workitems WHERE [System.Id] > 0 ORDER BY [System.Id] DESC with $top = 1

This will return the maximum System id - as it arranging it in the descending order.

Suggestion :

You can also change your logic something like below as well :

SELECT [System.Id] FROM workitems WHERE [System.Id] > 0 ORDER BY [System.Id] ASC with $top = 5000

Get the 5000th item System.Id , let's us assume there it is 5029

The next query would be :

SELECT [System.Id] FROM workitems WHERE [System.Id] > 5029 ORDER BY [System.Id] ASC with $top = 5000

You will get the next 5000 items starting from the system id- 5029.

You can loop the above logic.

For the exit case of the loop, you can check the number of items returned as part of the iteration - if it is less than 5000, then that would be the end of the iteration.

Azure DevOps API WIQL get maximum max work item id

1 Answers1