Splunk Search
Highlighted

How to stats count by unique value in a single field

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
Highlighted

Re: How to stats count by unique value in a single field

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

Highlighted

Re: How to stats count by unique value in a single field

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