Pipeline duration sample report
Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020
This article shows you how to get pipeline duration, or the time taken to run a pipeline. This report is similar to the duration summary metric in the 'Pipeline duration' chart of the Pipeline duration report.
The following image shows an example of a duration report for a specific pipeline for all pipeline runs from September 2022 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 can use the following queries of the PipelineRuns entity set to create different but similar pipeline duration reports.
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.
Return percentile durations for a specified pipeline
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/PipelineRuns?"
&"$apply=filter( "
&"Pipeline/PipelineName eq '{pipelinename}' "
&"and CompletedDate ge {startdate} "
&"and (SucceededCount eq 1 or PartiallySucceededCount eq 1) "
&") "
&"/compute( "
&"percentile_cont(TotalDurationSeconds, 0.5) as Duration50thPercentileInSeconds, "
&"percentile_cont(TotalDurationSeconds, 0.8) as Duration80thPercentileInSeconds, "
&"percentile_cont(TotalDurationSeconds, 0.95) as Duration95thPercentileInSeconds) "
&"/groupby( "
&"(Duration50thPercentileInSeconds, Duration80thPercentileInSeconds,Duration95thPercentileInSeconds)) "
,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 CompletedDate ge {startdate}
Return pipeline runs on or after the specified date.
and (SucceededCount eq 1 or PartiallySucceededCount eq 1)
Return only the successful or partially successful runs.
)
Close filter() clause.
/compute(
Start compute() clause.
percentile_cont(TotalDurationSeconds, 0.5) as Duration50thPercentileInSeconds,
Compute 50th percentile of pipeline durations of all pipeline runs that match the filter criteria.
percentile_cont(TotalDurationSeconds, 0.8) as Duration80thPercentileInSeconds,
Compute 80th percentile of pipeline durations of all pipeline runs that match the filter criteria.
percentile_cont(TotalDurationSeconds, 0.95) as Duration95thPercentileInSeconds)
Compute 95th percentile of pipeline durations of all pipeline runs that match the filter criteria.
/groupby(
Start groupby() clause.
(Duration50thPercentileInSeconds, Duration80thPercentileInSeconds,Duration95thPercentileInSeconds))
Group the response by Duration50thPercentileInSeconds, Duration80thPercentileInSeconds, and Duration95thPercentileInSeconds and end the groupby clause.
Return percentile durations for a specified pipeline ID
Pipelines can be renamed. To ensure that the Power BI reports don't break when the pipeline name is changed, use pipeline ID rather than pipeline name. You can obtain the pipeline ID from the URL of the pipeline runs page.
https://dev.azure.com/{organization}/{project}/_build?definitionId= {pipelineid}
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/PipelineRuns?"
&"$apply=filter( "
&"PipelineId eq {pipelineid} "
&"and CompletedDate ge {startdate} "
&"and (SucceededCount eq 1 or PartiallySucceededCount eq 1) "
&") "
&"/compute( "
&"percentile_cont(TotalDurationSeconds, 0.5) as Duration50thPercentileInSeconds, "
&"percentile_cont(TotalDurationSeconds, 0.8) as Duration80thPercentileInSeconds, "
&"percentile_cont(TotalDurationSeconds, 0.95) as Duration95thPercentileInSeconds) "
&"/groupby( "
&"(Duration50thPercentileInSeconds, Duration80thPercentileInSeconds,Duration95thPercentileInSeconds)) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Return percentile durations for a specified pipeline, filter by branch
To view the duration of a pipeline for a particular branch only, use the following queries. To create the report, do the following extra steps along with what is outlined in the Change column data type and Create the Clustered column chart report sections.
- Expand
BranchintoBranch.BranchName. - Add the field Branch.BranchName to the X-Axis.
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/PipelineRuns?"
&"$apply=filter( "
&"Pipeline/PipelineName eq '{pipelinename}' "
&"and CompletedDate ge {startdate} "
&"and (SucceededCount eq 1 or PartiallySucceededCount eq 1) "
&") "
&"/compute( "
&"percentile_cont(TotalDurationSeconds, 0.5, BranchSK) as Duration50thPercentileInSeconds, "
&"percentile_cont(TotalDurationSeconds, 0.8, BranchSK) as Duration80thPercentileInSeconds, "
&"percentile_cont(TotalDurationSeconds, 0.95, BranchSK) as Duration95thPercentileInSeconds) "
&"/groupby( "
&"(Duration50thPercentileInSeconds, Duration80thPercentileInSeconds,Duration95thPercentileInSeconds, Branch/BranchName)) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Return percentile durations for all project pipelines
To view the duration for all the pipelines of the project in a single report, use the following queries. To create the report, do the following extra steps along with what is outlined in the Change column data type and Create the Clustered column chart report sections.
- Expand
PipelineintoPipeline.PipelineName. - Add the field PIpeline.PipelineName to X-Axis.
See also Outcome summary for all pipelines for a sample report that has detailed similar steps as required here.
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/PipelineRuns?"
&"$apply=filter( "
&"CompletedDate ge {startdate} "
&"and (SucceededCount eq 1 or PartiallySucceededCount eq 1) "
&" ) "
&"/compute( "
&"percentile_cont(TotalDurationSeconds, 0.5, PipelineId) as Duration50thPercentileInSeconds, "
&"percentile_cont(TotalDurationSeconds, 0.8, PipelineId) as Duration80thPercentileInSeconds, "
&"percentile_cont(TotalDurationSeconds, 0.95, PipelineId) as Duration95thPercentileInSeconds) "
&"/groupby( "
&"(Duration50thPercentileInSeconds, Duration80thPercentileInSeconds,Duration95thPercentileInSeconds, Pipeline/PipelineName)) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
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.
Duration50thPercentileInSecondsDuration80thPercentileInSecondsDuration95thPercentileInSeconds
(Optional) Rename column fields
You can rename column fields. For example, you can rename the column Pipeline.PipelineName to Pipeline Name, or TotalCount to Total Count. To learn how, see Rename column fields.
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 Clustered column chart report
In Power BI, under Visualizations, choose the Clustered column chart report. The example assumes that no columns were renamed.
Add the following fields to the Y-Axis, right-click each field and ensure Sum is selected.
Duration50thPercentileInSecondsDuration80thPercentileInSecondsDuration95thPercentileInSeconds
To change the report title, legend, or other report visuals, select the Format your visual paint-brush icon from the Visualizations pane and adjust one or more settings.
Your report should appear similar to the following image.