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 |||

 

Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...