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