Splunk Search

How to stats count by unique value in a single field

DEAD_BEEF
Builder

I've been racking my brain on this and I know it's close, but I just cannot figure out the last part.

I'm trying to make a table that looks at all of our security tools for specific dates and displays:

  • the total log hits by day (working)
  • lists each tool and its breakout for that day (not working)

It's the second bullet that I'm having trouble with.

My search:

index=* month=august (day=1 OR day=2) | stats count AS "Total Logs", values(index) by day,month| rename values(index) AS "Tool" | table month, day, "Total Logs", Tool

The output is currently like this:

month     day     Total Logs     Tool
august     1       1234567       McAfee
                                 Bit9
                                 Oracle
august     2       9876543       McAfee
                                 Bit9
                                 Oracle

What I would like is

    month     day     Total Logs     Tool      Tool Count
    august     1       1234567       McAfee    542153
                                     Bit9      424124
                                     Oracle    12345
    august     2       9876543       McAfee    721482
                                     Bit9      145273
                                     Oracle    15739

How do I get it to count the number of each tool and display it as the last column?

0 Karma
1 Solution

twinspop
Influencer

Use another stats command with an eventstats thrown in for extra spice:

index=* month=august (day=1 OR day=2) | stats count as Logs by index day month |  eventstats sum(Logs) as TotalToolLogs by index day month | stats sum(Logs)  AS "Total Logs", list(index) as Tool list(TotalToolLogs) as "Tool Count" by month, day

The first stats does not do any multivalue trickery. The eventstats gets you the totals we'll be referring to briefly, the 2nd stats command uses list() which does not dedup and presents the lists in the order seen.

View solution in original post

twinspop
Influencer

Use another stats command with an eventstats thrown in for extra spice:

index=* month=august (day=1 OR day=2) | stats count as Logs by index day month |  eventstats sum(Logs) as TotalToolLogs by index day month | stats sum(Logs)  AS "Total Logs", list(index) as Tool list(TotalToolLogs) as "Tool Count" by month, day

The first stats does not do any multivalue trickery. The eventstats gets you the totals we'll be referring to briefly, the 2nd stats command uses list() which does not dedup and presents the lists in the order seen.

DEAD_BEEF
Builder

This is some Splunk sorcery! It works exactly as intended. I am not familiar with eventstats nor the chaining of these, so I will have to go through them individually to study how they work. I am also not familiar with the by multiple fields ( index day month ) and unsure how they interact when you list these in order like that. If you could provide any other relevant information to better help me understand it, I would really appreciate it! Lastly, I've never used list so I'll have to look that up as well.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...