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

[bhawkins1]

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?

[rjthibod · Accepted solution]

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]

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?

[rjthibod]

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

[rjthibod]

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.