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

[jwalzerpitt]

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:

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

[sideview · Accepted solution]

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]

Figured the sort out, as I did the following:

| sort by SourceIP_country_name,-list(count)

[jwalzerpitt]

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

[jwalzerpitt]

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