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!

Now Playing: Splunk Education Summer Learning Premieres

It’s premiere season, and Splunk Education is rolling out new releases you won’t want to miss. Whether you’re ...

The Visibility Gap: Hybrid Networks and IT Services

The most forward thinking enterprises among us see their network as much more than infrastructure – it's their ...

Get Operational Insights Quickly with Natural Language on the Splunk Platform

In today’s fast-paced digital world, turning data into actionable insights is essential for success. With ...