Skip to content

Latest commit

 

History

History
296 lines (243 loc) · 21.7 KB

TestReportingQueries.md

File metadata and controls

296 lines (243 loc) · 21.7 KB
Raw

Test Reporting Queries

The following is a list of default queries to use to look up information about failed tests. Feel free to change them for your own usages.

Click here to learn more about the data we're collecting.

Caveats (updated July 14, 2022):

  • Because this data is stored in an internal data source, it is unfortunately currently only available to Microsoft employees. (If you are an internal Microsoft employee, you can request access from the .NET Engineering Services team.)

Index

Tests That Have Changed Failure Rate in the Last Week

Expand for query

Variables:

  • targetSignificance: Target statistical likelihood that the failure change is due to a change in the last week. (The closer to 1 this value is, the more likely the test changed.)
  • repo: Repository to filter on. Set to empty string to inclue all repositories. Default is dotnet/runtime.
  • minimumHistoricalData: Minimum number of historical data points (e.g. how many times the test has run) to include to avoid new tests, (0 includes all tests)
let targetSignificance = 0.95;
let repo = "dotnet/runtime";
let minimumHistoricalData = 0;
let dt = toscalar(AzureDevOpsTestAnalysis | summarize max(ReportDate));
AzureDevOpsTestAnalysis
| where ReportDate == dt and Repository == repo
| where Significance >= targetSignificance and CurrentFailCount != 0
| extend HistoricalTotal = HistoricalFailCount + HistoricalPassCount + HistoricalPassOnRerunCount
| where HistoricalTotal >= minimumHistoricalData
| order by Significance desc

〽️ Link to query editor

Tests That Have Failed X% of the Time in the Recent Timespan

This query will return a list of tests that have failed a certain percentage in the recent provided timespan. The default example in this query provides a list of tests in the dotnet/runtime repo that have failed 10% of the time in the last 7 days.

Expand for query

Variables:

  • ts: Kusto timespan format. Default is 7d.
  • repo: Repository to filter on. Set to empty string to inclue all repositories. Default is dotnet/runtime.
  • failureThreshold: Double value denoting failure rate percentage. Default is 0.1 or 10%.
  • excludeAlwaysFailing: Set to true to filter out tests that are always failing to get a list of tests that are "flakey". Default is true.
let ts = 7d;                      // Timespan value
let repo = "dotnet/runtime";      // Optional: set to empty string if you want results from all repositories
let failureThreshold = 0.1;       // Double value denoting the lowest test fail rate to return
let excludeAlwaysFailing = true;  // Boolean. Set to true to exclude test results that are always failing
let subtable = AzureDevOpsTestsSummary
| where ReportDate >= ago(ts) and iff(repo == "", Repository == Repository, Repository == repo)
| summarize numerator=sum(FailCount), denom=sum(PassCount) + sum(FailCount) + sum(PassOnRetryCount), asOfDate=max(ReportDate) by BuildDefinitionName, TestName, ArgumentHash;
let argumentHashMap = AzureDevOpsTestsSummary
| where ReportDate >= ago(ts)
| summarize by ArgumentHash, Arguments;
subtable
| where denom > 0 and (todouble(numerator)/todouble(denom)) >= failureThreshold and iff(excludeAlwaysFailing, (todouble(numerator)/todouble(denom)) < 1, true)
| lookup (argumentHashMap) on ArgumentHash
| project BuildDefinitionName, TestName, Arguments, FailRate=(todouble(numerator) / todouble(denom)), FailCount=numerator, TotalRunCount=denom;

〽️ Link to query editor

Mean Value for the Expected Pass Rate for Tests

This query will return a list of tests and the mean value for the expected pass rate based on historical data. (Comparably, an inverse to Tests That Have Failed X% of the Time in the Recent Timespan)

Calculate the expected value (E[Y]) for how often a test is likely to pass on its initial run (retries not included) based on its historical data (e.g. monthly, weekly, daily aggregates). Ex: A test is known to fail once out of every seven runs. Its expected value (E[Y]) is determined to be 85.7%, meaning, we expect this test to succeed 85.7% of the time.

Expand for query

Variables:

  • ts: Kusto timespan format. Default is 7d.
  • repo: Repository to filter on. Set to empty string to inclue all repositories. Default is dotnet/runtime.
  • excludeAlwaysPassing: Set to true to filter out tests that are always passing. Default is true.
let ts = 7d;                      // Timespan value
let repo = "dotnet/runtime";      // Optional: set to empty string if you want results from all repositories
let excludeAlwaysPassing = true;  // Boolean. Set to true to exclude test results that are always passing
let subtable = AzureDevOpsTestsSummary
| where ReportDate >= ago(ts) and iff(repo == "", Repository == Repository, Repository == repo)
| summarize numerator=sum(PassCount), denom=sum(PassCount+FailCount+PassOnRetryCount), asOfDate=max(ReportDate) by BuildDefinitionName, TestName, ArgumentHash;
let argumentHashMap = AzureDevOpsTestsSummary
| where ReportDate >= ago(ts)
| summarize by ArgumentHash, Arguments;
subtable
| where denom > 0 and iff(excludeAlwaysPassing, (todouble(numerator)/todouble(denom)) < 1, true)
| lookup (argumentHashMap) on ArgumentHash
| project BuildDefinitionName, TestName, Arguments, MeanPassRate=(todouble(numerator) / todouble(denom)), PassCount=numerator, TotalRunCount=denom
| order by MeanPassRate;

〽️ Link to query editor

Mean Value for the Expected Pass on Retry Rate for Tests

Retries are meant to unblock and prevent a build from failing due to failing test, but they are still indicative of unwanted behavior, therefore, we need to track how often a test passes when retries are introduced. Ex: A test has a 100% pass rate, but only when the test re-runs after a failure every six runs, so it’s expected value for re-runs is 83.3%.

Expand for query

Variables:

  • ts: Kusto timespan format. Default is 14d.
  • repo: Repository to filter on. Set to empty string to inclue all repositories. Default is dotnet/arcade.
let ts = 14d;                      // Timespan value
let repo = "dotnet/arcade";        // Optional: set to empty string if you want results from all repositories
let subtable = AzureDevOpsTestsSummary
| where ReportDate >= ago(ts) and iff(repo == "", Repository == Repository, Repository == repo) and PassOnRetryCount > 0
| summarize numerator=sum(PassOnRetryCount), denom=sum(FailCount+PassOnRetryCount), asOfDate=max(ReportDate) by BuildDefinitionName, TestName, ArgumentHash;
let argumentHashMap = AzureDevOpsTestsSummary
| where ReportDate >= ago(ts)
| summarize by ArgumentHash, Arguments;
subtable
| where denom > 0
| lookup (argumentHashMap) on ArgumentHash
| project BuildDefinitionName, TestName, Arguments, MeanPassOnRetryRate=(todouble(numerator) / todouble(denom)), PassOnRetryCount=numerator, TotalRunCount=denom
| order by MeanPassOnRetryRate;

〽️ Link to query editor

Overall Average Pass Rate for a Test (Including Retries)

Query suggested by @dougbu for getting the overall average pass rate for tests, including retries.

Expand for query

Variables:

  • ts: Kusto timespan format. How many days ago to query.
  • definition: Build definition name.
  • repo: Repository to filter on. Set to empty string to inclue all repositories.
  • branch: Name of the target branch.
  • testName: Test name.
  • excludeAlwaysPassing: Set to true to filter out tests that are always passing.
let ts = 30d;                                                  // Timespan value to find test results after. 
let definition = "";                                           // Optional: The name of the build definition, leave as empty string for all build definitions.
let repo = "dotnet/aspnetcore";                                // Optional: set to empty string if you want results from all repositories
let branch = "";                                               // Optional: The name of the target branch the test ran against.
let testName="Templates.Test.GrpcTemplateTest.GrpcTemplate";   // Optional: The name of the test
let excludeAlwaysPassing = "";                                 // Boolean. Set to true to exclude test results that are always passing
let subtable = AzureDevOpsTestsSummary
    | where ReportDate >= ago(ts)
    | where isempty(definition) or BuildDefinitionName has definition
    | where isempty(repo) or Repository has repo
    | where isempty(branch) or Branch has branch
    | where isempty(testName) or TestName has testName
    | summarize
        numerator=sum(PassCount),
        denomerator=sum(PassCount) + sum(FailCount) + sum(PassOnRetryCount),
        passOnRetryCount=sum(PassOnRetryCount)
        by TestName, ArgumentHash;
let argumentHashMap = AzureDevOpsTestsSummary
    | where ReportDate >= ago(ts)
    | summarize by ArgumentHash, Arguments;
subtable
| where denomerator > 0 and (isempty(excludeAlwaysPassing) or (todouble(numerator) / todouble(denomerator)) < 1)
| lookup (argumentHashMap) on ArgumentHash
| project
    TestName,
    Arguments,
    MeanPassRate=(todouble(numerator) / todouble(denomerator)),
    PassCount=numerator,
    PassOnRetryCount=passOnRetryCount,
    TotalRunCount=denomerator;

〽️ Link to query editor

Search Failed Test Results as with Runfo

The AzureDevOpsTests table collects the details of failed test results, making it searchable as one would with searching for tests with Runfo. Here's a query to get you started:

Expand for query

Variables:

  • started: Kusto timespan format. How many days ago to query.
  • defintion: Build definition name
  • reason: The Azure DevOps build reason: Schedule, IndividualCI, PullRequest, Manual, and BatchedCI
  • targetBranch: Name of the target branch
  • name: Test name
  • jobName: Job name
  • message: Error message
  • workItemName: Work item name
let started = 7d;                    // Timespan value to find test results after. 
let definition = "aspnetcore-ci";    // Optional: The name of the build definition, leave as empty string for all build definitions.
let reason = "BatchedCI";            // Optional: The Azure DevOps build reason value (e.g. PullRequest, BatchedCI, et cetera)
let targetBranch = "main";           // Optional: The name of the target branch the test ran against.
let name = "";                       // Optional: The name of the test
let jobName = "";                    // Optional: The name of the job
let message = "AggregateException";  // Optional: Error message to search for
let workItemName = "";               // Optional: Work item name
AzureDevOpsTests
| where RunCompleted >= ago(started)
| where isempty(definition) or BuildDefinitionName has definition
| where isempty(reason) or BuildReason has reason
| where isempty(targetBranch) or Branch has targetBranch
| where isempty(name) or TestName has name
| where isempty(message) or Message has message
| where isempty(workItemName) or WorkItemName has workItemName

〽️ Link to query editor

Search Timeline as with Runfo

The TimelineIssues and TimelineBuilds tables can be used to search timeline issues as you would with Runfo. Here's a query to get you started.

Expand for query

Variables:

  • started: Kusto timespan format. How many days ago to query.
  • definition: Build definition name
  • reason: The Azure DevOps build reason: manual, schedule, individualCI, batchedCI, and pullRequest
  • result: State of the build
  • targetBranch: Name of the target branch
  • message: Error message
  • type: Warning or Error?
  • jobName: Job name
  • taskName: Task name
let started = 7d;                                  // Timespan value to find test results after. 
let definition = "\\dotnet\\roslyn\\roslyn-CI";    // Optional: The name of the build definition, leave as empty string for all build definitions.
let reason = "";                                   // Optional: The Azure DevOps build reason value (e.g. pullRequest, batchedCI, manual, individualCI)
let result = "";                                   // Optional: The state of the build (e.g. succeeded, failed, canceled, and partiallysucceeded)
let targetBranch = "main";                         // Optional: The name of the target branch the test is ran against.
let message = "timeout";                           // Optional: Error message to search for
let type = "";                                     // Optional: warning or error
let jobName = "Build_Windows_Release";             // Optional: Issues associated with jobs with this name
let taskName = "";                                 // Optional: Issues associated with tasks with this task name
TimelineIssues
| where isempty(message) or Message has message
| where isempty(type) or Type has type
| join kind=inner TimelineBuilds on BuildId
| where StartTime >= ago(started)
| where isempty(definition) or Definition has definition
| where isempty(reason) or Reason has reason
| where isempty(result) or Result == result
| where isempty(targetBranch) or TargetBranch has targetBranch
| join kind=inner TimelineRecords on BuildId
| where isempty(taskName) or TaskName has taskName
| where isempty(jobName) or Name has jobName
| order by StartTime

〽️ Link to query editor

Build Analysis Reporting

This Power BI page contains the following reports:

  • Details of PRs outcomes when merged (e.g. when a PR was merged on red)
  • Build outcomes and retry metrics
  • Tests that pass on rerun

〽️ Link to Power BI Report

Sentiment Tracker Feedback

This report tracks the usage and trends of the feedback we receive via the sentiment tracker in the Build Analysis check on the PRs.

〽️ Link to Power BI Report

Was this helpful? Yes No