Setting the starttime and endtime in an eval

[njfrost]

I wrote a macro where ReleaseInterval2013(month) evaluates starttime and endtime based on the month I select. The starttime and endtime are static dates for each month. I want to save myself from replacing the start/endtimes for every search I do with any month I choose.

My implementation does not work: the starttime and endtime are ignored.

This is what my macro definition looks like:

eval starttime=case($month$=="Jan", "12/8/2012:00:00:00", ...) | eval endtime=case($month%="Jan", 01/12/2013:00:00:00", ...)

And my search is like this:

searchterms | eval ReleaseMonth="Jan" | `ReleaseInterval2013(ReleaseMonth)` | stats count by lineOfBusiness

I am guessing it is not possible to set these values with eval because eval is evaluated for each log item and the search cannot respond to value changes after the search has started.

Is this assumption correct? And more importantly, what is the best approach to take in my situation? Times.conf only supports relative times as far as I know.

Thank you!

[lguinn2]

Okay, here is how I would do it. First, I would have a CSV file that looks like this:

month,startingTime,endingTime
Jan-2013,8-Jan-2013 0:00:00,27-Jan-2013 0:00:00

etc. Load this into Splunk as a lookup file and create a lookup for it. I will call the lookup date_lookup in my example. Good info here on how to set up a lookup.

Then I would create a macro that takes one argument, just as you did: ReleaseInterval(ReleaseMonth)

Here is what I would put in my macro:

eval tempMon=$ReleaseMonth$ 
| lookup date_lookup month as tempMon 
| eval tempStart=strptime(startingTime,"%d-%m-%Y %H:%M:%S")
| eval tempEnd =strptime(endingTime,"%d-%m-%Y %H:%M:%S")
| where _time >= tempStart AND _time <= tempEnd
| fields - tempMon tempStart tempEnd

Use the macro like this

searchterms |  `ReleaseInterval(Jan-2013)` | stats count by lineOfBusiness

Now you can setup the CSV file for multiple years, and you only have to update the CSV file to keep things going.

Let me know if you have problems with this, as my typing is never that great!

[gkanapathy]

You can just use a subsearch:

sourcetype=mybasesearchst "my search terms [ stats count | eval earliest="12/31/2012:12:34:56" | eval latest="1/31/2013:12:00:00" | return earliest latest ]

or

sourcetype=mybasesearchst "my search terms [  stats count | `mymacro` | return earliest=starttime latest=endtime ]

please note that earliest and latest are preferred over starttime and endtime, so I renamed your fields, though technically they may still work.

[gkanapathy]

i needed to start these subsearches with stats count to make the evals work.

[gkanapathy]

I made an error in the subsearch, correcting it now.

[Ayn]

Yes, the subsearch runs before the main search.

[njfrost]

This is new territory for me. I'll provide an update if I figure something out. Thanks for your suggestion.

[njfrost]

I am having trouble getting this to work. I cannot get the earliest and latest to be returned to the main search for use. Does the subsearch run before the main search?

[gkanapathy]

Also, I don't know if this is helpful, but rather than the eval, you could also specify a date within the month using relative times:

earliest=@mon+1w@w1+13h

if you can specify your dates that way.

[lguinn2]

You could do this:

yoursearchhere earliest=@m

This will search only the current month, whatever it may be. I don't know how you could make a macro that was any easier...

[njfrost]

Sorry! Please don't take offense to the down vote. I don't think this is a valid suggestion to my question.

I am not familiar with lookup tables. I am researching this now.
Thank you for the suggestion and quick response.

[lguinn2]

You would be better off to use a lookup table then. But don't vote down valid suggestions if you want people to offer more help,

[njfrost]

The start and end of my monthly intervals are predefined. They are mostly the second Saturday of the month but not always.
I have a list of start and end dates and I want to reference them by number or keyword.