SOLVED
Home

Kusto - Compare multiple returned values

%3CLINGO-SUB%20id%3D%22lingo-sub-1001786%22%20slang%3D%22en-US%22%3EKusto%20-%20Compare%20multiple%20returned%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1001786%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20compare%20the%20HTTP%205xx%20and%202xx%20codes%20from%20the%26nbsp%3B%3CFONT%3EW3CIISLog%3C%2FFONT%3E%20in%20Azure%20Monitor%20using%20Kusto.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20you%20return%20two%20or%20more%20values%20and%20then%20compare%20against%20eachother%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20can%20return%20all%202xx%20and%205xx%20values%20using%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3Esearch%20%22W3CIISLog%22%2F%2F%3CBR%20%2F%3E%7C%20where%20scStatus%20startswith%20%222%22%20or%20scStatus%20startswith%20%225%22%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EBut%20then%20I%20want%20what%20each%20returns%20into%20a%20variable%20so%20I%20can%20then%20compare%20to%20eachother.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EThanks%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1001786%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Explorer%20(Kusto)%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%20language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014295%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20-%20Compare%20multiple%20returned%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014295%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F96827%22%20target%3D%22_blank%22%3E%40Chris%20Peacock%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20elaborate%20what%20you%20want%20and%20how%20you%20want%20to%20compare%20them%3F%20Do%20you%20want%20to%20compare%20count%20or%20something%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014544%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20-%20Compare%20multiple%20returned%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9172%22%20target%3D%22_blank%22%3E%40Stanislav%20Zhelyazkov%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESure!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I'd%20like%20to%20gather%20all%20the%202xx%20codes%20in%20a%2024%20hour%20period%20and%20also%20gather%20all%20the%205xx%20codes%20in%20the%20same%20response%20from%20the%20%3CSPAN%3EW3CIISLog%3C%2FSPAN%3E%20log.%20Then%2C%20work%20out%20the%20percentage%20of%202xx%20codes%20vs%20the%20amount%20of%205xx%20codes.%20So%2C%20essentially%20working%20out%20the%20percentage%20of%20OK%20status%20vs%20non-OK%20status.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20makes%20sense%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014553%22%20slang%3D%22en-US%22%3ERe%3A%20Kusto%20-%20Compare%20multiple%20returned%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014553%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F96827%22%20target%3D%22_blank%22%3E%40Chris%20Peacock%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOk.%20below%20is%20the%20query.%20As%20a%20reminder%20I%20would%20like%20to%20say%20almost%20never%20to%20use%20search%20operator.%20That%20operator%20should%20be%20used%20only%20when%20you%20discover%20data.%20When%20you%20know%20where%20the%20data%20is%20you%20should%20just%20query%20the%20table%20where%20it%20is.%20Here%20is%20the%20query%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20status2or5Count%20%3D%20W3CIISLog%20%0A%7C%20where%20scStatus%20startswith%20%222%22%20%20or%20scStatus%20startswith%20%225%22%20%0A%7C%20count%0A%7C%20extend%20logs%20%3D%20'IIS'%0A%7C%20project%20logs%2C%20AllCount%20%3D%20Count%20%3B%0Alet%20status2Count%20%3D%20W3CIISLog%20%0A%7C%20where%20scStatus%20startswith%20%222%22%20%0A%7C%20count%0A%7C%20extend%20logs%20%3D%20'IIS'%0A%7C%20project%20logs%2C%20Status2Count%20%3D%20Count%20%3B%0Alet%20status5Count%20%3D%20W3CIISLog%20%0A%7C%20where%20scStatus%20startswith%20%225%22%20%0A%7C%20count%0A%7C%20extend%20logs%20%3D%20'IIS'%0A%7C%20project%20logs%2C%20Status5Count%20%3D%20Count%20%3B%0Astatus2or5Count%0A%7C%20join%20(%0A%20%20%20%20status2Count%0A%20%20%20%20%7C%20join%20(%0A%20%20%20%20%20%20%20%20status5Count%0A%20%20%20%20)%20on%20logs%20%0A)%20on%20logs%20%0A%7C%20extend%20Status2Perc%20%3D%20(Status2Count*100)%2FAllCount%0A%7C%20extend%20Status5Perc%20%3D%20(Status5Count*100)%2FAllCount%0A%7C%20project%20AllCount%2C%20Status2Count%2C%20Status5Count%2C%20Status2Perc%2C%20Status5Perc%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Chris Peacock
Occasional 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 :)

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

Related Conversations
Pivot table
gabriellerocha in Excel on
5 Replies
Compare Kusto results from three timespans
gauteweb in Azure Log Analytics on
1 Replies
How do I group rows in an excel table?
Marisa Medrano in Excel on
1 Replies
pivot problem
Patrick Naughton in Azure Log Analytics on
6 Replies