Splunk Search

how to convert oracle function

rimururu01
New Member

hello .
i want to convert oracle function to splunk search.
but i don't know this conversion .

here's oracle function.

oraacle function -----------------------------------------------------------------------------------------------> splunk search

LAST_DAY(YYYYMMDD) ----------------------------------------------------------------------------------------------->???

MONTHS_BETWEEN(YYYYMMDD1, YYYYMMDD2) -------------------------------------------------------> ???

RANK() OVER ( PARTITION BY LN_YYMM ORDER BY STD_YYMM DESC ) ----------------------> ???

How to convert oracle function to splunk search?
please help me. lol

Tags (3)
0 Karma

lguinn2
Legend

I am not sure that there is a useful mapping of these functions from SQL to the Splunk search language. The whole philosophy of data retrieval and results manipulation is different. As yannK asked - what are you trying to accomplish? Splunk can probably do the same thing, but it may do it in a very different way.

You might want to look at this short article: Splunk for SQL Users in the Search Reference Manual.

All of that said, Splunk is very good with date arithmetic. For MONTHS_BETWEEN, you could do the calculation in a variety of ways. The easiest would be based on the number of days between the two dates, divided by 31:

| eval mon_between = (later_time - earlier_time) / (86400 * 31)

but other algorithms are possible. For LASTDAY, I would use a lookup table that contains the month, day and year of the last day of the month. (The lookup table could also be incorporated into the MONTHS_BETWEEN calculation.)

If you want to do this sort of thing a lot, you could create a macro to do the calculations. You could even name the macros LASTDAY and MONTHS_BETWEEN 🙂

The RANK function is a bit harder for Splunk. Sure, it can be done easily enough, but it does not map-reduce well. So it is a simple but ugly problem when you are dealing with big data. For this, we really do need to know what you are trying to do.

But maybe people with greater SQL or Splunk expertise will have a better answer.

martin_mueller
SplunkTrust
SplunkTrust

You should buy whoever built the relative time modifiers a beer 🙂

As for your mon_between, I believe with Oracle February 1st and March 1st should have exactly 1.0 months between each other. I gave it a stab like this:

[months_between(2)]
args = arg1,arg2
definition = (tonumber(strftime($arg1$, "%Y")) - tonumber(strftime($arg2$, "%Y")))*12 + (tonumber(strftime($arg1$, "%m")) - tonumber(strftime($arg2$, "%m"))) + (tonumber(strftime($arg1$, "%d")) - tonumber(strftime($arg2$, "%d")))/31
iseval = 0
0 Karma

lguinn2
Legend

Martin - brilliant suggestion for LASTDAY. I could not come up with "mon@mon-d"
but I knew there had to be a way!

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Another great tool to look at for date fiddling is the eval function relative_time(). For instance, you could implement LASTDAY like this:

| gentimes start=-1000 increment=1d | eval last_day = strftime(relative_time(starttime, "+mon@mon-d"), "%F %T") | dedup last_day

yannK
Splunk Employee
Splunk Employee

what are supposed to do those functions ?

0 Karma
Get Updates on the Splunk Community!

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...