Pipeline stage wise failures sample report
Azure DevOps Services | Azure DevOps Server 2022 | Azure DevOps Server 2020
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 stagewise failures report for a specific pipeline from October 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
- 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
Stage, task, or job failure trend reports can be created by querying the PipelineRunActivityResults entity set.
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.
Stagewise failure trend
To view stagewise failure trend for a specific pipeline from a specified date, use the following queries.
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/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
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 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, Build ID and stage name.
aggregate (FailedCount with sum as FailedCount))
For each day, build ID, and stage; count the total number of failures. It will be the total number of task failures and 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 wise failure trend
To view the task wise failure trend, use the following queries.
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/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 wise failure trend
To view the job wise failure trend, use the following queries.
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/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. Simply enter a new name from the Query Settings pane.
Expand columns in Power Query Editor
Prior to creating the report, you'll need to expand the following two columns. To learn how to expand work items, see Transform Analytics data to generate Power BI reports.
- Expand
PipelineJobtoPipelineJob.StageName - Expand
PipelineRunCompletedOntoPipelineRunCompletedOn.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 ones that are more user friendly. 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 Stacked column chart report
In Power BI, under Visualizations, choose the Stacked column chart report. The following example assumes that no one renamed any columns.
Add
PipelineRunCompletedOn.Dateto X-Axis, right-click it, and select PipelineRunCompletedOn.Date, rather than Date Hierarchy.Add
FailedStagedCountto Y-Axis right-click it, and ensure Sum is selected.Add
PipelineJob.StageNameto Legend.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.
Related articles
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for