topic Re: How to use macro in first pipe to generate some strptime value in Splunk Search

How to use macro in first pipe to generate some strptime value

akocak — Tue, 15 Aug 2017 21:22:26 GMT

Hi ,
I have a Splunk DB Connect batch input that runs every 24 hours to get some table result set in Splunk.
Over the time since the index kept growing, in order to get best performance and keep data more historically, I added a variable to my SQL query that adds one more fields as PULL_DATE in the format of "%Y-%m-%d"

In Splunk, so far I called this in the second pipe as:

index=x source=y    |where PULL_DATE =  strftime(now(), "%Y-%m-%d")

and my goal is to take value into first pipe that my searches would provide a better performance,

so far I tried, strftime in the first pipe as a value, it doesn't work, Now I am trying to create a macro just to return me a value of.
strftime(now(), "%Y-%m-%d") as it is mentioned in this answer.
Splunk Answers: Is there a way to use eval before the initial event search (sourcetype=xx)?

My macro definition is:

![alt text][1]

I feel like i am close to what I need, however, I appreciate you all for your time reading this.

Re: How to use macro in first pipe to generate some strptime value

woodcock — Tue, 15 Aug 2017 23:40:53 GMT

This is your macro:

search PULL_DATE = [|makeresults | eval _time = strftime(now(), "%Y-%m-%d") | eval _time = "\"" . _time . "\"" | return $_time]

You call it like this:

index=x source=y | `YourMacroNameHere`

Re: How to use macro in first pipe to generate some strptime value

DalJeanis — Wed, 16 Aug 2017 02:08:27 GMT

Or you could do it this way...

 [| makeresults | eval search = "PULL_DATE=\"".strftime(now(), "%Y-%m-%d")."\"" | return $search]

...and call it like this...

  index=x source=y `YourMacroNameHere`

Re: How to use macro in first pipe to generate some strptime value

akocak — Wed, 16 Aug 2017 13:13:03 GMT

Thank you both for your answers, my goal is to have it like @DalJeanis way, I need it before the first pipe with index and sourcetype to eliminate extra work for Splunk. Trying DalJeanies method at this point.

Re: How to use macro in first pipe to generate some strptime value

akocak — Tue, 29 Sep 2020 15:22:21 GMT

Thanks Both again, so I made it work, job inspector results are interesting:
index=x sourcetype=y 'my_macro'
31 results by scanning 458 events in 6.727 seconds
index=x sourcetype=y |where PULL_DATE= strftime(now(), "%Y-%m-%d")
31 results by scanning 1,276 events in 2.465 seconds
index=x sourcetype=y PULL_DATE="2017-08-16"
31 results by scanning 458 events in 1.948 seconds

Macro added many seconds in performance although it limited scanned events. In my opinion, splunk should allow functions like strftime to be in the value before first pipe.
Performance wise, Did you guys have similar experience ever? or have any suggestions?

Re: How to use macro in first pipe to generate some strptime value

akocak — Tue, 29 Sep 2020 15:22:24 GMT

First, again, thank you both. I made it work out with DalJeanis solution, however, performance metrics are interesting
if i use my search as
index=x sourcetype=y 'my_macro'

31 results by scanning 458 events in 6.727 seconds
index=x sourcetype=y | where PULL_DATE = strftime(now(), "%Y-%m-%d") --> 7 seconds
31 results by scanning 1,276 events in 2.465 seconds
index=x sourcetype=y PULL_DATE = "2017-08-16"
31 results by scanning 458 events in 1.948 seconds

looks like old way of doing this is faster others.
would you guys enlighten me little as why what i expected is not there ?
Moreover, Could you move your answer to below for me to validate as right answer (will validate both)

Re: How to use macro in first pipe to generate some strptime value

DalJeanis — Wed, 16 Aug 2017 15:29:55 GMT

Hard code is always going to be faster than running a subsearch, that's just common sense.

Try it with a second eval macro that just always returns PULL_DATE="2017-08-16", and you can see how much of that 4.8 seconds difference is macro and how much is subsearch.

You can also verify that by inserting the macro code directly into the search and seeing where on the spectrum it comes out.

Re: How to use macro in first pipe to generate some strptime value

DalJeanis — Wed, 16 Aug 2017 15:35:47 GMT

@akocak - moved the comment to answer so that you can accept it and close the question.