topic Re: Earliest and latest with pivot command in Reporting

Earliest and latest with pivot command

OL — Wed, 20 Aug 2014 09:52:31 GMT

Hello,

Does anyone know if there is a way to add an earliest and latest with the pivot command?

Adding earliest or earliest_time doesn't not work.

Just to clarify, I don't want to use the timepicker here, I want to write a pivot command command in the same way I would write: "index=_internal earliest=-15m latest=now"

Regards,
Olivier

Re: Earliest and latest with pivot command

martin_mueller — Wed, 20 Aug 2014 10:03:21 GMT

I don't think so. What are you trying to achieve here?

Re: Earliest and latest with pivot command

OL — Wed, 20 Aug 2014 10:27:17 GMT

Hi Martin, thank you for replying. I'm trying to do subsearches with pivot using different time ranges

Re: Earliest and latest with pivot command

martin_mueller — Wed, 20 Aug 2014 11:07:52 GMT

I see. Assuming my feeling is correct and there is currently no way to specify the time range for a pivot command inline, I see two ways around this. First, it might be possible to build your search using only one larger pivot - that depends on what you're doing. Second, since you apparently already are writing searches manually rather than using the Pivot UI, you could consider falling back to regular search language.
Personally I'd explore the first option, since there probably is a good reason you're using pivot manually rather than traditional search language.

Re: Earliest and latest with pivot command

gkanapathy — Wed, 20 Aug 2014 12:55:19 GMT

Use _time > 1234567890 or whatever as part of your filter. Or better and more efficient, don't use pivot. Use tstats and the where clause of tstats

Re: Earliest and latest with pivot command

OL — Wed, 20 Aug 2014 13:45:10 GMT

Indeed, there was a reason why I wanted to use pivot and it is take advantage of the acceleration of the data model, so indeed the second position isn't a possibility for me. About the first one, it will be quite tricky to achieve it also because there is lots of subsearches and "join type=left". But thank you for the tips.

Re: Earliest and latest with pivot command

Simon — Wed, 20 Aug 2014 13:48:43 GMT

Is it possible to use the eval function relative_time()?

Re: Earliest and latest with pivot command

martin_mueller — Wed, 20 Aug 2014 13:52:27 GMT

Yeah, but probably not directly. You can however define an eval-based macro that does little more than call relative_time().

[relative_time(1)]
args = relative
definition = relative_time(time(), "$relative$")
iseval = 1

This is evaluated before the actual search starts.

Re: Earliest and latest with pivot command

OL — Wed, 20 Aug 2014 15:33:48 GMT

Hey gkanapathy! Thank you for the answer. How would you use the _time in the pivot and tstats commands?

I tried the "| pivot ... FILTER _time>1407684453" but no luck. This sounds promising. I start to understand why you say to not use pivot, btw, it takes ages to initialise.

@Martin, nice one, didn't know you could do that with macros 🙂

Re: Earliest and latest with pivot command

OL — Wed, 20 Aug 2014 16:17:42 GMT

@gkanapathy, I managed to make it work with tstats. Thx a lot.

Re: Earliest and latest with pivot command

helge — Wed, 27 Jan 2016 21:51:26 GMT

Nice idea, but you cannot use the "greater than" operator with pivot command filters, e.g. this does not work:

| pivot
...
filter _time > `relative_time("-5m")`

Or did you have something else in mind?