https://community.splunk.com/t5/Splunk-Search/Reducing-the-relation-of-a-report-from-stats/m-p/70873#M180735 <P>whats the raw data look like ? i.e. do D1 and D2 exist in the same event, or are they always in seperate events ?</P> Fri, 21 Dec 2012 16:01:07 GMT jonuwz 2012-12-21T16:01:07Z https://community.splunk.com/t5/Splunk-Search/Reducing-the-relation-of-a-report-from-stats/m-p/70872#M180734 <P>I'm sure this is a really simple one, but for some reason I can't get my head around how to format a result table in a specific way.</P> <P>I have a search that I run through stats. The stats command is like:</P> <PRE><CODE>... | stats sum(started) as started sum(finished) as finished by D1 D2 D1 D2 started finished did1 did2 3 4 did1 no2 4 8 no1 did2 5 9 no1 no2 6 5 </CODE></PRE> <P>But I want to reduce it down, without using appends (e.g. with only eval, chart and/or stats) to a format like:</P> <PRE><CODE>Dimension started finished did1 7 12 no1 11 14 did2 8 13 no2 10 13 </CODE></PRE> <P>It's sort of like a pivot I guess...</P> <P>thanks in advance.</P> Fri, 21 Dec 2012 15:30:16 GMT https://community.splunk.com/t5/Splunk-Search/Reducing-the-relation-of-a-report-from-stats/m-p/70872#M180734 brettcave 2012-12-21T15:30:16Z https://community.splunk.com/t5/Splunk-Search/Reducing-the-relation-of-a-report-from-stats/m-p/70873#M180735 <P>whats the raw data look like ? i.e. do D1 and D2 exist in the same event, or are they always in seperate events ?</P> Fri, 21 Dec 2012 16:01:07 GMT https://community.splunk.com/t5/Splunk-Search/Reducing-the-relation-of-a-report-from-stats/m-p/70873#M180735 jonuwz 2012-12-21T16:01:07Z https://community.splunk.com/t5/Splunk-Search/Reducing-the-relation-of-a-report-from-stats/m-p/70874#M180736 <P>Anyhow, assuming that you can't simplify the original search into something more suitable for processing into the final result.</P> <P>Here's how to transform your current output into the output you want.</P> <PRE><CODE>... | eval Dimension=D1." ".D2 | fields - D1 D2 | makemv Dimension | mvexpand Dimension | stats sum(finished) as finished sum(started) as started by Dimension </CODE></PRE> <P>What we're doing is duplicating the values for started and finished for the values of D1 and D2 per line.</P> Fri, 21 Dec 2012 16:13:36 GMT https://community.splunk.com/t5/Splunk-Search/Reducing-the-relation-of-a-report-from-stats/m-p/70874#M180736 jonuwz 2012-12-21T16:13:36Z https://community.splunk.com/t5/Splunk-Search/Reducing-the-relation-of-a-report-from-stats/m-p/70875#M180737 <P>thanks, it works great. d1 and d2 are determined from an MV field after a transaction: </P> <PRE><CODE>... | transaction visId | makemv someField | eval d1=mvfind(someField, "a_value") | eval d2=mvfind(someField, "b_value") | eval did_d1=if(isnotnull(d1), 1, 0) | eval did_d2=if(isnotnull(d2), 1, 0) | .... </CODE></PRE> Wed, 09 Jan 2013 11:29:03 GMT https://community.splunk.com/t5/Splunk-Search/Reducing-the-relation-of-a-report-from-stats/m-p/70875#M180737 brettcave 2013-01-09T11:29:03Z