Splunk Search

stats table with individual count and a total count for two fields

RecoMark0
Path Finder

Hello,
Say I wanted to create a table with the fields State, City, City Count, and Total. If I try to use |stats values(city) as city, count by State
I get a chart that only lists cities, but does not give their individual total, only the total for State.

Is it possible to get my chart to look like this:

| STATE |      CITY       | city_count|     Total    |
|CA     |   San Jose      |    5      |     15      |
|       | Santa Clara     |    10     |             |

the last table in the answer here shows it better: https://answers.splunk.com/answers/97676/getting-counts-on-multiple-fields-while-grouping-by-one-of-... I don't have an "mcount" field, so is this still possible to get the count of each city there? My table would have srcip as State, malware_type as city, and mcount as the total count of each city.

Also, I tried using the solution using chart shown here: https://answers.splunk.com/answers/215808/how-to-get-stats-count-results-broken-down-per-fie.html?ut...

however that table is a lot harder to follow(it lists each city as its own column).

Thank you.

0 Karma
1 Solution

somesoni2
Revered Legend

Here is how you will get the expected output

your base search | stats count by state city  | stats values(city) as city values(count) as city_count sum(count) as Total by State

View solution in original post

somesoni2
Revered Legend

Here is how you will get the expected output

your base search | stats count by state city  | stats values(city) as city values(count) as city_count sum(count) as Total by State

RecoMark0
Path Finder

Wow, you are awesome. That worked perfectly. Thank you so much!

0 Karma
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...