Optimizing the search

[abhi04]

Hi All,

How can I optimize the below query? Can we convert it to tstats?

index=abc host=def* stalled
| rex field=_raw "symbol (?<symbol>.*) /"
| eval hourofday = strftime(_time, "%H")
| where NOT (hourofday>2 AND hourofday <= 4)
| timechart dc(symbol) span=15m
| eventstats avg("count") as avg stdev("count") as stdev
| eval lowerBound=-1, upperBound=(avg+stdev*exact(4))
| eval isOutlier=if('count' < lowerBound OR 'count' > upperBound, 1, 0)
| fields _time, "count", lowerBound, upperBound, isOutlier, *
| sort -_time
| head 1
| where isOutlier=1

[abhi04]

This is awesome, thanks @bowesmana . And, yes, it was a type for the "count" 🙂

[bowesmana]

and there is a basic problem with that search anyway, which is that you are using a field called "count", which does not exist - your timechart will produce a field called dc(symbol). I assume that is a typo and that your real search does dc(symbol) as count

[bowesmana · Accepted solution]

Use TERM(stalled), as that will help filter the initial data volume retrieved

The order of commands is important - you are doing the rex before the hour constraint - change the order.

You may already have a field called date_hour (it is often extracted by default - check). If so you can put that in the search 

index=abc host=def* stalled (date_hour < 3 OR date_hour > 4)
| rex field=_raw "symbol (?<symbol>.*) /"
| timechart dc(symbol) span=15m

Replace the NOT with a positive constraint, i.e. check that the hour is < 3 or > 4 rather than NOT >2 AND <4

You can't convert it to tstats unless symbol becomes an indexed field.