Splunk Search

Translating SQL Query into Splunk Search Query: "LAG(...) OVER (...)"

syusjk6
Engager

Hi, I got stuck in translating the following SQL query into Splunk Search Query:

"LAG ( BCOLLDT, 1) OVER ( PARTITION BY PID ORDER BY PID, BCOLLDT, LASTREPTDT, PRCPDD, EXECPRCPUNIQNO )"

Here, BCOLLDT, PID, LASTREPTDT, PRCPDD, EXECPRCPUNIQNO are fields, respectively.

Any help ??

Tags (1)
0 Karma

Ayn
Legend

I'm not very proficient in Oracle SQL syntax either, but maybe this could help somehow? http://splunk-base.splunk.com/answers/41986/lead-lag-in-splunk

lguinn2
Legend

It would help those of us who don't use Oracle SQL if we could understand the problem in English. My interpretation is

For each PID, sort the events by the list of fields, then compare the BCOLLDT value in each event with the BCOLLDT value in the preceding event.

But I could be very wrong. And that still doesn't tell me - "what are you trying to accomplish?"

I often find that a completely different approach with Splunk can give a better answer more quickly. I hesitate to simply translate from SQL to SPL.

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...