The Use Case for Finding Users Assigned to Work Items
A customer faced an issue where an employee who had left the company was deactivated in Active Directory (AD). They needed to identify all the work items assigned to this user, but the user’s name wouldn’t appear in Azure DevOps (ADO) when using the work item query editor. To resolve this, I ran a set of ODATA queries to help them identify the work items in which the user was assigned. Review this blog if you are new to ODATA and need to understand how to run the queries.
The customer wanted to create a work item query and export to Excel to use as a reference for identifying the work items assigned to the user. However, the user’s name was not available in the drop for the work item query editor.
ODATA Queries
The easiest option is to identify the user’s SK value from the Users table. Change the ADO org and Team project and use either the user’s email or name in the filter clause.
https://analytics.dev.azure.com/ <Your ADO Org>/<Your Team Project>/_odata/v4.0-preview/Users?
$select=UserID,UserSK,UserName,UserEmail,AnalyticsUpdatedDate
&$filter=UserName eq 'Joe Person'
OR
&$filter=UserEmail eq [email protected]Add the user’s SK value and find all the work items assigned to the user.
https://analytics.dev.azure.com/ <Your ADO Org>/<Your Team Project>/_odata/v4.0-preview/workitems?
$select=WorkItemId,Title,WorkItemType,State
&$expand=AssignedTo($select=UserName)
&$filter=AssignedToUserSK eq 26c5c699-xxxxAdd other filters as needed, such as State or Work Item Type.
You use this for the other User Assigned fields in ADO.
Once the ODATA query is perfected, move it to Power BI to generate the report. Or run from Excel to create a list of users.
Hope this helps with your reporting efforts!