Forum Discussion
Get Azure DevOps work item daily historical data using OData query
Hi,
I have a report where I need to get the daily historical data of a work item. At the moment, I use Azure DevOps's analytics views to get this data by setting the "granularity" to "daily". For example,
I am trying to move away from using analytics views, and to use Odata query to get the same data. However, I don't know how to control the "granularity" of the historical data. When running the query:
https://analytics.dev.azure.com/{organisation}/_odata/v4.0-preview/WorkItemRevisions?$filter=WorkItemId eq {workItemId}
the historical data I get is at irregular intervals. I assume that this is because a "revision" is made whenever there is a change to the work item, and this query is simply returning these revisions, instead of returning the state of the work item at a regular interval. (Please correct me if wrong). Is it possible to control the granularity and get the query to return the state of a work item at a regular interval?
Thank you!
3 Replies
Your best bet is to just use the ADO rest APIs, it has additional operations that odata doesn't.
To use the REST api:
- Go here to craft the URL: Get started with the REST APIs for Azure DevOps Services and Team Foundation Server - Azure DevOps Services REST API | Microsoft Learn
- Use the Power Query formula: VSTS.Contents(url) where url is from step 1 (Web.Contents will not work).
Hi artemus , thank you for the suggestion, I will look into it. Another quick question, in my original post, I mentioned: "I assume that this is because a "revision" is made whenever there is a change to the work item, and this query is simply returning these revisions, instead of returning the state of the work item at a regular interval.". Would you be able to confirm this assumption? Thank you!
You might be able to use something similar to this (rolling date is set so it only ever returns 3 months, but can be changed to suit) and expand the column with the fields you want (replace <organisation>, <area path> and <project> to suit):
let
rollingDate =Date.ToText(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-3),"yyyy-MM-ddZ"),
Source = Json.Document( Web.Contents ("https://analytics.dev.azure.com/<organisation>/_odata",[
RelativePath = "v3.0-preview/WorkItemSnapshot?",
Query=[
#"$apply"="filter(Area/AreaPath eq '<area path>' "
&"and Project/ProjectName eq '<project>' and DateValue ge "& rollingDate & ")"
]])),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
