Forum Discussion

Chris Peacock's avatar
Chris Peacock
Copper Contributor
Nov 12, 2019
Solved

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 ex...
Azure Data Explorer (Kusto)
Kusto language
  • Stanislav_Zhelyazkov's avatar
    Stanislav_Zhelyazkov
    Nov 18, 2019

    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_Zhelyazkov's avatar
Stanislav_Zhelyazkov
MVP
Nov 18, 2019

HiChris Peacock 

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

  • Chris Peacock's avatar
    Chris Peacock
    Copper Contributor
    Nov 18, 2019

    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 🙂

    • Stanislav_Zhelyazkov's avatar
      Stanislav_Zhelyazkov
      MVP
      Nov 18, 2019

      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

Resources