Return raw results for a search filtered by event counts

[JdeFalconr]

I am searching Cisco calling records via the Splunk Cisco CDR app, examining the calling and called party numbers and their counts. I need to create a search that returns raw results only where a given calling party number shows up X or more times; I want to eliminate any results where its calling party number does not show up above that threshold. I unfortunately have had great difficulty in generating such a search and being able to export the raw events to CSV. In order to generate a table of counts by calling party number (only part of what I need) I use this:

`cdr_events`

earliest=-4h latest=now | stats min(_time) as _time values(callId) as callId values(callingPartyNumber) as callingPartyNumber by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID 
| search   | stats  dc(callId)  as CallCount by callingPartyNumber | where CallCount > 500

Unfortunately I do not have confidence that search's raw results (the "Events" tab on the search screen) only correspond to the events identified by the "where" clause. Moreover if I try to export results to CSV only the table of stats is returned.

The search is already scheduled to email out the table of stats. In a perfect world, though, I would have that table of stats emailed and the raw results - filtered in the manner I described above - included in the email as an attachment. At this point, however, I would gladly take just the raw log lines for all instances where the count of callingPartyNumber exceeds value X that I designate.

[Richfez]

There are two answers here, so please pick the one you like best.

Also, keep in mind one really important distinction. The "raw records" are not calls - the "raw records" are call legs. You could want that, but I think you really want a view like "Browse Calls" gives you. I give you both...

And lastly - this is as much a tutorial as it is an answer - there are extra steps in here so that hopefully you can repeat this with other thresholds or for other similar needs! I like to teach people how to fish... 🙂

OK, so with that out of the way.

If you really want "raw" data -
What you could do is use a search similar to the one you have above as a subsearch, and search in the cdr data for those calls:

`cdr_events` 
    [ search `cdr_events` 
    | stats list(callId) as callId list(callingPartyNumber) as callingPartyNumber by 
        globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID 
    | chart dc(callId) AS callIds over callingPartyNumber  | where callIds > 500  | fields callingPartyNumber ]

I got to this by starting from General Report, doing a "distinct count of" callId over callingPartyNumber, then clicking on the right to see raw search syntax. That gave me most of the subsearch, I just added the | where ... | fields .... Then I wrapped them in square brackets, added a search in front of the macro, then stuck another copy of the macro that pulls out CDR events outside that.

What that means then is that I'm going to run the subsearch, which will return the callingPartyNumbers that match your criteria. That subsearch returns those back into the main search to filter it down. You can read up on subsearches in the Splunk docs if you aren't real hip with them yet.

BUT. I think you want a prettier view of "Calls" not just "gibberish. Sorry, did I say "gibberish" out loud in referring to the Cisco CDR data? My apologies!

So, let's try this again.

Go to Browse Calls and put in a number - any number that matches something you have will work, because we're going to replace everywhere we see that with our subsearch we created above. Then click the 'See full search syntax'.

The first line starts ---- oh lookit there, it's already a subsearch!

`cdr_and_cmr_events` [search `cdr_events` ( callingPartyNumber::12345 OR originalCalledPartyNumber::12345  OR finalCalledPartyNumber::12345  ) | stats count by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID | fields - count] ...

So, let's take that subsearch and just jam in our own (see it up there near the top?)...

`cdr_and_cmr_events` [ search `cdr_events` 
     | stats list(callId) as callId list(callingPartyNumber) as callingPartyNumber 
        by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID 
     | chart dc(callId) AS callIds over callingPartyNumber | where callIds > 500 | fields callingPartyNumber]
| sort 0 + dateTimeConnect | eval durationStr=tostring(duration,"duration") 
| stats list(MLQK) as MLQK min(_time) as _time list(callingPartyNumber) as callingPartyNumber list(cause_description) as cause_description list(destDeviceName) as destDeviceName list(dest_device_type) as dest_device_type max(_time) as detailLatest list(device_type) as device_type list(duration) as duration list(durationStr) as durationStr list(finalCalledPartyNumber) as finalCalledPartyNumber sum(legs) as legs list(on_hook_party) as on_hook_party list(origDeviceName) as origDeviceName list(orig_device_type) as orig_device_type list(originalCalledPartyNumber) as originalCalledPartyNumber sum(transfers) as transfers list(type) as type by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID 
| search   ( callingPartyNumber::12345 OR originalCalledPartyNumber::12345 OR finalCalledPartyNumber::12345 )| rename durationStr as duration 
| sort 0 - _time 
| fields MLQK _time callingPartyNumber cause_description destDeviceName dest_device_type detailLatest device_type duration durationStr finalCalledPartyNumber legs on_hook_party origDeviceName orig_device_type originalCalledPartyNumber transfers type
 `id_fields`

Now, you'll see there's one MORE spot where the number I searched for shows up - about 3/4ths of the way down. So, let's do some more jamming, convert it to a subsearch in the process.

`cdr_and_cmr_events` [ search `cdr_events` 
     | stats list(callId) as callId list(callingPartyNumber) as callingPartyNumber by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID 
     | chart dc(callId) AS callIds over callingPartyNumber | where callIds > 500 | fields callingPartyNumber]
| sort 0 + dateTimeConnect | eval durationStr=tostring(duration,"duration") 
| stats list(MLQK) as MLQK min(_time) as _time list(callingPartyNumber) as callingPartyNumber list(cause_description) as cause_description list(destDeviceName) as destDeviceName list(dest_device_type) as dest_device_type max(_time) as detailLatest list(device_type) as device_type list(duration) as duration list(durationStr) as durationStr list(finalCalledPartyNumber) as finalCalledPartyNumber sum(legs) as legs list(on_hook_party) as on_hook_party list(origDeviceName) as origDeviceName list(orig_device_type) as orig_device_type list(originalCalledPartyNumber) as originalCalledPartyNumber sum(transfers) as transfers list(type) as type by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID 
| search [ search `cdr_events` 
     | stats list(callId) as callId list(callingPartyNumber) as callingPartyNumber 
        by globalCallID_callId globalCallID_callManagerId globalCallId_ClusterID 
     | chart dc(callId) AS callIds over callingPartyNumber | where callIds > 500 | fields callingPartyNumber]
| rename durationStr as duration 
| sort 0 - _time 
| fields MLQK _time callingPartyNumber cause_description destDeviceName dest_device_type detailLatest device_type duration durationStr finalCalledPartyNumber legs on_hook_party origDeviceName orig_device_type originalCalledPartyNumber transfers type
 `id_fields`

Now... this seems to work on my test data, but my test data is pretty terrible.

I hope this gets you to where you need to be. If it does not, please email in to support@sideviewapps.com and we can take a longer look at your problem!

-Rich

[renjith_nair]

@JdeFalconr ,
Have you tried using eventstats in place of stats?

e.g.

|eventstats dc(callId) as CallCount by callingPartyNumber

This will add CallCount to each event matching the callingPartyNumber and then you can filter the events using where