Splunk Search

Optimizing the search

abhi04
Communicator

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
Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

 

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.

 

View solution in original post

abhi04
Communicator

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

 

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.

 

Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...