https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55310#M13498 <P>UPDATED: </P> <P><CODE>eventtype=visit | stats count by city person | eventstats dc(person) as totalPeople | stats dc(person) as visitors last(totalPeople) as totalPeople by city | eval percentage=100*(visitors/totalPeople) | table city percentage</CODE></P> Fri, 08 Mar 2013 05:59:41 GMT sideview 2013-03-08T05:59:41Z https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55309#M13497 <P>I am having a ton of trouble expressing this query.</P> <P>Suppose I have 1,000 distinct people, and 25 cities. Over a time period, each person might visit [0...n] cities.</P> <P>I want a report which shows, for each city, what percentage of my unique people visited that city? Note that in this report, the percentages won't add up to 100%, since any person might visit more than one city in the time period.</P> <P>This search gives me the # of unique people and returns one row, value of "1000":</P> <PRE>eventtype=visit |stats dc(person) | rename dc(person) as "# Unique People"</PRE> <P>This search shows # of unique people who visited each city and returns 25 rows:</P> <PRE>eventtype=visit |stats dc(person) by city | rename dc(person) as "# Unique People" | sort -"# Unique People"</PRE> <P>What I want is, in Search #2, instead of showing a # of people, I want to show the # of people divided by the total returned in Search #1 (which would be a percentage of the total unique people).</P> <P>Can anyone help? I've played with append, appendcols, join, appendpipe.. I'm lost.</P> Fri, 08 Mar 2013 03:12:35 GMT https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55309#M13497 bryanfe 2013-03-08T03:12:35Z https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55310#M13498 <P>UPDATED: </P> <P><CODE>eventtype=visit | stats count by city person | eventstats dc(person) as totalPeople | stats dc(person) as visitors last(totalPeople) as totalPeople by city | eval percentage=100*(visitors/totalPeople) | table city percentage</CODE></P> Fri, 08 Mar 2013 05:59:41 GMT https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55310#M13498 sideview 2013-03-08T05:59:41Z https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55311#M13499 <P>Thank you. I tried this, but the "totalPeople" column ends up empty. Not sure why.</P> Fri, 08 Mar 2013 06:22:57 GMT https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55311#M13499 bryanfe 2013-03-08T06:22:57Z https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55312#M13500 <P>totalPeople ends up empty because the first stats drops the person field, you need to swap things around a bit.</P> <PRE><CODE>... | eventstats dc(person) as totalPeople | stats dc(person) as uniquePeople values(totalPeople) as totalPeople by city | eval percentage = 100*(uniquePeople/totalPeople) </CODE></PRE> Fri, 08 Mar 2013 08:31:54 GMT https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55312#M13500 martin_mueller 2013-03-08T08:31:54Z https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55313#M13501 <P>Oops. I see the problem. I'll update.</P> Fri, 08 Mar 2013 18:12:36 GMT https://community.splunk.com/t5/Splunk-Search/Combining-quot-stats-dc-x-quot-with-quot-stats-dc-x-by-y-quot-in/m-p/55313#M13501 sideview 2013-03-08T18:12:36Z