Reporting

Earliest and latest with pivot command

Communicator

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

Tags (1)
0 Karma
1 Solution

Splunk Employee
Splunk Employee

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

View solution in original post

Communicator

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.

0 Karma

Splunk Employee
Splunk Employee

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

View solution in original post

Communicator

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

0 Karma

Communicator

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 🙂

0 Karma

SplunkTrust
SplunkTrust

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.

Builder

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?

0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Communicator

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma