Forum Discussion
Kusto - Compare multiple returned values
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
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
4 Replies
Can you elaborate what you want and how you want to compare them? Do you want to compare count or something ?
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 🙂
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
