Pipeline outcome summary sample report
Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020
A pipeline run represents a single execution of a pipeline. During a run, the pipeline is processed, and agents process one or more jobs. Outcomes include Succeeded, Failed, Canceled, and Partially Succeeded. To create reports that show the outcomes of pipeline runs, you query the ``PipelineRuns entity set.
This article provides several queries and instructions on how to create a report to get the number of runs for different pipeline outcomes.
The following image shows an example of an outcome summary report.
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 outcome summary 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.
Pipeline duration for a named pipeline
The following queries return the pipeline runs for a specific pipeline from a specified start date.
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} "
&") "
&"/aggregate( "
&"$count as TotalCount, "
&"SucceededCount with sum as SucceededCount , "
&"FailedCount with sum as FailedCount, "
&"PartiallySucceededCount with sum as PartiallySucceededCount , "
&"CanceledCount with sum as CanceledCount "
&") "
,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.
)
Close filter() clause
/aggregate(
Start aggregate clause for all the pipeline runs matching the filter criteria.
$count as TotalCount,
Count the total number of runs as TotalCount.
SucceededCount with sum as SucceededCount ,
Count the number of successful runs as SucceededCount.
FailedCount with sum as FailedCount,
Count the number of failed runs as FailedCount.
PartiallySucceededCount with sum as PartiallySucceededCount ,
Count the number of partially successful runs as PartiallySucceededCount.
CanceledCount with sum as CanceledCount
Count the number of canceled runs as CanceledCount.
)
Close aggregate() clause.
Pipeline run outcome summary for a specific pipeline ID
Pipelines can be renamed. To ensure that the Power BI reports don't break when a pipeline name is changed, use the pipeline ID rather than its name. You can obtain the pipeline ID from the URL of the pipeline runs page.
https://dev.azure.com/{organization}/{project}/_build?definitionId={pipelineid}
The following queries return the pipeline runs for a specific pipeline ID and from a specified start date.
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} "
&") "
&"/aggregate( "
&"$count as TotalCount, "
&"SucceededCount with sum as SucceededCount , "
&"FailedCount with sum as FailedCount, "
&"PartiallySucceededCount with sum as PartiallySucceededCount , "
&"CanceledCount with sum as CanceledCount "
&") "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Pipeline run outcome summary filtered by branch
To view the outcome summary of a pipeline for a particular branch, use the following queries. To create the report, do the following steps:
- Expand
BranchintoBranch.BranchName - Change column data type
- Create the Donut chart report
- Select Slicer from the Visualizations pane and add the
Branch.BranchNameto the slicer's Field - Select the branch from the slicer for which you need to see the outcome summary.
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} "
&") "
&"/groupby( "
&"(Branch/BranchName), "
&"aggregate( "
&"$count as TotalCount, "
&"SucceededCount with sum as SucceededCount , "
&"FailedCount with sum as FailedCount, "
&"PartiallySucceededCount with sum as PartiallySucceededCount , "
&"CanceledCount with sum as CanceledCount "
&")) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Pipeline run outcome summary filtered by build reason
You may want to view the outcome summary of a pipeline for only specific Build Reasons (Manual / BatchedCI, Pull Request, and so on). To create the report, do the following steps:
- Change column data type
- Create the Donut chart report
- Select Slicer from the Visualizations pane and add the
Pipeline.PipelineNameto the slicer's Field - Select the pipeline from the slicer for which you need to see the outcome summary.
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} "
&") "
&"/groupby( "
&"(RunReason), "
&"aggregate( "
&"$count as TotalCount, "
&"SucceededCount with sum as SucceededCount , "
&"FailedCount with sum as FailedCount, "
&"PartiallySucceededCount with sum as PartiallySucceededCount , "
&"CanceledCount with sum as CanceledCount "
&")) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
Outcome summary for all project pipelines
You may want to view the pipeline outcome summary for all project pipelines in a single report. To create the report, do the following steps:
- Expand
PipelineintoPipeline.PipelineName - Change column data type
- Create the Donut chart report
- Select Slicer from the Visualizations pane and add the
Pipeline.PipelineNameto the slicer's Field - Select the pipeline from the slicer for which you need to see the outcome summary.
See also Outcome summary for all pipelines sample report for detailed steps.
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} "
&") "
&"/groupby( "
&"(Pipeline/PipelineName), "
&"aggregate( "
&"$count as TotalCount, "
&"SucceededCount with sum as SucceededCount , "
&"FailedCount with sum as FailedCount, "
&"PartiallySucceededCount with sum as PartiallySucceededCount , "
&"CanceledCount with sum as CanceledCount "
&")) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
(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 Power Query Editor, select the TotalCount column, and then select Data Type from the Transform menu, and choose Whole Number. For more information about changing the data type, see Transform Analytics data to generate Power BI reports, Transform a column data type.
(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 Donut chart report
In Power BI, under Visualizations, choose the Donut report.
Add the following fields to Values, in the order indicated. Right-click each field and ensure Sum is selected.
CanceledCountPartiallySucceededCount.SucceededCountFailedCount
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 the resulting report.