topic Re: How to optimize this query? in Splunk Dev

How to optimize this query?

spoo — Thu, 11 May 2023 12:24:06 GMT

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

Re: How to optimize this query?

richgalloway — Thu, 11 May 2023 13:41:29 GMT

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

Re: How to optimize this query?

spoo — Fri, 12 May 2023 05:04:48 GMT

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