Pipeline task duration sample report
Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020
How long does it take different tasks to complete? This article provides the queries from which you can generate a report for a specific pipeline and its tasks. For example, the following image lists the 50th, 80th, and 95th percentile in seconds for all tasks completed for a specific pipeline from September 1 to December 15, 2022.
Important
Power BI integration and access to the OData feed of the Analytics Service are generally available for Azure DevOps Services and Azure DevOps Server 2020 and later versions. The sample queries provided in this article are valid only against Azure DevOps Server 2020 and later versions, and depend on v3.0-preview or later version. We encourage you to use these queries and provide us feedback.
Prerequisites
- Access: Be a member of a project with 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
This article assumes you read Overview of Sample Reports using OData Queries and have a basic understanding of Power BI.
Sample queries
You query the PipelineRunActivityResults? entity set to return task duration information.
Note
To determine available properties for filter or report purposes, see Metadata reference for Azure Pipelines. You can filter your queries or return properties using any of the Property values under an EntityType or NavigationPropertyBinding Path values available with an EntitySet. Each EntitySet corresponds to an EntityType. For more information about the data type of each value, review the metadata provided for the corresponding EntityType.
Copy and paste the following Power BI query directly into the Get Data > Blank Query window. For more information, see Overview of sample reports using OData queries.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRunActivityResults?"
&"$apply=filter( "
&"Pipeline/PipelineName eq '{pipelinename}' "
&"and PipelineRunCompletedOn/Date ge {startdate} "
&"and (PipelineRunOutcome eq 'Succeed' or PipelineRunOutcome eq 'PartiallySucceeded') "
&"and (CanceledCount ne 1 and SkippedCount ne 1 and AbandonedCount ne 1) "
&") "
&"/compute( "
&"percentile_cont(ActivityDurationSeconds, 0.5, TaskDisplayName) as TaskDuration50thPercentileInSeconds, "
&"percentile_cont(ActivityDurationSeconds, 0.8, TaskDisplayName) as TaskDuration80thPercentileInSeconds, "
&"percentile_cont(ActivityDurationSeconds, 0.95, TaskDisplayName) as TaskDuration95thPercentileInSeconds) "
&"/groupby( "
&"(TaskDuration50thPercentileInSeconds, TaskDuration80thPercentileInSeconds,TaskDuration95thPercentileInSeconds, TaskDisplayName)) "
&"&$orderby=TaskDuration50thPercentileInSeconds desc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Substitution strings and query breakdown
Substitute the following strings with your values. Don't include brackets {} with your substitution. For example if your organization name is "Fabrikam", replace {organization} with Fabrikam, not {Fabrikam}.
{organization}- Your organization name{project}- Your team project name{pipelinename}- Your pipeline name. Example:Fabrikam hourly build pipeline{startdate}- The date to start your report. Format: YYYY-MM-DDZ. Example:2021-09-01Zrepresents September 1, 2021. Don't enclose in quotes or brackets and use two digits for both, month and date.
Query breakdown
The following table describes each part of the query.
Query part
Description
$apply=filter(
Start filter() clause.
Pipeline/PipelineName eq '{pipelinename}'
Return pipeline runs for the specified pipeline.
and PipelineRunCompletedOn/Date ge {startdate}
Return task results for a pipeline run on or after the specified date.
and (PipelineRunOutcome eq 'Succeed' or PipelineRunOutcome eq 'PartiallySucceeded')
Return task results for only successful or partially successful pipeline runs.
and (CanceledCount ne 1 and SkippedCount ne 1 and AbandonedCount ne 1)
Omit pipeline runs that were canceled, skipped, or abandoned.
)
Close the filter() clause.
/compute(
Start compute() clause.
percentile_cont(ActivityDurationSeconds, 0.5, TaskDisplayName) as TaskDuration50thPercentileInSeconds,
For each task, compute the 50th percentile of task durations for all tasks that match the filter criteria.
percentile_cont(ActivityDurationSeconds, 0.8, TaskDisplayName) as TaskDuration80thPercentileInSeconds,
For each task, compute the 80th percentile of task durations for all tasks that match the filter criteria.
percentile_cont(ActivityDurationSeconds, 0.95, TaskDisplayName) as TaskDuration95thPercentileInSeconds)
For each task, compute the 95th percentile of task durations for all tasks that match the filter criteria.
/groupby(
Start the groupby() clause.
(TaskDuration50thPercentileInSeconds, TaskDuration80thPercentileInSeconds,TaskDuration95thPercentileInSeconds, TaskDisplayName))
Group by task of pipeline run and calculated day wise 50th percentile task duration, 80th percentile task duration, and 95th percentile task duration.
&$orderby=TaskDuration50thPercentileInSeconds desc
Order the response by task having highest 50th percentile duration.
(Optional) Rename query
You can rename the default query label, Query1, to something more meaningful. Simply enter a new name from the Query Settings pane.
Change column data type
From the Transform menu change the data type for the following columns to Decimal Number**. To learn how, see Transform a column data type.
- TaskDuration80thPercentileInSeconds
- TaskDuration80thPercentileInSeconds
- TaskDuration95thPercentileInSeconds.
(Optional) Rename column fields
You can rename column fields. For example, you can rename the following columns so that they are more display-friendly. To learn how, see Rename column fields.
| Original field name | Renamed field |
|---|---|
TaskDisplayName |
Task Name |
TaskDuration50thPercentileInSeconds |
50th Percentile |
TaskDuration80thPercentileInSeconds |
80th Percentile |
TaskDuration95thPercentileInSeconds |
95th Percentile |
Close the query and apply your changes
Once you've completed all your data transformations, choose Close & Apply from the Home menu to save the query and return to the Report tab in Power BI.
Create the Table report
In Power BI, under Visualizations, choose the Table report. Fields have been renamed as indicated in Rename column fields section.
Add the following fields to the Columns in the order specified.
- Task Name
- 50th Percentile
- 80th Percentile
- 95th Percentile
To change the report title, select the Format your visual paint-brush icon from the Visualizations pane, select General, expand Title, and replace the existing text.
The following image shows a portion of the resulting report.