Edit

Pipeline outcome summary sample report

  • Article

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.

Screenshot of Power BI Pipelines 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

  • To view Analytics data and query the service, you need to be a member of a project with Basic access or greater. By default, all project members are granted permissions to query Analytics and define Analytics views.
  • To learn 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've 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. To learn more 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.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review 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-01Z represents 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.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review 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 Branch into Branch.BranchName
  • Change column data type
  • Create the Donut chart report
  • Select Slicer from the Visualizations pane and add the Branch.BranchName to the slicer's Field
  • Select the branch from the slicer for which you need to see the outcome summary.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review 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:

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review 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 Pipeline into Pipeline.PipelineName
  • Change column data type
  • Create the Donut chart report
  • Select Slicer from the Visualizations pane and add the Pipeline.PipelineName to 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.

You can paste the Power BI query listed below directly into the Get Data->Blank Query window. For more information, review 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.

Screenshot of Power BI query menu options, rename query.

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. To learn more 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.

Screenshot of Power Query Editor Close and Apply option.

Create the Donut chart report

  1. In Power BI, under Visualizations, choose the Donut report.

    Screenshot of visualization fields selections for all pipeline runs report.

  2. Add the following fields to Values, in the order indicated. Right-click each field and ensure Sum is selected.

    • CanceledCount
    • PartiallySucceededCount.
    • SucceededCount
    • FailedCount

  3. 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.

    Screenshot of Visualizations pane, Report format options, change title.

    The following image shows the resulting report.

    Screenshot of Power BI sample Pipelines Outcome Summary report.