Splunk Search

How to get the sum of count on specific field name?

jwalzerpitt
Influencer

I have the following search query:

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)

that produces:

alt text

How can I add/sum the SourceIP_country_name field to produce the following results:

Row Labels Sum of Count
United States 125703
China 100991
Ukraine 21944

Tags (3)
1 Solution

sideview
SplunkTrust
SplunkTrust

The search is a little strange, in that the second stats command will effectively be the same as | rename count as "list(count)"

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.

So... remove that second redundant stats clause.

source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | sort by -count

and to get a rollup of the whole thing by just SourceIP_country_name, the easiest way is to run:

source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_country_name | sort by -count

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 | stats sum(count) as count by SourceIP_country_name | sort by -count, giving a full expression of:

source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | sort by -count

| stats sum(count) as count by SourceIP_country_name | sort by -count

View solution in original post

sideview
SplunkTrust
SplunkTrust

The search is a little strange, in that the second stats command will effectively be the same as | rename count as "list(count)"

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.

So... remove that second redundant stats clause.

source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | sort by -count

and to get a rollup of the whole thing by just SourceIP_country_name, the easiest way is to run:

source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_country_name | sort by -count

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 | stats sum(count) as count by SourceIP_country_name | sort by -count, giving a full expression of:

source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | sort by -count

| stats sum(count) as count by SourceIP_country_name | sort by -count

jwalzerpitt
Influencer

Figured the sort out, as I did the following:

| sort by SourceIP_country_name,-list(count)

0 Karma

jwalzerpitt
Influencer

Playing around I was able to sort by CountryName using the following:

| stats count by SourceIP_country_name, SourceIP_city | stats list(count) by SourceIP_country_name, SourceIP_city | sort by -list(SourceIP_country_name)

Is it possible to sort first by alphabetically, then numerically within the list?

Thx

0 Karma

jwalzerpitt
Influencer

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:

CountryName CityName Couint
USA

Kansas City 100
Los Angeles 77
China
Beijing 45

Thx

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...