Splunk Search

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

jwalzerpitt
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

Runals
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

jtrucks
Splunk Employee
Splunk Employee

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...