Splunk Search

How to write a search to get a daily count of each fieldB by fieldA for 30 days?

mattbirk
Explorer

The events, each contain fieldA and fieldB (as well as other stuff). Currently, the search below works for 1 day, but I am trying to get a per day result for 30 days:

| top fieldB by fieldA

which returns the count of each fieldB per fieldA...exactly what I want...except I want per day over 30 days.

I cannot do a timechart span=1d because there is no longer a _time field due to the TOP command. Is there a better way to do this without the TOP command? Anything along the lines of a: stats count fieldB by fieldA?

Tags (4)
1 Solution

somesoni2
Revered Legend

Try something like this

your base search | bucket span=1d _time | stats count by _time,fieldA,fieldB | sort 0 _time -count | streamstats count as rank sum(count) as Total by _time, fieldA | where rank>=10  | eval percent=round(count*100/Total,2) | table _time, fieldA, fieldB,count,percent

View solution in original post

somesoni2
Revered Legend

Try something like this

your base search | bucket span=1d _time | stats count by _time,fieldA,fieldB | sort 0 _time -count | streamstats count as rank sum(count) as Total by _time, fieldA | where rank>=10  | eval percent=round(count*100/Total,2) | table _time, fieldA, fieldB,count,percent

mattbirk
Explorer

Oh my, that's awesome! Works perfectly. Not sure I necessarily understand every command in that search, but it works. Can you explain the streamstats and rank command do? I also plan on Gooling them 🙂 Thanks!!

mattbirk
Explorer

Also, to add: I had to change the where statement to where rank>=0 to get all the results...I noticed I was missing the highest count results. Looks good now...just curious.

0 Karma

sk314
Builder

somesoni has a specific set of skills, he finds the questions and then he fixes them.

0 Karma

sk314
Builder

Have you tried | bucket _time span=1d |chart count(fieldA) over _time by fieldB

and run the search over last 30 days? I am sure there are better ways to do this.

0 Karma

mattbirk
Explorer

Yea, I'm honestly not sure the best way to do this. There are a LOT of unique values for both fields, so it makes this difficult. I'll try to work with what you suggested as well...see if I can think things up from there.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...