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!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...