Query Details
Azure Runbooks Query With Correlation
Query
let RequestId = toguid("9A07544E-004A-4E14-895D-3348165D7DBD");
let UPN = "[email protected]";
AzureDiagnostics
| join kind= inner (
AzureDiagnostics
| where TimeGenerated > ago(180d)
| where resultDescription_RequestId_g == RequestId or resultDescription_UPN_s == UPN
| distinct CorrelationId
) on CorrelationId
| project TimeGenerated,_TimeReceived,Latency=(_TimeReceived-TimeGenerated),RequestId=resultDescription_RequestId_g, RunbookName=RunbookName_s, Status=resultDescription_Status_s,ResultType,UPN=resultDescription_UPN_s,Attempt=
resultDescription_AttemptCount_d,RequestExpectedSuccessCount=
resultDescription_RequestExpectedSuccessCount_d, ExternalEmailAddress=resultDescription_ExternalEmailAddress_s
| summarize CountSuccess = dcountif(RunbookName, Status == "Success"),
CountFailure = dcountif(RunbookName, Status == "Failure"),
CountDistinct = dcountif(RunbookName, Status != "Success"),
RequestExpectedSuccessCount = max(RequestExpectedSuccessCount) by RunbookName
| summarize requestExpectedSuccessCount = max(RequestExpectedSuccessCount),
totalFailed = sum(CountFailure),
totalSuccess = sum(CountSuccess),
totalStarted = sum(CountDistinct)
| extend packedStatistics = pack_all()
| project packedStatistics
Explanation
The query is retrieving Azure diagnostics data and summarizing it based on certain conditions. It joins the AzureDiagnostics table with itself, filters the data based on a specific time range and specific RequestId or UPN values. It then projects certain columns and calculates the Latency, and performs a summary of counts and maximum values based on the RunbookName. Finally, it calculates the total counts of failures, successes, and distinct runbooks, and packs the statistics into a single column.
Details
Rod Trent
Released: March 26, 2020
Tables
AzureDiagnostics
Keywords
Devices,Intune,User
Operators
joinwheredistinctonprojectsummarizedcountifmaxsumextendpack_allproject