Query optimization

[mlf]

Can anyone come up with a better/faster/more elegant way of achieving this?

CALL_NAME="method-1" CALL_STATUS="CALL_ENDED" 
| bucket _time span=5m
| stats count as count_1, avg(CALL_DURATION) as duration_1 by _time 
| join _time [ 
  search CALL_NAME="method-2" CALL_STATUS="CALL_ENDED" 
  | bucket _time span=5m
  | stats count as count_2, avg(CALL_DURATION) as duration_2 by _time ] 
| eval duration_D=duration_2 - duration_1
| where ( count_1 >= 5 AND duration_1 > 4000 ) OR (count_2 >= 5 AND duration_2 > 10000 ) OR (count_1 >= 5 AND count_2 >= 5 AND duration_D > 7000)

I can get the basic values with this:

CALLNAME="method-1" OR CALLNAME="method-2" CALLSTATUS="CALL_ENDED" 
| timechart span=5m count(CALLDURATION) as count, avg(CALLDURATION) as duration by _time, CALLNAME

but I can't figure out how to get the 'where' logic to work with the resulting column names.

Any ideas?

[lguinn2]

The following will do everything except for dealing with duration_D...

CALLNAME="method-1" OR CALLNAME="method-2" CALLSTATUS="CALL_ENDED" 
| bucket span=5m _time
| stats count(CALLDURATION) as count, avg(CALLDURATION) as duration by _time, CALLNAME
| where count > 5
| where (CALLNAME="method-1" AND duration > 4000) OR (CALLNAME="method-2" AND duration > 10000)

[spock_yh · Accepted solution]

Actually, you probably meant not to use _time in the "by" clause of the timechart command, since timechart is already by time.

If you do:

CALLNAME="method-1" OR CALLNAME="method-2" CALLSTATUS="CALL_ENDED" 
| timechart span=5m count(CALLDURATION) as count, avg(CALLDURATION) as duration by CALLNAME

timechart will produce 4 columns, named "count: method-1", "duration: method-1", "count: method-2", and "duration: method-2". You can then perform the rest of the search using those fields, but to make things a bit simpler I'd first rename the fields:

CALLNAME="method-1" OR CALLNAME="method-2" CALLSTATUS="CALL_ENDED" 
| timechart span=5m count(CALLDURATION) as count, avg(CALLDURATION) as duration by CALLNAME
| rename
  "count: method-1" as count_1, "count: method-2" as count_2,
  "duration: method-1" as duration_1, "duration: method-2" as duration_2
| eval duration_D=duration_2 - duration_1
| where ( count_1 >= 5 AND duration_1 > 4000 ) OR (count_2 >= 5 AND duration_2 > 10000 ) OR (count_1 >= 5 AND count_2 >= 5 AND duration_D > 7000)

Note that for cases where timechart isn't appropriate, you can do the same using "stats", but instead of 2 aggregation expressions you would use 4 with an "eval" condition, as follows:

stats
  sum(eval(if(CALLNAME=="method-1",1,0))) as count_1,
  sum(eval(if(CALLNAME=="method-2",1,0))) as count_2,
  avg(eval(if(CALLNAME=="method-1",CALLDURATION,null()))) as duration_1,
  avg(eval(if(CALLNAME=="method-2",CALLDURATION,null()))) as duration_2
by whatever_field

Hope this helps,
Yair Halevi (Spock)

[dwaddle]

If this resolved your issue, please accept the answer as correct by clicking on the check-mark to the left of the answer. Thanks!

[mlf]

Bingo! It was actually the spaces in the column names that were tripping me up. (after figuring out that I had to single quote them in the where clause...) Good call on the rename though. Make things much simpler.