Splunk Dev

How to optimize this query?

spoo
Explorer

index="abcd"
| eval _time = strptime(TS_Changed_At,"%d/%m/%Y %H:%M")
| sort 0 ID _time
| dedup ID _time
| eventstats last(Status) as current_status by ID
| where current_status="AAA" OR current_status="BBB" OR current_status="CCC"
| streamstats current=f window=1 values(Status) as prev_status by ID
| where NOT Status=prev_status
| eval Cal= if(Status="CCC" AND (NOT prev_status="AAA " AND NOT prev_status="BBB"),substr(TS_Last_Status_Change,1,16),if(Status="BBB" AND NOT prev_status="AAA",substr(TS_Last_Status_Change,1,16),if(Status="AAA",substr(TS_Last_Status_Change,1,16),"")))
| where NOT Cal=""
| eventstats max(eval(strptime(Cal,"%d/%m/%Y %H:%M"))) as max_ by ID
| where max_ = strptime(Cal,"%d/%m/%Y %H:%M")
| table ID Cal

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

What is the goal of the query?  There may be a more efficient way to accomplish the same thing.

Here are some general tips:

  • Make the base search (before the first pipe) as specific as possible to reduce the number of events read from the index.
  • Use the fields command early to eliminate unused fields.
  • Sort only when and where necessary.
  • Use non-distributable commands as late in the query as possible.  The first non-distributable command makes the entire query single-threaded (so to speak).
index="abcd" ID=* TS_Changed_At=* sourcetype=foo
| fields ID TS_Changed_At Status Cal
| eval _time = strptime(TS_Changed_At,"%d/%m/%Y %H:%M")
| dedup ID _time
| eventstats last(Status) as current_status by ID
| where current_status="AAA" OR current_status="BBB" OR current_status="CCC"
| sort 0 ID _time
| streamstats current=f window=1 values(Status) as prev_status by ID
| where NOT Status=prev_status
| eval Cal= if(Status="CCC" AND (NOT prev_status="AAA " AND NOT prev_status="BBB"),substr(TS_Last_Status_Change,1,16),if(Status="BBB" AND NOT prev_status="AAA",substr(TS_Last_Status_Change,1,16),if(Status="AAA",substr(TS_Last_Status_Change,1,16),"")))
| where NOT Cal=""
| eventstats max(eval(strptime(Cal,"%d/%m/%Y %H:%M"))) as max_ by ID
| where max_ = strptime(Cal,"%d/%m/%Y %H:%M")
| table ID Cal

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

What is the goal of the query?  There may be a more efficient way to accomplish the same thing.

Here are some general tips:

  • Make the base search (before the first pipe) as specific as possible to reduce the number of events read from the index.
  • Use the fields command early to eliminate unused fields.
  • Sort only when and where necessary.
  • Use non-distributable commands as late in the query as possible.  The first non-distributable command makes the entire query single-threaded (so to speak).
index="abcd" ID=* TS_Changed_At=* sourcetype=foo
| fields ID TS_Changed_At Status Cal
| eval _time = strptime(TS_Changed_At,"%d/%m/%Y %H:%M")
| dedup ID _time
| eventstats last(Status) as current_status by ID
| where current_status="AAA" OR current_status="BBB" OR current_status="CCC"
| sort 0 ID _time
| streamstats current=f window=1 values(Status) as prev_status by ID
| where NOT Status=prev_status
| eval Cal= if(Status="CCC" AND (NOT prev_status="AAA " AND NOT prev_status="BBB"),substr(TS_Last_Status_Change,1,16),if(Status="BBB" AND NOT prev_status="AAA",substr(TS_Last_Status_Change,1,16),if(Status="AAA",substr(TS_Last_Status_Change,1,16),"")))
| where NOT Cal=""
| eventstats max(eval(strptime(Cal,"%d/%m/%Y %H:%M"))) as max_ by ID
| where max_ = strptime(Cal,"%d/%m/%Y %H:%M")
| table ID Cal

 

---
If this reply helps you, Karma would be appreciated.

spoo
Explorer

Thanks much. 

This search has completed and has returned 5,225 results by scanning 29,868 events in 0.592 seconds. Where the older one's was 1.606 seconds which is great news |||

 

Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...