topic Re: how to convert oracle function in Splunk Search

how to convert oracle function

rimururu01 — Mon, 28 Sep 2020 13:21:02 GMT

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

Re: how to convert oracle function

yannK — Wed, 20 Feb 2013 07:04:05 GMT

what are supposed to do those functions ?

Re: how to convert oracle function

lguinn2 — Wed, 20 Feb 2013 07:48:03 GMT

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.

Re: how to convert oracle function

martin_mueller — Wed, 20 Feb 2013 09:12:01 GMT

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

Re: how to convert oracle function

lguinn2 — Wed, 20 Feb 2013 15:56:01 GMT

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

Re: how to convert oracle function

martin_mueller — Wed, 20 Feb 2013 18:07:01 GMT

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