- Article
Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019
Using Analytics for Azure DevOps, you can construct basic and filtered queries to return data of interest. You can run these queries directly in your browser or within Power BI.
This article builds off information provided in Construct OData queries for Analytics and Metadata reference for Azure Boards Analytics. Also, the queries in this article are focused on retrieving work tracking entity sets, however, the principles apply for querying other entity sets.
In this article you'll learn how to define queries that return the following data:
- Count of items (no other data)
- Count of items and data
- Properties defined for Areas or Iteration Paths
- Selected columns or fields
- Filtered data
- Return data for Identity, Area Path, and Iteration Path fields
- Filter by a navigation property
- Query a date range
- Nest expand statements
- Sort results, orderby option
Note
The Analytics service is automatically enabled and supported in production for all Azure DevOps Services. Power BI integration and access to the OData feed of the Analytics Service are generally available. We encourage you to use it and give us feedback.Available data is version-dependent. The latest supported version is v2.0, and the latest preview version is v4.0-preview. For more information, see OData API versioning.
Note
The Analytics service is automatically installed and supported in production for all new project collections for Azure DevOps Server 2020 and later versions. Power BI integration and access to the OData feed of the Analytics Service are generally available. We encourage you to use it and give us feedback. If you upgraded from Azure DevOps Server 2019, then you can install the Analytics service during upgrade.
Available data is version-dependent. The latest supported version is v2.0, and the latest preview version is v4.0-preview. For more information, see OData API versioning.
Note
The Analytics service is in preview for Azure DevOps Server 2019. You can enable or install it for a project collection. Power BI integration and access to the OData feed of the Analytics Service are in Preview. We encourage you to use it and give us feedback.
Available data is version-dependent. The latest supported version is v2.0, and the latest preview version is v4.0-preview. For more information, see OData API versioning.
Prerequisites
- To view Analytics data and query the service, you need to be a member of a project with Basic access or greater. By default, all project members are granted permissions to query Analytics and define Analytics views.
- To learn about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics.
Note
In this article, the OData query URL is defined for Azure DevOps Services. To construct a similar query for an on-premises server, see the guidance provided in Construct OData queries for Analytics. We encourage you to adjust the queries provided for your organization and project to get familiar with querying OData using your browser.
Return a count of items (no other data)
To learn about the number of items or entities defined in an organization or project, specify $apply=aggregate($count as Count) query option. For example, the following queries return the number of projects, work items, Area Paths, and users defined for an organization.
https://analytics.dev.azure.com/{OrganizationName}/_odata/v4.0-preview/Projects?$apply=aggregate($count as Count)https://analytics.dev.azure.com/{OrganizationName}/_odata/v4.0-preview/WorkItems?$apply=aggregate($count as Count)https://analytics.dev.azure.com/{OrganizationName}/_odata/v4.0-preview/Areas?$apply=aggregate($count as Count)https://analytics.dev.azure.com/{OrganizationName}/_odata/v4.0-preview/Users?$apply=aggregate($count as Count)Tip
Cross-project queries fail when the user running the query doesn't have access to all the projects. Read more about requirements in Project and organization-scoped queries.
Project count
@odata.context"https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/$metadata#Projects(Count)"value 0 @odata.idnull Count 28Work item count
@odata.context"https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/$metadata#WorkItems(Count)"value 0 @odata.idnull Count 1166Area count
@odata.context"https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/$metadata#Areas(Count)"value 0 @odata.idnull Count 70User count
@odata.context"https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/$metadata#Areas(Count)"value 0 @odata.idnull Count 16Return a count of items and data
To return a count of items along with select data for the items, specify the $count=true query option. For example, the following queries return a count of work items, Area Paths, and users defined for a project along with the specified properties. For valid properties, see Metadata reference for Azure Boards Analytics and Calendar date, Project, and User metadata reference for Azure DevOps Analytics.
Tip
If you don't specify the properties to return, Analytics will return all properties defined for the specified entity type.
https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/v4.0-preview/WorkItems?$count=true&$select=WorkItemId,Title,WorkItemType https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/v4.0-preview/Areas?$count=true&$select=AreaName,AreaPath https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/v4.0-preview/Users?$count=true&$select=UserName,UserEmailAreas or Iterations properties
To look up the AreaSK or IterationSK, or other property of an Area Path or Iteration Path, use the following queries.
Return the AreaSK for a specific Area Path
The following query specifies to return the AreaSK property defined for the Fabrikam Fiber\Service Delivery\Internet Area Path. To specify other properties defined for the Areas entity set, see Metadata reference for Azure Boards Analytics, Areas.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/Areas?$filter=AreaPath eq 'Fabrikam Fiber\Service Delivery\Internet' &$select=AreaSKThe query returns the following data.
{ "@odata.context": "https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#Areas(AreaSK)", "value": [ { "AreaSK": "637dc203-0549-4415-9663-b6358ac27d21" } ]}Return the IterationSK for a specific Iteration Path
The following query specifies to return the IterationSK property defined for the Fabrikam Fiber\Release 1\Sprint 3 Iteration Path. To specify other properties defined for the Iterations entity set, see Metadata reference for Azure Boards Analytics, Iterations.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/Iterations?$filter=IterationPath eq 'Fabrikam Fiber\Release 1\Sprint 3' &$select=IterationSKThe query returns the following data.
{ "@odata.context": "https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#Iterations(IterationSK)", "value": [ { "IterationSK": "862e961a-ac7a-4fcc-9ebc-8afd0c12fed5" } ]}Return specific properties or fields
To return specific properties or work item fields, add a $select clause that specifies the property names.
For example, to return the Work Item ID, Work Item Type, Title, and State of work items, add the following clause to your query. This clause specifies the properties that correspond to the named fields.
Note
Property names don't contain any spaces. Your query will fail if you add spaces. OData queries require attention is paid to both spacing and casing. To understand how custom field properties are labeled, see Metadata reference for Azure Boards, Custom properties.
Here we specify to return the top three work items.
https://analytics.dev.azure.com/fabrikam/Fabrikam Fiber/_odata/v4.0-preview/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$top=3Analytics returns the following data.
@odata.context"https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State)"value 0 WorkItemId 462 Title "Test case" WorkItemType "Test Case" State "Design" 1 WorkItemId 491 Title "Change color settings" WorkItemType "Shared Steps" State "Active" 2 WorkItemId 461 Title "Test impediment" WorkItemType "Impediment" State "Open".
Filter your data
To filter an entity set to return select items, specify a $filter clause that specifies the criteria the items must meet. Building on the last query, here we add a filter clause to only return Feature work item types that are in the In Progress state.
/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=State eq 'In Progress'
https://analytics.dev.azure.com/fabrikam/Fabrikam Fiber/_odata/v4.0-preview/WorkItems?$filter=WorkItemType eq 'Feature' and State eq 'In Progress'&$select=WorkItemId,Title,AssignedTo,StateAnalytics returns the following data.
@odata.context"https://analytics.dev.azure.com/kelliott/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(WorkItemId,Title,State)"value 0 WorkItemId380 Title"Refresh web look, feel, performance factors" State"In Progress" 1 WorkItemId480 Title"Customer Phone - Phase 1" State"In Progress" 2 WorkItemId493 Title"Change initial view" State"In Progress" 3 WorkItemId479 Title"Customer Web - Phase 1" State"In Progress" 4 WorkItemId551 Title"Mobile feedback" State"In Progress"Specify several filter clauses
You can use AND and OR to specify several filter clauses.
For example, the following query specifies to return work items of type User Story, Bug, and Backlog Work (a custom work item type) that are in the New, Committed, or Active states. Use parenthesis to group filter clauses as needed.
Additionally, you can apply various functions such as contains, startswith, endswith and more. See the Supported OData features and clauses, Supported functions.
Return data for Identity, Area Path, and Iteration Path fields
Select properties are associated with navigational properties and aren't directly accessible using the $select statement. You must use an $expand statement to return the data of interest. These properties are often associated with several properties of their own. For example, with Identity fields, you can specify to return the user name or the user email.
The following table provides examples of how to expand several of these properties.
| Type fields | Referenced property | Example clauses to include |
|---|---|---|
| DateTime | DateSK | $expand=CreatedDate($select=Date) or$expand=CreatedDate($select=WeekStartingDate) |
| Identity | UserSK | $expand=AssignedTo($select=UserName) or$expand=AssignedTo($select=UserEmail) |
| Area | AreaSK | $expand=Area($select=AreaName) or$expand=Area($select=AreaPath) |
| Iteration | IterationSK | $expand=Iteration($select=IterationName) or$expand=Iteration($select=IterationPath) or$expand=Iteration($select=StartDate) |
| Project | ProjectSK | $expand=Project($select=ProjectName) |
| Team | TeamSK | $expand=Teams($select=TeamName) |
To specify several properties that need to be expanded, you specify them in a single expand clause within a comman-delimited list.
$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath)
Filter by a navigation property
When you specify a navigation property as part of your filter criteria, you must specify it in the required format.
For example, the following clause specifies to filter work items based on Iteration 1 defined for the project.
/WorkItems?$filter=Iteration/IterationPath eq 'Project Name\Iteration 1'
In this example, Iteration is the navigation property name and IterationPath corresponds to the full path for the iteration. To use another entity as a filter, put the navigation property followed by a slash followed by the name of the field to filter on.
And, here's the full OData query:
https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$filter=Iteration/IterationPath eq 'Project Name\Iteration 1'Here's another example that requests the top five work items under the Fabrikam Fiber\Service Delivery\Voice Area Path are returned.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$top=5&$filter=Area/AreaPath eq 'Fabrikam Fiber\Service Delivery\Voice'&$select=WorkItemId, WorkItemType, Title, State&$orderby=WorkItemId asc@odata.context"https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State)"value 0 WorkItemId361 Title "Hello World Web Site" WorkItemType"Product Backlog Item" State "Removed" 1 WorkItemId362 Title "Resume" WorkItemType"Product Backlog Item" State "New" 2 WorkItemId363 Title "Welcome back page" WorkItemType"Product Backlog Item" State "Done" 3 WorkItemId365 Title "Pause" WorkItemType"Feature" State "New" 4 WorkItemId374 Title "Fix performance issues" WorkItemType"Task" State "To Do"Tip
You can't use the navigation property directly in a $select statement. Instead, you need to use $expand.
The previous filtering example for the Iteration Path doesn't return the iteration path because it's contained in a related entity. To return data in a related entity, add an $expand statement:
/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=WorkItemId eq 10000&$expand=Iteration
And here's an example that returns information assigned to work item ID 480.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$filter=WorkItemId eq 480&$select=WorkItemId,WorkItemType,Title,State&&$expand=Iteration @odata.context"https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State,Iteration)"value 0 WorkItemId 480 Title "Customer Phone - Phase 1" WorkItemType "Feature" State "In Progress" Iteration ProjectSK "56af920d-393b-4236-9a07-24439ccaa85c" IterationSK "c7063041-ff3a-4d7f-bb46-c433c7030d59" IterationId "c7063041-ff3a-4d7f-bb46-c433c7030d59" IterationName "Sprint 1" Number 55297 IterationPath "Fabrikam Fiber\\Release 1\\Sprint 1" StartDate "2022-01-17T00:00:00-08:00" EndDate "2022-02-04T23:59:59.999-08:00" IterationLevel1 "Fabrikam Fiber" IterationLevel2 "Release 1" IterationLevel3 "Sprint 1" IterationLevel4 null IterationLevel5 null IterationLevel6 null IterationLevel7 null IterationLevel8 null IterationLevel9 null IterationLevel10null IterationLevel11null IterationLevel12null IterationLevel13null IterationLevel14null Depth 2 IsEnded true AnalyticsUpdatedDate"2022-01-18T22:18:58.17Z"As you can see, the Iteration Path is expanded in the result and all of the iteration data is returned. It's probably more data than you want.
To return less data, add a $select statement against the iteration as well:
/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=WorkItemId eq 10000&$expand=Iteration($select=Name,IterationPath)
It then returns the following data.
{ "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State,Iteration,Iteration(Name,IterationPath))", "value":[ { "WorkItemId":10000, "WorkItemType":"Task", "Title":"Some title", "State":"Completed", "Iteration":{ "Name":"Sprint 55", "IterationPath":"Fabrikam\\Sprints\\Sprint 55" } } ]}Query a date range
The following example returns work items whose Changed Date is greater than equal to January 1, 2021.
https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=ChangedDate ge 2021-01-01ZThe following example returns work items whose Changed Date occurs during the week of April 26 through April 30, 2021.
https://analytics.dev.azure.com{OrganizationName}/{ProjectName}/_odata/{version}/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=ChangedDate ge 2021-04-26Z&ChangedDate le 2021-04-30ZNest expand statements
In OData, you can nest $expand statements. For example, you can write the previous query statement to display the project the iteration is part of:
/WorkItems?$filter=WorkItemId eq 10000&$expand=Iteration($expand=Project)
It returns the following JSON:
{ "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/$metadata#WorkItems", "value":[ { "WorkItemId":10000, "Revision":3, "Watermark":283397, "Title":"Production deployment and testing for Entitlement API v2 and Subscriber database", "WorkItemType":"Task", "ChangedDate":"2014-07-10T19:29:58.41Z", "CreatedDate":"2014-04-19T22:44:58.31Z", "State":"Completed", "Reason":"Completed", "Priority":2, "CompletedWork":10.0, "OriginalEstimate":20.0, "Count":1, "Iteration":{ "IterationId":"7a2c246e-fc62-41af-ad18-62332017bc46", "Name":"Sprint 55", "Number":13021, "IterationPath":"Fabrikam\\Sprints\\Sprint 55", "StartDate":"2013-09-23T00:00:00Z", "EndDate":"2013-10-11T00:00:00Z", "IterationLevel1":"Fabrikam", "IterationLevel2":" Sprints", "IterationLevel3":"Sprint 55", "Level":2, "IsDeleted":false, "Project":{ "ProjectId":"b924d696-3eae-4116-8443-9a18392d8544", "ProjectName":"Fabrikam", "IsDeleted":false } } } ]}You can also combine $expand and $select statements. For example, you can change the previous query to only return the Iteration Name and Iteration Path:
/WorkItems?$filter=WorkItemId eq 10000&$expand=Iteration($select=IterationId,IterationPath;$expand=Project)
It returns the following JSON:
{ "@odata.context":"https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/$metadata#WorkItems(Iteration(IterationId,IterationPath,Project))", "value":[ { "WorkItemId":10000, "Revision":3, "Watermark":283397, "Title":"Production deployment and testing for Entitlement API v2 and Subscriber database","WorkItemType":"Task", "ChangedDate":"2014-07-10T19:29:58.41Z", "CreatedDate":"2014-04-19T22:44:58.31Z", "State":"Completed", "Reason":"Completed", "Priority":2, "CompletedWork":10.0, "OriginalEstimate":20.0, "Count":1, "Iteration":{ "IterationId":"7a2c246e-fc62-41af-ad18-62332017bc46","IterationPath":"Fabrikam\\Sprints\\Sprint 55", "Project":{ "ProjectId":"b924d696-3eae-4116-8443-9a18392d8544", "ProjectName":"Fabrikam", "IsDeleted":false } } } ]}Notice that the result here shows only the IterationId and IterationPath and that the Project is a nested object within the JSON result. Another key item to note is the URL itself. When using a $select statement and an $expand clause, you must use a semi-colon (;) before the $expand. Anything else will result in an error.
Sort results, orderby option
Specify the $orderby option to sort your results or specify the sequence in which results are returned. You can sort in ascending or descending order using keywords asc or desc, respectively. Some examples are shown
| Sort by | Clause to include |
|---|---|
| Work item ID | /WorkItems?$orderby=WorkItemId |
| Work item ID descending | /WorkItems?$orderby=WorkItemId desc |
| Work item type and State | /WorkItems?$orderby=WorkItemType,State |
Next steps
Related articles
- Construct OData queries for Analytics
- Metadata reference for Azure Boards Analytics
- Best practices to use when querying Analytics
- Supported OData features
- OData v4.0 specification
- OData v4.0 Part 2: URL Conventions Plus Errata 02
