Skip to main content
 
Splunk Lantern

Total call minutes by subscriber

 

The table below explains in detail the steps of a Splunk Enterprise or Splunk Cloud Platform search to calculate the total call minutes by subscribers within a certain set of countries. For more information, review the use case monitoring key telecommunications service metrics.

Some commands, parameters, and field names in the searches below may need to be adjusted to match your environment.  In addition, to optimize the searches shown below, you should specify an index and a time range when appropriate.

Splunk recommends that customers look into using data models, report acceleration, or summary indexing when searching across hundreds of GBs of events in a single search. The searches provided here are a good starting point, but depending on your data, search time range, and other factors, more can be done to ensure that they scale appropriately.   

Splunk Search Explanation
| sourcetype=<call detail records> Search only your telephony data.
| eval _time = start Extract the timestamp and put it in a field called "start".
| lookup <cdr disposition mapping file> disposition

Enrich the search results with clearer descriptions of the dispositions.

If your lookup file does not contain the disposition column, adjust the search to match the names in your lookup.

| search disposition="ANSWERED" OR billable > 0 Search for billable call events, identified with a disposition of ANSWERED or where the billable field has a value greater than 0.
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})"

Extract the destination (dst) country code.

This example assumes that the country code is what precedes a 10 digit number.

| lookup <country code to name mapping file> phoneCode AS dstCountry OUTPUTNEW countryName as dstCountryName ISO2 AS dstCountryCode

Enrich the results by adding a country name and iso code, based on the dstCountry field extracted.

If the column names in your lookup file differ from those shown here, adjust the search to match the names in your lookup.

| fields _time accountcode src billable dstCountryCode Return only the fields shown.
| convert dur2sec(billable) AS calculatedBillableS Convert the value in the billable field to a numerical value in seconds without replacing the original values.
| stats values(accountcode) AS Account values(dstCountryCode) AS CalledCountries sum(eval(round((calculatedBillableS/60),0))) AS BillableMinutes BY src Return the values for account codes and destination countries. Calculate billable minutes based on the summated call durations rounded to the nearest minute, then sort by source.
| nomv CalledCountries Convert this multivalue CalledCountries field into single values.
| rename src AS "Caller" BillableMinutes AS "Billable Minutes" Rename the fields as shown for better readability.
| table Account Caller CalledCountries "Billable Minutes" Display the results in a table with columns in the order shown.
| sort - "Billable Minutes" Sort the results with the largest amount of billable minutes first.