Where command -- How can I use this on a search without affecting the base search?

[sepkarimpour]

I want to compare two identical searches but one looking for just count and the other using count | where the average response time is less than 'x' to find the percentage difference between them.

When I search for them separately, I can get what I'm looking for:

index=* ... "OK" | stats count
index=* ... "OK" | stats count, avg(ResponseTime) AS AvgRespTime | where AvgRespTime<=1000 | fields - AvgRespTime

As the base search is the same, how do I set the second one to use where without it affecting the first one as well? I've tried adding the where after, but it changes both. If I set them each to a type, e.g. base search | eval Type=P1 & base search | eval Type=P2, can I set P2 to use a WHERE command, e.g. where AvgRespTime<=1000 for P2 only?

[sepkarimpour · Accepted solution]

I used parts of both answers and ran the following with a result:

index=* ... "OK"
| eventstats perc99(ResponseTime) as p99Resp
| eval Type=if(ResponseTime<=p99Resp,"Efficient","Rest")
| stats count(eval(Type="Efficient")) as "Under 1000ms", count as Total
| eval Efficiency=100*('Under 1000ms'/Total)

[woodcock]

Don't forget to UpVote.

[woodcock]

Yes, do this:

... | stats count AS TotalCount, count(eval(avg(ResponseTime)<=1000)) AS QuickCount

[DalJeanis]

If you want the count of all records, and the count of all records with responseTime<=1000, do this

Method 1 - create fields that have the data you want, then stats them.

   index= ... "OK" 
  | eval QuickCount=if(ResponseTime<=1000,1,0)
  | eval QuickResponse=if(ResponseTime<=1000,ResponseTime,null())
  | stats count as TotalCount, avg(ResponseTime) AS AvgRespTime, 
     sum(QuickCount) as QuickCount, 
     avg(QuickResponse) as avgQuickResponse

Method 2 - eval them in a stats command

   index= ... "OK" 
  | eval QuickCount=
  | eval QuickResponse=
  | stats count as TotalCount, avg(ResponseTime) AS AvgRespTime, 
     sum(eval(if(ResponseTime<=1000,1,0))) as QuickCount, 
     avg(eval(if(ResponseTime<=1000,ResponseTime,null()))) as avgQuickResponse

[sepkarimpour]

Is there a way I can have it so it's the following:

... | stats count as TotalCount, sum(eval(if(avg(ResponseTime)<=1000,1,0))) as QuickCount

To find the percentage difference, I ran the query below:

index= ... "OK"
| eval QuickCount=if(ResponseTime<=1000,1,0)
| eval QuickResponse=if(ResponseTime<=1000,ResponseTime,null())
| stats avg(ResponseTime) AS AvgResponseTime, count as TotalCount, sum(eval(if(AvgResponseTime<=1000,1,0))) as QuickCount
| eval Percentage=100*(TotalCount-QuickCount)/TotalCount
| fields - AvgResponseTime

I get no results for Quick Count now when I try this though.

[DalJeanis]

@sepkarimpour - the reason you get no results with that code is because
A) AvgResponseTime does not exist until after stats, but
B) you are using AvgResponseTime in stats to calculate QuickCount, which becomes null, and
C) you are using Quickcount to calculate Percentage, which becomes null.

The way to debug that kind of issue if you just can't see it (which most of us can't in our own code) is to cut off one line at a time from the end until the data reappears, or until you notice something missing that should be there. When you have an exact thing that is wrong, then you follow that thing back up the code until you find the misspelling, miscapitalization, or logic error that caused it.

If you can't find it, then you can either keep deleting one line at a time until the data looks right again, or do a binary search - delete a bunch of code, and when the results of what is left looks right, flip between adding half of the last chunk back or deleting another half chunk until you zero in on the exact line that causes a problem.