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.
... View more