Splunk equivalent of SQL window function query

vats — Thu, 07 May 2020 06:48:09 GMT

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.

Re: Splunk equivalent of SQL window function query

jkat54 — Thu, 07 May 2020 10:20:41 GMT

Something like this but add more latest() earliest() for tid

index=yourIndex source=yoursource sourcetype=yourSourcetype | stats latest(cid) earliest(cid) by step

topic Re: Splunk equivalent of SQL window function query in All Apps and Add-ons

Splunk equivalent of SQL window function query

Re: Splunk equivalent of SQL window function query