Splunk Search

Finding Events That Occur A Minimum Number of Times per Month

lboro_garyp
Path Finder

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?

Labels (4)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

lboro_garyp
Path Finder

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
SplunkTrust
SplunkTrust

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

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.

ITWhisperer
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...