Archive
Highlighted

Is it possible to have a macro take an argument, run a search constructed from a lookup, and return a result?

Builder

Hello all,

I am trying to accomplish something and I'm not sure it's possible. I will try to explain my case using a simplified example:

I have a lookup containing dates that define periods. The dates define the start of a period which is always a Saturday, and each period has a third Monday which needs to be calculated. The lookup looks like this:

Calendar_year,Month,Day,Period
2018,1,27,1
2018,2,24,2
2018,3,31,3
2018,4,28,4
...

In order to get the third Monday for each period I use the following:

| inputlookup lookup.csv
| eval Period_Start = strptime(Calendar_year."-".Month."-".Day." 12:00:00","%Y-%m-%d %H:%M:%S")
| eval Third_Monday=strftime(Period_Start+(23*24*60*60),"%Y-%m-%d")

I have an index that contains event data and I need to know whether the _time for a given event corresponds to the third Monday of one of my periods. In order to do this first I need to explode my lookup so that it gives me values for each day. I do this by appending the following to the above search:

| eval _time = Period_Start
| chart values(*) as * over _time span=1d
| filldown

This will give me daily breakdown for each period where I will also have the ThirdMonday field. Then I have converted the search into a macro called `isthird_monday(1)that consists of the above code, takes 1 argument calledday` and outputs a single field. To ensure that I can filter the desired day, I added the following:

| eval Day = strftime(_time,"%Y-%m-%d")
| search Day="$day$"
| table Third_Monday

Now for the part that I cannot get working. I would like to know whether I can call this macro and return the Third_Monday field. What I am playing around with:

index=my_index
| eval Day=strftime(_time,"%Y-%m-%d")
| eval Third_Monday=if(Day=[`is_third_monday(Day)`],1,0)

It's not working, and I don't even know whether I can accomplish what I want this way.

Could somebody please help me understand whether I am wasting my time, or whether there are alternative routes to getting the results I'm looking for? Am I just crazy?

Thank you and best regards,

Andrew

Tags (1)
0 Karma
Highlighted

Re: Is it possible to have a macro take an argument, run a search constructed from a lookup, and return a result?

You have a bunch of events, and you want to know if they correspond to particular time markers - that's a case for using a time-bounded lookup.

I'd recommend converting that existing csv file with non-standard date formats to a new lookup with only two columns. The first column will be a time stamp for the start of the third Monday in the given period, and the second column will be the period identifier. Then you'll configure your time-bounded lookup with max_offset_secs=86400 (the number of seconds in a day). Then you can just use that lookup file to see if a given event takes place on a third Monday:

| lookup third_mondays.csv _time OUTPUT period

and if you want to filter out events that don't take place on a third Monday, append this:

| where isntonull(period)
0 Karma