How do I narrow my base query by time

[chris94089]

Greetings,

I want to use one base query for my dashboard, with time going back a couple months.   I thought I would populate one big search and and then have dashboards narrow it by relative time chunks for day/week/month, I tried using 

| where

but I noticed that 

 |  mysearch  earliest=08/10/2020:00:00:00 latest=@d | where _time>relative_time("@d", "-2d@d")

does not generate results since relative_time() wants an epoch time as the first argument, which is lame since the other argument totally accepts relative snap-to time.

Is there a way to do this?

[richgalloway]

The relative_time function makes time math easier.  It takes an epoch timestamp and returns a new epoch timestamp that is offset from the original by the amount specified in the second argument.  That the second argument must be a relative time specifier has no bearing on what the first argument is allowed to be.

I believe you can narrow your base query this way

|  mysearch  earliest=08/10/2020:00:00:00 latest=@d 
| where _time>relative_time(now(), "-2d@d")

[chris94089]

Ok, this does "work" in that it returns search results.  But if anything it makes things more frustrating since it's the only relative value that relative_time() function seems to support in the first argument.  Literally every doc I've seen uses the now() function in the relative_time() examples.  But the devs should know that not all data needs to be thought of as "up to now."

where _time>relative_time(now(), "-2d@d")

is allowed, but

where _time>relative_time("@w", "-2w@w")

is not allowed? (I changed from days to weeks, to hopefully better illustrate the use case)

Accounting, for example, doesn't necessarily require data up to the moment the report is being viewed.  It hinges on defined periods like months and quarters.  In sales, weeks become more important.

And the devs do support this concept since the latest= key can be set to something other than now at search time, but it seems this functionality is only supported at the start of the pipe, and not further down.  This behavior ultimately limits the usefulness of scheduled searches and base queries in dashboards.

[richgalloway · Accepted solution]

What is 

where _time>relative_time("@w", "-2w@w")

supposed to mean?  What exactly is supposed to be relative to "@w" and "-2w@w"?

That you usually see now() as the first argument to relative_time does not mean that is the only option. The first argument can be any number and that number will be interpreted as an epoch timestamp and the return value will be relative to that timestamp.

To put an upper bound on time, use another call to relative_time().

where (_time>relative_time(now(), "-2w@w") AND (_time < relative_time(now(), "@w"))

Of course, all data are constrained to the limits of the search window, either via the time picker or the earliest and latest options.

BTW, earliest and latest are options to the search command (which is implied before the first pipe).

[chris94089]

that last little bit is super creative.  I need to try it