Kusto Query : Retrieve latest 2 runs based on the time and summarize

user_4119825 0
Viewed 897

I am new to kusto, and i am trying to retrieve last 2 run data and summarize the error count.

Please refer the below snippet,

enter image description here

For reference, below is the table query,

let Temptable=datatable(RunId:string,Message:string,AppName:string,timestamp:datetime) [  "1", "start",   "App1", '2020-02-27T04:30:01.6062658Z',  "1", "end",   "App1", '2020-02-27T04:31:01.6062658Z',  "2", "start",   "App1", '2020-02-27T04:00:01.6062658Z',  "2", "end",   "App1", '2020-02-27T04:01:01.6062658Z',  "3", "start",   "App1", '2020-02-27T03:30:01.6062658Z',  "3", "end",   "App1", '2020-02-27T03:31:01.6062658Z',  "4", "start",   "App1", '2020-02-27T03:00:01.6062658Z',  "4", "end",   "App1", '2020-02-27T03:01:01.6062658Z',  "5", "start",   "App1", '2020-02-27T02:30:01.6062658Z',  "5", "end",   "App1", '2020-02-27T02:31:01.6062658Z',  "6", "start",   "App2", '2020-02-27T04:00:01.6062658Z',  "6", "end",   "App2", '2020-02-27T04:01:01.6062658Z',  "7", "start",   "App2", '2020-02-27T03:00:01.6062658Z',  "7", "end",   "App2", '2020-02-27T03:01:01.6062658Z',  "8", "start",   "App2", '2020-02-27T02:00:01.6062658Z',  "8", "end",   "App2", '2020-02-27T02:01:01.6062658Z',  "9", "start",   "App3", '2020-02-27T01:00:01.6062658Z',  "9", "end",   "App3", '2020-02-27T01:01:01.6062658Z',  "10", "start",   "App4", '2020-02-27T00:30:01.6062658Z',  "10", "end",   "App4", '2020-02-27T00:32:01.6062658Z',  "11", "start",   "App4", '2020-02-27T00:15:01.6062658Z',  "11", "end",   "App4", '2020-02-27T00:16:01.6062658Z'  ];
let Errortable=datatable(RunId:string,Error:string,AppName:string) [    "1", "Error1",   "App1",  "1", "Error2",   "App1",  "1", "Error3",   "App1",  "2", "Error1",   "App1",  "2", "Error4",   "App1",  "3", "Error1",   "App1",  "3", "Error2",   "App1",  "3", "Error3",   "App1",  "3", "Error4",   "App1",  "4", "Error1",   "App1",  "5", "Error1",   "App2",  "5", "Error2",   "App2",  "6", "Error1",   "App2",  "6", "Error2",   "App2",  "7", "Error1",   "App2",  "8", "Error1",   "App2",  "9", "Error1",   "App3",  "9", "Error2",   "App3",  "11", "Error1",   "App4",  "11", "Error1",   "App4"  ];

Below is what i have tried,

let FactTable = Temptable
| where Message == "start"
| summarize by AppName
| project AppName;
let LatestRun = FactTable
| join kind = inner (Temptable | where timestamp < ago(6h) and Message == "start") on AppName
| summarize arg_max(timestamp,*) by AppName
| project AppName,RunId;
LatestRun
| join kind = leftouter (Errortable) on AppName,RunId
| summarize Count_Error = count(Error) by AppName,CurrentRunId = RunId

enter image description here

If i summarize the result, For "App4", count is retrieved as 1 which is wrong.

How can i achieve this? I need to display current run and previous run as well, as shown in the above snippet.

Can someone give some idea how to crack this?

Edit 2: I have figured out an answer, but i am not sure this is the good approach. 

let FactTable = Temptable
| where Message == "start"
| summarize by AppName
| project AppName;
let LatestRun = FactTable
| join kind = inner (Temptable | where timestamp < ago(6h) and Message == "start") on AppName
| summarize arg_max(timestamp,*) by AppName
| project AppName,RunId;
let PreviousRun = FactTable
| join kind = inner (Temptable | where timestamp < ago(6h) and Message == "start") on AppName
| join kind= inner ( LatestRun) on AppName
| where RunId != RunId1
| summarize arg_max(timestamp,*) by AppName
| project AppName,RunId;
let CurrResult = FactTable
| join kind = leftouter (LatestRun) on AppName
| join kind = leftouter (Errortable) on AppName,RunId
| summarize Curr_ErrorCount = count(isnotempty(Error)) by AppName,RunId
| project AppName,Curr_RunId = RunId,Curr_ErrorCount;
let PrevResult = FactTable
| join kind = leftouter (PreviousRun) on AppName
| join kind = leftouter (Errortable) on AppName,RunId
| summarize Prev_ErrorCount = count(isnotempty(Error)) by AppName,RunId
| project AppName,Prev_RunId = RunId,Prev_ErrorCount;
LatestRun
| join kind = leftouter (CurrResult) on AppName
| join kind = leftouter (PrevResult) on AppName
| project AppName, Curr_RunId, Curr_ErrorCount, Prev_RunId,Prev_ErrorCount
| order by AppName asc

enter image description here

Any better approach to achieve this?

kql azure-data-explorer azure-application-insights azure
2 Answers

You can also try the following, using top-nested:

let runs = Temptable 
| where Message  == "start" 
| top-nested of AppName by min(1), top-nested 2 of RunId by timestamp=min(timestamp) desc;
runs
| join kind=leftouter Errortable on RunId
| summarize CountErrors=countif(isnotempty(Error)), timestamp = max(timestamp) by AppName, RunId
| order by AppName asc, timestamp desc 
| extend P = pack_all()
| summarize runs = make_list(P) by AppName
| project AppName = runs[0].AppName, Cur_RunId = runs[0].RunId, Cur_ErrorCount = runs[0].CountErrors, Prev_RunId = runs[1].RunId, Prev_ErrorCount = runs[1].CountErrors

enter image description here

user_103721530

Best to my knowledge, your "Edit 2" KQ is the perfect.

user_119871270
Related