Finding Events That Occur A Minimum Number of Times per Month

[lboro_garyp]

Hi folks, I'm analysing Cisco CallManager telephone call details records that have  been ingested to Splunk. I need to find the extensions that make and receive a minimum number of external calls per month, every month, in the last calendar year to evaluate who should continue to receive PSTN telephone service.

I've managed to identify external calls, classify their callType (in a new field) as Incoming or Outgoing, and create a new field in each event called "activeNumber" that represents the internal number making or receiving the call.

Using

| chart count by activeNumber date_month

I can see a chart of calls per number per month.

Using

| stats count by date_month activeNumber | where count > 10

 I can see the same data, but only where the count for any given month is greater than 10.

I'm stuck where to go next, though, and I suspect this may not have been entirely the correct route to take. I need to find the activeNumber values that appear at least 10 times *every* month within the search period (12 months, for example). I toyed with the idea of concatenating the month and the activeNumber into a new field in each event that made the activeNumber's appearance in each month unique, but, again, didn't know where to go from there. My other idea was to make a list of activeNumber values for each month and then compare the lists, but wasn't sure how to do that. I suspect that a subsearch may be necessary.

Does anyone have an idea how I'd go about this?

[lboro_garyp]

Thanks for the replies, both of you!

I was, indeed, close. I didn't realise you could continue to process the output of a stats command further, so @richgalloway's solution is pretty obvious when you grok that.

@ITWhisperer, I'd not used eventstats before, so this was a good one to learn. Your code outputs a line for each activeNumber per month, though. So where Rich's outputs a line for every activeNumber that is active in all the months, yours output a line per month per activeNumber. So the overall number of activeNumbers, multiplied by the number of months searched for.

Both answers are great, though, I understand how they work and why they give different values. Thanks both again!

[ITWhisperer]

Yes, it depends on whether you want to keep all the information created by your stats command or roll it up

[richgalloway · Accepted solution]

I think you're close.  Building on your stats command, another stats will show the activeNumbers with counts greater than 10 for 12 months.

| stats count by date_month activeNumber | where count > 10
| stats count by activeNumber | where count >= 12

[ITWhisperer]

Try something like this

| stats count by date_month activeNumber | where count > 10
| eventstats count as active_months by actveNumber
| eventstats dc(date_month) as all_months
| where active_months == all_months