CODEWITHSUNDEEP
azure-data-explorerkqlkusto-explorer
user4493342
user4493342

Reputation: 3

Calculate the success rate for each Command for each day for 28 days [Azure Data Explorer]

I have data with the schema

Command isSuccess TimeStamp
Cmd1 0 2020-12-01 1:00:00.00
Cmd2 1 2020-12-02 2:00:00.00
Cmd1 1 2020-12-01 3:00:00.00
Cmd2 1 2020-12-02 4:00:00.00

This data stretches over the course of many days with many records per day. I want to calculate the success rate for each cmd per day and return that as a table with the schema: Day Date, Command, Success Rate

I was thinking of using bin() to split data by days, but I was unsure how to calculate the success rate while using bin(). The best I can think of is calculating the success rate for each day (lets say 28 days) individually, then union() those rows together for each day (28 union() calls). That seems hacky.

The goal of this is to create a table where I can use stdev() and avg() to find success rates that are anomalies for a Command.

Can you please advise me on how to best calculate the success rate for every command for each day in 28 days and return that as a table?

Upvotes: 0

Views: 818

Answers (1)

Yoni L.
Yoni L.

Reputation: 25925

you could try something like this:

datatable(command:string, is_success:int, timestamp:datetime)
[
    "Cmd1", 0, datetime(2020-12-01 1:00:00.00),
    "Cmd2", 1, datetime(2020-12-02 2:00:00.00),
    "Cmd1", 1, datetime(2020-12-01 3:00:00.00),
    "Cmd2", 1, datetime(2020-12-02 4:00:00.00),
]
| summarize success_rate = 100.0 * countif(is_success == 1) / count() 
         by command, day = startofday(timestamp)
command day success_rate
Cmd1 2020-12-01 00:00:00.0000000 50
Cmd2 2020-12-02 00:00:00.0000000 100

Upvotes: 1

Related Questions