Edit

Pipeline stage failures sample report

Azure DevOps Services | Azure DevOps Server | Azure DevOps Server 2022

This article shows you how to create a report of a pipeline's daily stage failures. This report is similar to the Failure trend chart of the Pipeline pass rate report.

The following image shows an example of a stage failures report for a specific pipeline.

Screenshot of Power BI Pipelines stagewise failures stacked column report.

Note

The sample queries in this article require v3.0-preview or later of the Analytics OData feed. Share your feedback.

Category Requirements
Access levels - Project member.
- 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 create stage, task, or job failure trend reports by querying the PipelineRunActivityResults entity set.

Note

To find available properties for filtering or reporting, see the Metadata reference for Azure Pipelines. You can filter queries or return properties by using any Property value defined under an EntityType, or any NavigationPropertyBinding Path value listed for an EntitySet. Each EntitySet maps to an EntityType, which documents the data type for each property.

Stage failure trend

To view the stage failure trend for a specific pipeline from a specified date, use the following queries.

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 'Failed' "
        &"and TaskOutcome eq 'Failed' "
        &") "
            &"/groupby( "
                &"(PipelineRunCompletedOn/Date, PipelineRunId, PipelineJob/StageName ), "
                &"aggregate (FailedCount with sum as FailedCount)) "
            &"/groupby( "
                &"(PipelineRunCompletedOn/Date, PipelineJob/StageName ), "
            &"aggregate "
        &"(cast(FailedCount gt 0, Edm.Int32) with sum as FailedStageCount)) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Substitution strings and query breakdown

Replace the following strings with your values. Don't include the braces {} in 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-DD followed by Z (UTC indicator). Example: 2026-09-01Z represents September 1, 2026. Don't enclose in quotes or brackets.

Query breakdown

The following table describes each part of the query.

Query part

Description

$apply=filter(

Start filter() clause.

Pipeline/PipelineName eq '{pipelinename}'

Return task results for a specific pipeline.

and PipelineRunCompletedOn/Date ge {startdate}

Return task results for pipeline runs on or after the specified date.

and PipelineRunOutcome eq 'Failed'

Return task results where build outcome is failed.

and TaskOutcome eq 'Failed'

Return task results where task outcome is failed.

)

Close filter clause.

/groupby(

Start groupby() clause.

(PipelineRunCompletedOn/Date, PipelineRunId, PipelineJob/StageName ),

Group by date of completion of pipeline run, pipeline run ID, and stage name.

aggregate (FailedCount with sum as FailedCount))

For each day, pipeline run ID, and stage, count the total number of failures. This value represents the total number of task failures, not stage failures.

/groupby(

Start groupby() clause.

(PipelineRunCompletedOn/Date, PipelineJob/StageName ),

Group by day and stage name.

aggregate

Start aggregate clause.

(cast(FailedCount gt 0, Edm.Int32) with sum as FailedStageCount))

For each day, sum the number of times a stage failed and then close the aggregate clause.

Task failure trend

To view the task failure trend, use the following queries.

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 'Failed' "
        &"and TaskOutcome eq 'Failed' "
        &") "
            &"/groupby( "
                &"(PipelineRunCompletedOn/Date, TaskDisplayName), "
                &"aggregate "
            &"(FailedCount with sum as FailedCount)) "
    ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
in
    Source

Job failure trend

To view the job failure trend, use the following queries.

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 'Failed' "
        &"and TaskOutcome eq 'Failed' "
        &") "
            &"/groupby( "
                &"(PipelineRunCompletedOn/Date, PipelineRunId, PipelineJob/JobName ), "
                &"aggregate (FailedCount with sum as FailedCount)) "
            &"/groupby( "
                &"(PipelineRunCompletedOn/Date, PipelineJob/JobName ), "
            &"aggregate "
        &"(cast(FailedCount gt 0, Edm.Int32) with sum as FailedJobCount)) "
    ,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. Enter a new name from the Query Settings pane.

Screenshot of Power BI query menu options, rename query.

Expand columns in Power Query Editor

Before you create the report, expand the following two columns. For more information about expanding work items, see Transform Analytics data to generate Power BI reports.

  • Expand PipelineJob to PipelineJob.StageName
  • Expand PipelineRunCompletedOn to PipelineRunCompletedOn.Date

Change column data type

From the Transform menu, change the data type for the FailedStageCount column to Whole Number. To learn how, see Transform a column data type.

(Optional) Rename column fields

You can rename column fields to be more user friendly. To learn how, see Rename column fields.

Close the query and apply your changes

When you finish all your data transformations, select Close & Apply from the Home menu. This action saves the query and returns you to the Report tab in Power BI.

Screenshot of Power Query Editor Close and Apply option.

Create the Stacked column chart report

  1. In Power BI, under Visualizations, choose the Stacked column chart report. The following example assumes that you didn't rename any columns.

    Screenshot of Visualization fields selections for stagewise failures Stacked column chart report.

  2. Add PipelineRunCompletedOn.Date to X-Axis, right-click it, and select PipelineRunCompletedOn.Date, rather than Date Hierarchy.

  3. Add FailedStageCount to Y-Axis, right-click it, and ensure Sum is selected.

  4. Add PipelineJob.StageName to Legend.

  5. 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 look similar to the following image.

Screenshot of Power BI Pipelines sample stagewise failures stacked column report.

Related content

  • Last updated on