Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Azure DevOps Services | Azure DevOps Server | Azure DevOps Server 2022 | Azure DevOps Server 2020
You can use Analytics for Azure DevOps to construct OData queries to return data you're interested in. You can run these queries in your browser or in client software like Excel or Power BI.
This article focuses on queries for retrieving Azure Boards work tracking entity sets, but the principles apply to querying other entity sets. For more information, see Construct OData queries for Analytics and Metadata reference for Azure Boards Analytics.
This tutorial shows you how to:
- Define queries that return item counts, with or without their data.
- Select to return data for specific properties.
- Filter data by specific properties.
- Return data for navigation properties like Identity, Area Path, and Iteration Path.
- Use
expandclauses and nestedexpandstatements. - Query date ranges.
- Use the
orderbyoption to sort results.
Note
The Analytics service is automatically enabled and supported in production for all services within Azure DevOps Services. Power BI integration and access to the OData feed of the Analytics service are generally available. You're encouraged to use the Analytics OData feed and provide feedback.
Available data is version-dependent. The latest supported version of the OData API 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. You're encouraged to use the Analytics OData feed and provide feedback. If you upgrade from Azure DevOps Server 2019, you can install the Analytics service during upgrade.
Available data is version-dependent. The latest supported version of the OData API is v2.0, and the latest preview version is v4.0-preview. For more information, see OData API versioning.
Prerequisites
| Category | Requirements |
|---|---|
| Access levels | - Project member. - At least Basic access. |
| Permissions | By default, project members have permission to query Analytics and create views. For more information about other prerequisites regarding service and feature enablement and general data tracking activities, see Permissions and prerequisites to access Analytics. |
Note
Cross-project queries fail when the user running the query doesn't have access to all projects. For more information about requirements, see Project and organization-scoped queries.
Note
The OData queries in this article use the query URL defined for Azure DevOps Services, https://analytics.dev.azure.com/. Substitute your own organization and project names in the queries to get familiar with querying OData.
For an on-premises server, you can construct similar queries with a URL based on your server and project collection, https://<servername>/<ProjectCollectionName>/. For more information, see Construct OData queries for Analytics.
Get a count of items
To return only a count of items or entities defined in an organization or project without including other information, apply the $apply=aggregate($count as Count) query option. The following queries return the number of projects, work items, area paths, and users in 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)
The preceding queries return results like the following example for projects in the fabrikam organization:
{
"@odata.context": "https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/$metadata#Projects(Count)",
"value": [
{
"@odata.id": null,
"Count": 16
}
]
}
Get a count of items and their data
To return a count of items along with selected data for the items, specify the $count=true query option in a select statement. The following queries return a count of work items, area paths, and users defined for a project along with specified properties. For valid properties, see Metadata reference for Azure Boards Analytics and Calendar date, Project, and User metadata reference for Azure DevOps Analytics.
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,UserEmail
Note
To return all properties defined for a specified entity type, you can use $count=true with no select clause. However, if you don't include a $select or $apply clause, you receive a warning such as VS403507: The specified query does not include a $select or $apply clause which is recommended for all queries. Details on recommended query patterns are available here: https://go.microsoft.com/fwlink/?linkid=861060. To avoid running into usage limits, always include a $select or $apply clause in your queries.
For example, the following query requests the count and User Names of users in the Fabrikam Fiber project:
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/Users?$count=true&$select=UserName
The query returns a count of 5 users with their User Names.
{
"@odata.context": "https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#Users(UserName)",
"@odata.count": 5,
"value": [
{
"UserName": "Microsoft.VisualStudio.Services.TFS"
},
{
"UserName": "[email protected]"
},
{
"UserName": "Jamal Hartnett"
},
{
"UserName": "[email protected]"
},
{
"UserName": "[email protected]"
}
]
}
Select 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 $select=WorkItemId,WorkItemType,Title,State clause to your query.
The $select clause specifies the property names that correspond to the named fields. Property names in OData queries require attention to both spacing and casing. Although property display names like Work Item ID can contain spaces, formal property names can't contain spaces.
For more information about property names and labels, see Metadata reference for Azure Boards. To understand how custom field properties are labeled, see Custom properties.
The following example query requests the work item IDs, titles, and states for the top three work items in the Fabrikam Fiber project.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$top=3
Analytics returns the following data.
{
"@odata.context": "https://analytics.dev.azure.com/fabrikamprime/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State)",
"value": [
{
"WorkItemId": 31,
"Title": "About screen",
"WorkItemType": "Task",
"State": "New"
},
{
"WorkItemId": 30,
"Title": "Change background color",
"WorkItemType": "Task",
"State": "Active"
},
{
"WorkItemId": 32,
"Title": "Standardize on form factors",
"WorkItemType": "Task",
"State": "Active"
}
]
}
Filter data
To filter an entity set to return specific items, add a $filter clause that specifies the criteria the items must meet. The following filter clause returns only Feature work item types that are in the In Progress state.
/WorkItems?$filter=WorkItemType eq 'Feature' and State eq 'In Progress'
The following example query specifies to return Work Item ID, Work Item Type, Title, and State only of Feature work items that are in the In Progress state.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$select=WorkItemId,Title,AssignedTo,State&$filter=WorkItemType eq 'Feature' and State eq 'In Progress'
Specify several filter clauses
You can use and and or to specify several filters in a single $select clause. For example, the following query specifies several fields from work items of types User Story, Bug, or custom type Backlog Work that are in the New, Committed, or Active states. Use parenthesis to group filter clauses as needed.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$select=WorkItemId,Title,AssignedTo,State&$filter=(WorkItemType eq 'User Story' or WorkItemType eq 'Bug' or WorkItemType eq 'Backlog Work') and (State eq 'New' or State eq 'Committed' or State eq 'Active')
The query returns data like the following results:
{
"@odata.context": "https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(WorkItemId,Title,AssignedTo,State)",
"value": [
{
"WorkItemId": 210,
"Title": "Slow response on form",
"State": "Active"
},
...
{
"WorkItemId": 160,
"Title": "Game store testing",
"State": "New"
}
]
}
You can also apply various functions such as contains, startswith, and endswith in $select clauses. See Supported functions.
Query Area Path or Iteration Path properties
To look up the AreaSK, IterationSK, or other properties for a specific area path or iteration path, use the following queries.
Return the AreaSK for a specific area path
The following query requests the AreaSK property defined for the Fabrikam Fiber\Production Planning\Web area path. To see other defined properties for the Areas entity set, see Areas.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/Areas?$filter=AreaPath eq 'Fabrikam Fiber\Production Planning\Web' &$select=AreaSK
The query returns the following data.
{
"@odata.context": "https://analytics.dev.azure.com/fabrikamprime/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#Areas(AreaSK)",
"value": [
{
"AreaSK": "aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb"
}
]
}
Return the IterationSK for a specific iteration path
The following query returns the IterationSK property defined for the Fabrikam Fiber\3Week Sprints\Sprint 3 iteration path. To see other defined properties for the Iterations entity set, see Iterations.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/Iterations?$filter=IterationPath eq 'Fabrikam Fiber\Release 1\Sprint 3' &$select=IterationSK
Filter by navigation properties
Navigation properties represent relationships between entity types. When you specify a navigation property as part of your filter criteria, you must specify the full path for the navigation property. For example, the following clause filters work items based on a specified Iteration Path for the Iteration navigation property.
/WorkItems?$filter=Iteration/IterationPath eq 'Project Name\Iteration 1'
Iteration is the navigation property and IterationPath is the field of interest. Iteration/IterationPath is the full path for the IterationPath property.
The following example query requests data from the top five work items under the Fabrikam Fiber\3Week Sprints\Sprint 3 iteration path by specifying the full path for Iteration/IterationPath.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$top=5&$filter=Iteration/IterationPath eq 'Fabrikam Fiber\3Week Sprints\Sprint 3'&$select=WorkItemId, WorkItemType, Title, State&$orderby=WorkItemId asc
Expand data from related entities
The preceding example query doesn't return Iteration data, because Iteration is a related entity. Properties of navigation properties like Identity, Area, and Iteration aren't directly accessible by using $select statements. You must use $expand statements to return data from related entities.
/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=WorkItemId eq 00000&$expand=Iteration
The following example query requests information associated with work item ID 480, including expanded Iteration data.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$filter=WorkItemId eq 480&$select=WorkItemId,WorkItemType,Title,State&$expand=Iteration
The query returns the following data, which includes all the fields from the expanded Iteration property.
{
"@odata.context": "https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State,Iteration)",
"value": [
{
"WorkItemId": 480,
"Title": "Add animated emoticons",
"WorkItemType": "User Story",
"State": "New",
"Iteration": {
"ProjectSK": "bbbbbbbb-1111-2222-3333-cccccccccccc",
"IterationSK": "cccccccc-2222-3333-4444-dddddddddddd",
"IterationId": "cccccccc-2222-3333-4444-dddddddddddd",
"IterationName": "Sprint 3",
"Number": 276,
"IterationPath": "Fabrikam Fiber\\3Week Sprints\\Sprint 3",
"StartDate": "2025-12-04T00:00:00-12:00",
"EndDate": "2025-12-25T23:59:59.999-12:00",
"IterationLevel1": "Fabrikam Fiber",
"IterationLevel2": "3Week Sprints",
"IterationLevel3": "Sprint 3",
"IterationLevel4": null,
"IterationLevel5": null,
"IterationLevel6": null,
"IterationLevel7": null,
"IterationLevel8": null,
"IterationLevel9": null,
"IterationLevel10": null,
"IterationLevel11": null,
"IterationLevel12": null,
"IterationLevel13": null,
"IterationLevel14": null,
"Depth": 2,
"IsEnded": false,
"AnalyticsUpdatedDate": "2025-10-22T17:28:14.7166667Z"
}
}
]
}
Use select in expand statements
If an expanded property returns more data than you want, add a $select statement against the property.
/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=WorkItemId eq 00000&$expand=Iteration($select=Name,IterationPath)
For example, the following example query selects only the IterationName and IterationPath data from the expanded Iteration property.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$filter=WorkItemId eq 480&$select=WorkItemId,WorkItemType,Title,State&$expand=Iteration($select=IterationName,IterationPath)
The query returns the following data.
{
"@odata.context": "https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State,Iteration(IterationName,IterationPath))",
"value": [
{
"WorkItemId": 480,
"Title": "Add animated emoticons",
"WorkItemType": "User Story",
"State": "New",
"Iteration": {
"IterationName": "Sprint 3",
"IterationPath": "Fabrikam Fiber\\3Week Sprints\\Sprint 3"
}
}
]
}
The following table shows how to use $expand and $select clauses to select several fields in navigation properties. For example, you use $expand=AssignedTo($select=UserName) to return the Assigned to property of the User Name field in the Identity navigation property.
| Type field | Referenced property | Example clauses |
|---|---|---|
| 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) |
You can specify several properties to expand in a single $expand clause by using a comma-delimited list.
$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath)
Use nested expand statements
You can use nested OData $expand statements. For example, the following query uses nested $expand statements to display the project an iteration is in.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$filter=WorkItemId eq 480&$select=WorkItemId,WorkItemType,Title,State&$expand=Iteration($expand=Project)
The query returns the following data:
{
"@odata.context": "https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State,Iteration)",
"value": [
{
"WorkItemId": 480,
"Title": "Add animated emoticons",
"WorkItemType": "User Story",
"State": "New",
"Iteration": {
"ProjectSK": "bbbbbbbb-1111-2222-3333-cccccccccccc",
"IterationSK": "cccccccc-2222-3333-4444-dddddddddddd",
"IterationId": "cccccccc-2222-3333-4444-dddddddddddd",
"IterationName": "Sprint 3",
"Number": 276,
"IterationPath": "Fabrikam Fiber\\3Week Sprints\\Sprint 3",
"StartDate": "2025-12-04T00:00:00-12:00",
"EndDate": "2025-12-25T23:59:59.999-12:00",
"IterationLevel1": "Fabrikam Fiber",
"IterationLevel2": "3Week Sprints",
"IterationLevel3": "Sprint 3",
"IterationLevel4": null,
"IterationLevel5": null,
"IterationLevel6": null,
"IterationLevel7": null,
"IterationLevel8": null,
"IterationLevel9": null,
"IterationLevel10": null,
"IterationLevel11": null,
"IterationLevel12": null,
"IterationLevel13": null,
"IterationLevel14": null,
"Depth": 2,
"IsEnded": false,
"AnalyticsUpdatedDate": "2025-10-22T17:28:14.7166667Z",
"Project": {
"ProjectSK": "bbbbbbbb-1111-2222-3333-cccccccccccc",
"ProjectId": "bbbbbbbb-1111-2222-3333-cccccccccccc",
"ProjectName": "Fabrikam Fiber",
"AnalyticsUpdatedDate": "2025-10-28T20:27:13.5833333Z",
"ProjectVisibility": "Private"
}
}
}
]
}
You can add $select statements, for example to return only the IterationName and IterationPath from Iteration:
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$filter=WorkItemId eq 480&$select=WorkItemId,WorkItemType,Title,State&$expand=Iteration($select=IterationName,IterationPath;$expand=Project)
This query returns the following data:
{
"@odata.context": "https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/$metadata#WorkItems(WorkItemId,WorkItemType,Title,State,Iteration(IterationName,IterationPath,Project))",
"value": [
{
"WorkItemId": 480,
"Title": "Add animated emoticons",
"WorkItemType": "User Story",
"State": "New",
"Iteration": {
"IterationName": "Sprint 3",
"IterationPath": "Fabrikam Fiber\\3Week Sprints\\Sprint 3",
"Project": {
"ProjectId": "bbbbbbbb-1111-2222-3333-cccccccccccc",
"ProjectId": "bbbbbbbb-1111-2222-3333-cccccccccccc",
"ProjectName": "Fabrikam Fiber",
"AnalyticsUpdatedDate": "2025-10-28T20:27:13.5833333Z",
"ProjectVisibility": "Private"
}
}
}
]
}
The results show only the IterationName and IterationPath from Iteration, and Project as a nested object within the Iteration results.
Note
When you nest an $expand clause inside a $select statement, you must use a semi-colon ; before the nested $expand to avoid an error.
Query a date range
The following example query returns work items whose last Changed Date is greater than or equal to January 1, 2025.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=ChangedDate ge 2025-01-01Z
The following example query returns work items whose last Changed Date occurred during the week of October 31 through November 7, 2025.
https://analytics.dev.azure.com/fabrikam/Fabrikam%20Fiber/_odata/v4.0-preview/WorkItems?$select=WorkItemId,WorkItemType,Title,State&$filter=ChangedDate ge 2025-10-31Z&ChangedDate le 2025-11-07Z
Sort results
Specify the $orderby option to sort your results or specify the sequence to return results in. You can sort in ascending or descending order using keywords asc or desc. The following table shows some examples.
| Sort by | Clause |
|---|---|
| Work item ID | /WorkItems?$orderby=WorkItemId |
| Work item ID descending | /WorkItems?$orderby=WorkItemId desc |
| Work item type and State | /WorkItems?$orderby=WorkItemType,State |