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