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!

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

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...