https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141097#M39045 <P>The search is a little strange, in that the second stats command will <EM>effectively</EM> be the same as <CODE>| rename count as "list(count)"</CODE></P> <P>To explain why, coming out of the first stats command, stats will guarantee that each row is a unique combination of SourceIP_city and SourceIP_country. Therefore for each combination of those two fields there will only be one count. Therefore when the second stats comes along and says "list all the counts for every combination", there will be exactly one. In other words normally list(foo) would give you a multivalued-field but in this case it never will. Each row will have only a single value for the count.</P> <P>So... remove that second redundant stats clause. </P> <PRE><CODE>source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | sort by -count </CODE></PRE> <P>and to get a rollup of the whole thing by just SourceIP_country_name, the easiest way is to run: </P> <PRE><CODE>source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_country_name | sort by -count </CODE></PRE> <P>However, if for some reason you want to leave the stats command as it is in your base search, you could also just tack on <CODE>| stats sum(count) as count by SourceIP_country_name | sort by -count</CODE>, giving a full expression of: </P> <PRE><CODE>source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | sort by -count </CODE></PRE> <P>| stats sum(count) as count by SourceIP_country_name | sort by -count</P> Mon, 28 Sep 2020 18:53:12 GMT sideview 2020-09-28T18:53:12Z https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141096#M39044 <P>I have the following search query:</P> <P>source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | stats list(count) by SourceIP_city, SourceIP_country_name | sort by -list(count)</P> <P>that produces:</P> <P><IMG src="https://community.splunk.com/storage/temp/28625-capture.png" alt="alt text" /></P> <P>How can I add/sum the SourceIP_country_name field to produce the following results:</P> <P>Row Labels Sum of Count<BR /> United States 125703<BR /> China 100991<BR /> Ukraine 21944</P> Mon, 28 Sep 2020 18:56:00 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141096#M39044 jwalzerpitt 2020-09-28T18:56:00Z https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141097#M39045 <P>The search is a little strange, in that the second stats command will <EM>effectively</EM> be the same as <CODE>| rename count as "list(count)"</CODE></P> <P>To explain why, coming out of the first stats command, stats will guarantee that each row is a unique combination of SourceIP_city and SourceIP_country. Therefore for each combination of those two fields there will only be one count. Therefore when the second stats comes along and says "list all the counts for every combination", there will be exactly one. In other words normally list(foo) would give you a multivalued-field but in this case it never will. Each row will have only a single value for the count.</P> <P>So... remove that second redundant stats clause. </P> <PRE><CODE>source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | sort by -count </CODE></PRE> <P>and to get a rollup of the whole thing by just SourceIP_country_name, the easiest way is to run: </P> <PRE><CODE>source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_country_name | sort by -count </CODE></PRE> <P>However, if for some reason you want to leave the stats command as it is in your base search, you could also just tack on <CODE>| stats sum(count) as count by SourceIP_country_name | sort by -count</CODE>, giving a full expression of: </P> <PRE><CODE>source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | sort by -count </CODE></PRE> <P>| stats sum(count) as count by SourceIP_country_name | sort by -count</P> Mon, 28 Sep 2020 18:53:12 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141097#M39045 sideview 2020-09-28T18:53:12Z https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141098#M39046 <P>Sorry for the delay in replying. Thx for the information, and the clarification on the search. How would I modify the search to show the Source_IP_country_name and then each SourceIP_city with its corresponding count? So we'd have:</P> <P>CountryName CityName Couint <BR /> USA<BR /><BR /> Kansas City 100<BR /> Los Angeles 77<BR /> China<BR /> Beijing 45</P> <P>Thx</P> Mon, 28 Sep 2020 18:58:27 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141098#M39046 jwalzerpitt 2020-09-28T18:58:27Z https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141099#M39047 <P>Playing around I was able to sort by CountryName using the following:</P> <P>| stats count by SourceIP_country_name, SourceIP_city | stats list(count) by SourceIP_country_name, SourceIP_city | sort by -list(SourceIP_country_name)</P> <P>Is it possible to sort first by alphabetically, then numerically within the list?</P> <P>Thx</P> Mon, 28 Sep 2020 18:58:30 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141099#M39047 jwalzerpitt 2020-09-28T18:58:30Z https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141100#M39048 <P>Figured the sort out, as I did the following:</P> <P>| sort by SourceIP_country_name,-list(count)</P> Mon, 28 Sep 2020 18:58:33 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-the-sum-of-count-on-specific-field-name/m-p/141100#M39048 jwalzerpitt 2020-09-28T18:58:33Z