Splunk Search

How to change existing stats search output for aggregate counts based on a field?

Motivator

Jesse,

Wondering if I could throw another question at you... I have the following 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)

which gets me the following:
alt text

How can I add the SourceIP_country_name fields so I would see the following:

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

Thx

Tags (1)
0 Karma

Motivator

Perhaps I'm missing something but I don't think you need your second stats.

At any rate what you are looking for is to put | eventstats at the end of your search like

source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | eventstats sum(count) by SourceIP_country_name

If you wanted to get crazy I always like doing something like this

source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_city, SourceIP_country_name | sort -count | stats sum(count) as Total_Events list(SourceIP_city) as City list(count) as Count by SourceIP_country_name | sort -Total_Events

The problem is if you export this to a scheduled PDF the columns for the second stats split-by are put at the bottom. If you export it to csv the mv list fields are space delimited. Looks nice in Splunk though 😃

Depending on what you are looking for you could chain a couple stats commands to act like a mini drill down. Something like

source="mysource" ImmediateAction=Block | geoip SourceIP | stats count by SourceIP_country_name SourceIP_city | stats sum(count) as count by SourceIP_country_name

That will give you JUST the data by country but if you click the country it will go a step down and show you the individual line items for "City" within that country without taking you directly to the raw data (third click will).

0 Karma

Splunk Employee
Splunk Employee

Do you want to add those as Totals to the same table, or change the existing table?

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!