Specific query - question

%3CLINGO-SUB%20id%3D%22lingo-sub-2812554%22%20slang%3D%22en-US%22%3ESpecific%20query%20-%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2812554%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20need%20assistance%20please.%3C%2FP%3E%3CP%3EGiven%20the%20following%20query%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EF5_CL%3CBR%20%2F%3E%7C%20where%20TimeGenerated%20%26gt%3B%3D%20ago(3m)%20%2F%2Fchange%20to%20required%20time%3CBR%20%2F%3E%7C%20extend%20RawData%3Dsplit(RawData%2C%20'%23%23')%20%2F%2Fsplit%20all%20raw%20data%20to%20specific%20values%3CBR%20%2F%3E%7C%20extend%20remote_address%20%3D%20tostring(RawData%5B1%5D)%20%2F%2F%20take%20external%20remote%20address%3CBR%20%2F%3E%7C%20extend%20URL_Path%20%3D%20trim(%40'%5C%3F(.*)'%2Ctrim(%40'(%5B%5E%5Cs%5D%2B)'%2Ctostring(RawData%5B5%5D)))%3CBR%20%2F%3E%7Cextend%20responseStatus%20%3D%20tostring(RawData%5B3%5D)%3CBR%20%2F%3E%7C%20summarize%20count()%20by%20URL_Path%2Cremote_address%3CBR%20%2F%3E%7C%20where%20responseStatus%20!%3D%20%22403%22%20and%20responseStatus%20!%3D%20%22404%22%20and%20%3CSTRONG%3EresponseStatus%3C%2FSTRONG%3E%20!%3D%20%22503%22%20and%20count_%20%26gt%3B%20150%20and%20remote_address%20!%3D%20%22%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%26nbsp%3B%3CSTRONG%3EresponseStatus%3C%2FSTRONG%3E%26nbsp%3Bis%20not%20recognized%20in%20%22where%22%20line%20since%20it's%20not%20summarize.%20I%20don't%20want%20it%20to%20be%20summarize%20since%20it%20will%20split%20all%20values%20of%26nbsp%3B%3CSTRONG%3EresponseStatus.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnybody%20has%20idea%20how%20%3CSTRONG%3Eand%20responseStatus%20!%3D%20%22503%22%26nbsp%3B%3C%2FSTRONG%3Ewill%20be%20valid%20in%20%22where%22%20line%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20the%20question%20is%20clear%2C%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2813230%22%20slang%3D%22en-US%22%3ERe%3A%20Specific%20query%20-%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2813230%22%20slang%3D%22en-US%22%3ECan%20you%20move%20this%20line%20BEFORE%20the%20summarize%3CBR%20%2F%3E%3CBR%20%2F%3E%7C%20where%20responseStatus%20!%3D%20%22403%22%20and%20responseStatus%20!%3D%20%22404%22%20and%20responseStatus%20!%3D%20%22503%22%20and%20remote_address%20!%3D%20%22%22%3CBR%20%2F%3E%3CBR%20%2F%3Ethen%20have%20this%20line%20afterwards%20the%20summarize%3F%26gt%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3E%7C%20where%20count_%20%26gt%3B%20150%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, I need assistance please.

Given the following query:


F5_CL
| where TimeGenerated >= ago(3m) //change to required time
| extend RawData=split(RawData, '##') //split all raw data to specific values
| extend remote_address = tostring(RawData[1]) // take external remote address
| extend URL_Path = trim(@'\?(.*)',trim(@'([^\s]+)',tostring(RawData[5])))
|extend responseStatus = tostring(RawData[3])
| summarize count() by URL_Path,remote_address
| where responseStatus != "403" and responseStatus != "404" and responseStatus != "503" and count_ > 150 and remote_address != ""

 

The problem is that responseStatus is not recognized in "where" line since it's not summarize. I don't want it to be summarize since it will split all values of responseStatus.

 

Anybody has idea how and responseStatus != "503" will be valid in "where" line?

 

I hope the question is clear, Thanks.

2 Replies
Can you move this line BEFORE the summarize

| where responseStatus != "403" and responseStatus != "404" and responseStatus != "503" and remote_address != ""

then have this line afterwards the summarize?>

| where count_ > 150

@CliveWatson 
Wow!
It works, Thanks!!