Splunk Search

How to convert inline pivot expression into equivalent non-pivot search expression?

bhawkins1
Communicator

I have a pivot query that produces a one-million row table with ~50 columns. I'd like to extend the limit for that table to at least 10-million rows, but pivot tables can't do this.

To deal with the issue I'd like to convert the pivot expression into an equivalent non-pivot search expression.

To do this manually, the only solution I've thought of so far involves ~50 joins (one for each column). I'm confident that splunk has done something smarter to generate the pivot.

Is there an easy way to convert a pivot expression into a non-pivot search?

0 Karma
1 Solution

rjthibod
Champion

You can open you inline Pivot search in the Job inspector to see how Splunk translates the Pivot into other commands like tstats.

If you run the search in the UI, you should be able to click the "job inspector" icon and expand the details to see the contents of the litsearch, reportsearch, etc. fields. The non-pivot equivalent is basically those pieces put together.

View solution in original post

rjthibod
Champion

You can open you inline Pivot search in the Job inspector to see how Splunk translates the Pivot into other commands like tstats.

If you run the search in the UI, you should be able to click the "job inspector" icon and expand the details to see the contents of the litsearch, reportsearch, etc. fields. The non-pivot equivalent is basically those pieces put together.

bhawkins1
Communicator

Thanks! I can see that the pivot is working by simply appending a | sort limit=1000000 key to the end of the query. Removing or otherwise changing this should remove the limit - correct?

0 Karma

rjthibod
Champion

you will want to use sort 0 key to have unlimited results

rjthibod
Champion

Note, this suggestion is not going to be the optimal solution, but it is a solution. Having that many joins is concerning, so the optimal solution would depend on many other factors like how many datamodels are you searching, how many different datamodel objects, accelerated or not accelerated datamodels, etc.

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...