https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252319#M75484 <P>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.</P> <P>To deal with the issue I'd like to convert the pivot expression into an <EM>equivalent</EM> non-pivot search expression.</P> <P>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.</P> <P>Is there an easy way to convert a pivot expression into a non-pivot search?</P> Wed, 12 Oct 2016 15:35:48 GMT bhawkins1 2016-10-12T15:35:48Z https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252319#M75484 <P>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.</P> <P>To deal with the issue I'd like to convert the pivot expression into an <EM>equivalent</EM> non-pivot search expression.</P> <P>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.</P> <P>Is there an easy way to convert a pivot expression into a non-pivot search?</P> Wed, 12 Oct 2016 15:35:48 GMT https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252319#M75484 bhawkins1 2016-10-12T15:35:48Z https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252320#M75485 <P>You can open you inline Pivot search in the Job inspector to see how Splunk translates the Pivot into other commands like <CODE>tstats</CODE>. </P> <P>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 <CODE>litsearch</CODE>, <CODE>reportsearch</CODE>, etc. fields. The non-pivot equivalent is basically those pieces put together.</P> Wed, 12 Oct 2016 15:39:05 GMT https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252320#M75485 rjthibod 2016-10-12T15:39:05Z https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252321#M75486 <P>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.</P> Wed, 12 Oct 2016 15:40:28 GMT https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252321#M75486 rjthibod 2016-10-12T15:40:28Z https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252322#M75487 <P>Thanks! I can see that the pivot is working by simply appending a <CODE>| sort limit=1000000 key</CODE> to the end of the query. Removing or otherwise changing this should remove the limit - correct?</P> Wed, 12 Oct 2016 15:52:12 GMT https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252322#M75487 bhawkins1 2016-10-12T15:52:12Z https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252323#M75488 <P>you will want to use <CODE>sort 0 key</CODE> to have unlimited results</P> Wed, 12 Oct 2016 15:56:50 GMT https://community.splunk.com/t5/Splunk-Search/How-to-convert-inline-pivot-expression-into-equivalent-non-pivot/m-p/252323#M75488 rjthibod 2016-10-12T15:56:50Z