I am looking for a Splunk query equivalent to the following SQL query involving window functions.
Assuming I have a table like so:
CREATE TABLE my_stats_tbl ( time timestamp, cid character varying(10), tid character varying(10), step character varying(10) );
and SQL query:
select cid, tid, step, min(time) as earliest, max(time) as latest from ( select time, cid, tid, step, cid_recent_time, dense_rank() over (order by cid_recent_time desc) as cid_recency_rank from ( select time, cid, tid, step, max(time) over(partition by cid) as cid_recent_time from my_stats_tbl )q1 ) q2 where cid_recency_rank = 1 group by cid, tid, step order by cid, tid, step
How do i accomplish this in Splunk, assuming I have a search query which generates an output similar to my SQL table my_stats_tbl
?
Essentially, I want to:
1. Select only those rows from my search query that correspond to the most recent cid
AND.
2. Amongst rows selected in step-1 above, I want to find the earliest and latest timestamps for each cid
, tid
and step
triplet.
I'd appreciate any pointers.
Something like this but add more latest() earliest() for tid
index=yourIndex source=yoursource sourcetype=yourSourcetype | stats latest(cid) earliest(cid) by step