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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...