CODEWITHSUNDEEP
azure-application-insightsazure-data-explorerazure-log-analyticskql
Andrew Tsay
Andrew Tsay

Reputation: 1953

Kusto query: how to perform a nested for loop

I'm building a reliability query for my dashboard. We have multiple pipelines, and each pipeline should have at least one success per day. We don't want to get dinged for failures, because our SLA requires us to only have 1 success per day.

So if I'm looking at an entire week's worth of data, I need to check if EACH day of EACH pipeline over the past week has at least 1 successful run. Been struggling for 2 days, and this representation of a for-loop is all I could conjure up.

let startDate = todatetime("2021-06-30");
let endDate = todatetime("2021-07-07");
let days = range(startDate, endDate, 1d);
let pipelineNames = dynamic(["pipeline1", "pipeline2"]);
ADFPipeline
| where PipelineName == pipelineNames[0]
| where TimeGenerated between (todatetime(days[0]) .. 1d)
| where Status == "Succeeded"
| summarize AtLeastOneSuccess = countif(Status == 'Succeeded') > 0

then

| where PipelineName == pipelineNames[1]
| where TimeGenerated between (todatetime(days[0]) .. 1d)

| where PipelineName == pipelineNames[0]
| where TimeGenerated between (todatetime(days[1]) .. 1d)

| where PipelineName == pipelineNames[1]
| where TimeGenerated between (todatetime(days[1]) .. 1d)

| where PipelineName == pipelineNames[0]
| where TimeGenerated between (todatetime(days[2]) .. 1d)

| where PipelineName == pipelineNames[1]
| where TimeGenerated between (todatetime(days[2]) .. 1d)

and at the end, somehow aggregate it all so I get, "Hey, in the past 7 days, you have 3 days where pipeline1 didn't run, and 1 day where pipeline2 didn't run. So 4/14 = 71% reliability.

Upvotes: 0

Views: 965

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

have you considered running the aggregation by pipeline and day, and then processing the result based on your requirements?

let startDate = todatetime("2021-06-30");
let endDate = todatetime("2021-07-07");
let pipelineNames = dynamic(["pipeline1", "pipeline2"]);
ADFPipeline
| where TimeGenerated between (startDate .. endDate)
| where PipelineName in (pipelineNames)
| summarize AtLeastOneSuccess = countif(Status == 'Succeeded') > 0
         by PipelineName, startofday(TimeGenerated)

Upvotes: 1

Related Questions