Analyzing telecommunications subscriber services

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

As a telecommunications provider, you are responsible for the availability of your service. Your primary concern is analyzing failed calls so that you can improve quality of service and not lose customers. However, your organization's business analysts are also interested in data concerning call volume and duration connected to both individual subscribers and geographic regions. They want to use that data to make pricing and marketing decisions. This guide provides a number of searches for gathering call data, from which you can create dashboards for both of these distinct use cases.

Data required

Call detail record (CDR) logs

Procedures

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.
Your typical telecommunications transactions may include more than four steps, and 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.

► Stage 1: Search and investigation

Call failure statistics

This search provides insight into the failures that your network experiences. You might be able to operationalize such anomalies in the form of an alert or action.

This search requires CDR logs with disposition and error fields, as well as a lookup with a mapping of the disposition codes to a potentially more readable message. Then it calculates and displays the number of occurrences of anomalies by disposition.

| sourcetype <call detail record logs> 
| lookup <cdr disposition mapping> disposition 
| search disposition="FAILED" OR disposition="CONGESTED" 
| stats count values(description) AS Description BY disposition 
| rename disposition AS Disposition count AS Count 
| table Description Disposition Count 
| eval Description=if(match(Disposition,"CONGESTED"),"Route Error",Description) 
| sort - Count

Failed calls with enriched error information

This search finds failed calls and provides more insight into why they didn't complete successfully. The results might enable you to more effectively troubleshoot and resolve ongoing issues.

This example utilizes CDR logs to look for failed calls. The failed call data is then enriched with disposition and error messages, as well as a lookup for the country. Finally, it formats the data in report form.

| sourcetype <call detail record logs> 
| eval _time = start 
| lookup <cdr disposition mapping> disposition 
| search disposition=FAILED OR disposition=CONGESTED 
| eval Description=if(match(disposition,"CONGESTED"),"Route Error",description) 
| rex field=src "(?<srcCountry>\d+)(?=\d{10})" 
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})" 
| lookup <cdr country codes> phoneCode AS dstCountry OUTPUTNEW countryName AS dstCountryName ISO2 AS dstCountryCode 
| table _time src dst dstCountryCode Description disposition 
| rename _time AS Time src AS "Source #" dst AS "Destination #" disposition AS Disposition dstCountryCode AS "Destination Country"

General call disposition details and information

This search provides insight into your calls and insight to potential technical issues.

This example utilizes CDR logs and a lookup that maps error codes to a potentially more readable format. It then runs the results through data analysis and formats the results for a potentially more readable report.

| sourcetype <call detail record logs> 
| lookup <cdr disposition mapping> disposition 
| stats count values(description) AS Description BY disposition 
| rename disposition AS Disposition count AS Count 
| table Description Disposition Count 
| eval Description=if(match(Disposition,"CONGESTED"),"Route Error",Description) 
| sort - Count

► Stage 2: Proactive monitoring

Failed calls by destination and outbound network gateway

This search can provide insight into where calls fail and the path through which the system routes the call. Routing issues can often be traced back to the underlying transit network, but with this information from your CDR logs, you potentially gain operational insight into where calls encounter issues so you can proactively address such issues.

For this search, the CDR must have the destination/callee, along with failure status and the path used. From there, the search uses statistics to correlate fields within the event and then formats the results into a potentially more readable report.

| sourcetype <call detail record logs> 
| eval _time = start 
| lookup <cdr disposition mapping> disposition 
| search disposition=FAILED OR disposition=CONGESTED 
| eval Description=if(match(disposition,"CONGESTED"),"Route Error",description) 
| rex field=src "(?<srcCountry>\d+)(?=\d{10})" 
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})" 
| lookup <cdr country codes> phoneCode AS dstCountry OUTPUTNEW countryName AS dstCountryName ISO2 AS dstCountryCode 
| stats count values(dst) AS Destination values(disposition) AS Disposition values(Description) AS Description values(_time) AS Time BY partyBiD dst 
| rename partyBiD AS "Routed Path" 
| table Time Destination Description "Routed Path" count

Longest/shortest call durations by destination

This examples shows our longest call durations by destination and by the dialing subscriber. This search can provide insight into who your top customers are and from where they are calling. Such information can be used to create targeting marketing or campaigns or to generate time-series reports displaying call routing and associated costs.

For this search, the CDR data source must have both a source/caller and destination/callee. In addition to this, a country code is required in order to find the dialed party. This example extracts a country code from the dialed number, then uses a lookup to match the code to a country name. From there, the stats command generates the dataset and sorts against time. Choose a specific visualization, or reformat the data into a table.

To sort by shortest duration instead of longest, remove the minus sign in the final sort line.

| sourcetype <call detail record logs> 
| eval _time = start 
| lookup <cdr disposition mapping> disposition 
| search disposition="ANSWERED" OR disposition="BUSY" 
| rex field=src "(?<srcCountry>\d+)(?=\d{10})" 
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})" 
| lookup <cdr country codes> phoneCode AS dstCountry OUTPUTNEW countryName AS dstCountryName ISO2 AS dstCountryCode 
| fields src dst dstCountryName duration 
| stats count list(dst) AS dst list(dstCountryName) AS dstCountryName BY duration src 
| rename duration AS "Call Duration" src AS "Caller" dst AS "Called Number" dstCountryName AS "Destination Country" 
| sort - "Call Duration"

► Stage 3: Operational visibility

Countries with the highest/lowest call volumes

This search shows the countries with the highest call volumes and gives insight into where your customers make the most calls. This information then might be used to negotiate lower rates for such traffic or perhaps raise your own prices.

For this search, the CDR data source must have both a source/caller and destination/callee. In addition, a country code is required in order to find the dialed party. This example extracts a country code from the dialed number, then uses a lookup to match the code to a country name. From there, it uses stats to pull the top 10 countries. You can also use the top command to pull the data into a visualization, or you can reformat the data into a table.

| sourcetype <call detail record logs> 
| eval _time = start 
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})" 
| lookup <cdr country codes> phoneCode AS dstCountry OUTPUTNEW countryName AS dstCountryName 
| stats count BY dstCountryName 
| sort - count 
| head 10

To search for lowest call volume instead of highest, use this search.

| sourcetype <call detail record logs> 
| eval _time = start 
| lookup <cdr disposition mapping> disposition 
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})" 
| lookup <cdr country codes> phoneCode AS dstCountry OUTPUTNEW countryName AS dstCountryName ISO2 AS dstCountryCode 
| rare limit=10 dstCountryName

Subscribers with the highest outbound call volume

This search provides insight into who your top customers are and where they call from. This information can be used to create targeted marketing or campaigns or to generate time-series reports that display call routing and associated costs.

For this search, the CDR data source must have both a source/caller and destination/callee. In addition, a country code is required in order to find the dialed party. This example extracts a country code from the dialed number, then uses a lookup to match the code to a country name. From there, it uses stats to calculate total call per destination and then again to calculate total call volume by the subscriber's number.

| sourcetype <call detail record logs> 
| eval _time = start 
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})" 
| lookup <cdr country codes> phoneCode AS dstCountry OUTPUTNEW countryName AS dstCountryName ISO2 AS dstCountryCode 
| stats count count(dstCountryName) AS dcount values(dstCountryName) AS CountryName BY src dstCountryName 
| stats sum(count) as count list(dcount) AS dcount values(CountryName) AS dstCountryName BY src 
| sort - count 
| table src dstCountryName dcount count 
| rename src AS "Subscriber" count AS "Total Call Count" dcount AS "Destination Calls" dstCountryName AS "Dialed Countries"

Successful call statistics by geography

This search generates a visual representation to give insight into the locations that your customers call the most. This view can allow you to spot abnormalities in expected call behavior. This information can also be used to negotiate lower rates for voice traffic, or to increase prices.

For this search, the CDR data source must have both a source/caller and destination/callee. In addition, a country code is required in order to find the dialed party. This example extracts a country code from the dialed number, then uses a lookup to match the code to a country name. You can also use the top command to pull the data into a visualization, or you can reformat the data into a table.

| sourcetype <call detail record logs> 
| eval _time = start 
| lookup cdr_disposition_mapping disposition 
| search disposition="ANSWERED" OR disposition="BUSY" 
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})" 
| lookup cdr_country_codes phoneCode AS dstCountry OUTPUTNEW countryName AS dstCountryName ISO2 AS dstCountryCode 
| stats count BY dstCountryName 
| geom geo_countries allFeatures=true featureIdfield=dstCountryName

Total call minutes by subscriber

This search can provide insight into where your users call, as well as the total duration of all their calls. This information could be used for targeted marketing campaigns, billing reconciliation, and capacity and service forecasting.

For this search, the CDR data source must have both a source/caller and destination/callee. In addition, a country code is required in order to find the dialed party. This example extracts a country code from the dialed number, then uses a lookup to match the code to a country name. From there, the data is processed with the stats command, and uses nomv to make the fields within the dialed countries display a single value.

| sourcetype <call detail record logs> 
| eval _time = start 
| lookup cdr_disposition_mapping disposition 
| search disposition="ANSWERED" OR billable > 0 
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})" 
| lookup cdr_country_codes phoneCode AS dstCountry OUTPUTNEW countryName AS dstCountryName ISO2 AS dstCountryCode 
| fields _time accountcode src billable dstCountryCode 
| convert dur2sec(billable) AS calculatedBillableS 
| stats values(accountcode) AS Account values(dstCountryCode) AS CalledCountries sum(eval(round((calculatedBillableS/60),0))) AS BillableMinutes by src 
| nomv CalledCountries 
| rename src AS "Caller" BillableMinutes AS "Billable Minutes" 
| table Account Caller CalledCountries "Billable Minutes" 
| sort - "Billable Minutes"

► Stage 4: Business insights

Failed call metrics by geographic location

This search finds failed calls, which are then visualized on a geomap. This search and visualization gives a more readable representation of where the failed calls occur.

This search requires CDR logs with a disposition status. It then extracts a destination country code from the dialed number, and enriches this with a country lookup to map to a country name. It then calculates an aggregate count by country, and uses geomap with the specific geo_countries maps.

| sourcetype <call detail record logs> 
| eval _time = start 
| lookup cdr_disposition_mapping disposition 
| search disposition=FAILED OR disposition=CONGESTED 
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})" 
| lookup cdr_country_codes phoneCode AS dstCountry OUTPUTNEW countryName AS dstCountryName 
| stats count NY dstCountryName  
| geom geo_countries allFeatures=true featureIdfield=dstCountryName

Estimated generated call revenue

This search associates a monetary value to each successful call and gives an estimated total daily revenue stream. Having this information could assist in informed decisions about capacity, billing rates, services, and marketing campaigns.

This search requires CDR/PBX logs that contain, at a minimum, the log of successful outbound calls. This data is then enriched based on the destination/callee, and current billable rates are added to the results. It then calculates the call duration. Next, it summates the total cost and reports on that. Finally, it aggregates and reports the total cost.

| sourcetype <call detail record logs> 
| eval _time = start 
| lookup cdr_disposition_mapping disposition 
| search disposition="ANSWERED" OR billable > 0 
| rex field=dst "(?<dstCountry>\d+)(?=\d{10})" 
| lookup cdr_country_codes phoneCode AS dstCountry OUTPUTNEW countryName as dstCountryName ISO2 AS dstCountryCode rateFirstMinute rateMinute 
| fields _time accountcode src billable dstCountryCode rate* 
| convert dur2sec(billable) AS calculatedBillableS 
| eval billableMinutes = calculatedBillableS / 60 
| eval billableMinutes = ceiling(billableMinutes) 
| eval billableMinusOne = billableMinutes - 1 
| eval totalBillable = rateMinute + (billableMinusOne * rateMinute) 
| stats sum(totalBillable) AS CallRevenue

Next steps

You might be interested in the following additional telecommunications use cases:

​Data required

Procedures

Next steps

Data required