SOLVED

Kusto - Compare multiple returned values

Copper Contributor

Hi all,

 

I would like to compare the HTTP 5xx and 2xx codes from the W3CIISLog in Azure Monitor using Kusto.

 

How do you return two or more values and then compare against eachother?

 

For example, I can return all 2xx and 5xx values using:

 

search "W3CIISLog"//
| where scStatus startswith "2" or scStatus startswith "5"

 

But then I want what each returns into a variable so I can then compare to eachother.

 

Thanks

4 Replies

Hi@Chris Peacock 

Can you elaborate what you want and how you want to compare them? Do you want to compare count or something ?

@Stanislav Zhelyazkov 

 

Sure!

 

So, I'd like to gather all the 2xx codes in a 24 hour period and also gather all the 5xx codes in the same response from the W3CIISLog log. Then, work out the percentage of 2xx codes vs the amount of 5xx codes. So, essentially working out the percentage of OK status vs non-OK status. 

 

I hope that makes sense :)

best response confirmed by Stanislav Zhelyazkov (MVP)
Solution

@Chris Peacock 

Ok. below is the query. As a reminder I would like to say almost never to use search operator. That operator should be used only when you discover data. When you know where the data is you should just query the table where it is. Here is the query:

 

let status2or5Count = W3CIISLog 
| where scStatus startswith "2"  or scStatus startswith "5" 
| count
| extend logs = 'IIS'
| project logs, AllCount = Count ;
let status2Count = W3CIISLog 
| where scStatus startswith "2" 
| count
| extend logs = 'IIS'
| project logs, Status2Count = Count ;
let status5Count = W3CIISLog 
| where scStatus startswith "5" 
| count
| extend logs = 'IIS'
| project logs, Status5Count = Count ;
status2or5Count
| join (
    status2Count
    | join (
        status5Count
    ) on logs 
) on logs 
| extend Status2Perc = (Status2Count*100)/AllCount
| extend Status5Perc = (Status5Count*100)/AllCount
| project AllCount, Status2Count, Status5Count, Status2Perc, Status5Perc

@Stanislav ZhelyazkovAwesome work! Many thanks

1 best response

Accepted Solutions
best response confirmed by Stanislav Zhelyazkov (MVP)
Solution

@Chris Peacock 

Ok. below is the query. As a reminder I would like to say almost never to use search operator. That operator should be used only when you discover data. When you know where the data is you should just query the table where it is. Here is the query:

 

let status2or5Count = W3CIISLog 
| where scStatus startswith "2"  or scStatus startswith "5" 
| count
| extend logs = 'IIS'
| project logs, AllCount = Count ;
let status2Count = W3CIISLog 
| where scStatus startswith "2" 
| count
| extend logs = 'IIS'
| project logs, Status2Count = Count ;
let status5Count = W3CIISLog 
| where scStatus startswith "5" 
| count
| extend logs = 'IIS'
| project logs, Status5Count = Count ;
status2or5Count
| join (
    status2Count
    | join (
        status5Count
    ) on logs 
) on logs 
| extend Status2Perc = (Status2Count*100)/AllCount
| extend Status5Perc = (Status5Count*100)/AllCount
| project AllCount, Status2Count, Status5Count, Status2Perc, Status5Perc

View solution in original post