Define basic queries using OData Analytics
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
- Access level: You must be a member of a project with Basic access or higher.
- Permission: 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
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.id null
Count 28
Work item count
@odata.context "https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/$metadata#WorkItems(Count)"
value
0
@odata.id null
Count 1166
Area count
@odata.context "https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/$metadata#Areas(Count)"
value
0
@odata.id null
Count 70
User count
@odata.context "https://analytics.dev.azure.com/fabrikam/_odata/v4.0-preview/$metadata#Areas(Count)"
value
0
@odata.id null
Count 16
Return 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,UserEmail
Areas 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=AreaSK
The 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=IterationSK
The 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=3
Analytics 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,State
Analytics 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
WorkItemId 380
Title "Refresh web look, feel, performance factors"
State "In Progress"
1
WorkItemId 480
Title "Customer Phone - Phase 1"
State "In Progress"
2
WorkItemId 493
Title "Change initial view"
State "In Progress"
3
WorkItemId 479
Title "Customer Web - Phase 1"
State "In Progress"
4
WorkItemId 551
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
WorkItemId 361
Title "Hello World Web Site"
WorkItemType "Product Backlog Item"
State "Removed"
1
WorkItemId 362
Title "Resume"
WorkItemType "Product Backlog Item"
State "New"
2
WorkItemId 363
Title "Welcome back page"
WorkItemType "Product Backlog Item"
State "Done"
3
WorkItemId 365
Title "Pause"
WorkItemType "Feature"
State "New"
4
WorkItemId 374
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
IterationLevel10 null
IterationLevel11 null
IterationLevel12 null
IterationLevel13 null
IterationLevel14 null
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-01Z
The 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-30Z
Nest 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
משוב
https://aka.ms/ContentUserFeedback.
בקרוב: במהלך 2024, נפתור בעיות GitHub כמנגנון המשוב לתוכן ונחליף אותו במערכת משוב חדשה. לקבלת מידע נוסף, ראה:שלח והצג משוב עבור