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

[akocak]

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.

[DalJeanis · Accepted solution]

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`

[DalJeanis]

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

[akocak]

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)

[akocak]

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.

[akocak]

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?

[DalJeanis]

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.

[woodcock]

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`