Reporting

Earliest and latest with pivot command

OL
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

gkanapathy
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

OL
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

gkanapathy
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

OL
Communicator

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

0 Karma

OL
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

martin_mueller
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.

helge
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

Simon
Contributor

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

0 Karma

martin_mueller
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

OL
Communicator

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma