Find and Block Called Numbers That Never Answer

TransNexus customers who manage dialer traffic often call numbers repeatedly that will never complete. The telephone numbers may be invalid or perhaps they are valid numbers that are not in service. Whatever the reason, each repeated call attempt wastes network resources and creates a CDR processing burden.

To solve this problem, TransNexus users can use the following query of the REPORTABLECDR table to identify calls to called numbers that were not successful after two or more call attempts. This query will generate a list of called numbers that can be blocked.

  • Query for MySQL.

    select distinct CalledNumberAfterRouting from REPORTABLECDR where O_CallStartTime > 'yyyy-mm-ddThh:mm:ssZ' and O_CallStartTime < 'yyyy-mm-ddT00:00:00Z' and O_Total > 1 and O_Total=O_Count and SuccessfulCall='no';

    There are two (yyyy-mm-dd and hh:mm:ss), which will be replaced as the required start and end time, respectively.

    For example, use the following query to search from 2014-04-21 00:00:00 to 2014-04-02 00:00:00 on a MySQL database:

    mysql> select distinct CalledNumberAfterRouting from REPORTABLECDR where O_CallStartTime > '2014-04-21T00:00:00Z' and O_CallStartTime < '2014-04-22T00:00:00Z' and O_Total > 1 and O_Total=O_Count and SuccessfulCall='no';

    Below is an example of the output.

    +++++++++++++++++++++++++++++++++++++++++
| CalledNumberAfterRouting |
+++++++++++++++++++++++++++++++++++++++++
| 14045266060 |
+++++++++++++++++++++++++++++++++++++++++
1 row in set (9.98 sec)

    Note if you limit the list to called number that failed on three or more attempts, change “O_Total>1” to “O_Total>2” in the query statement.

  • Query for Oracle

    The following is an example of the query on an Oracle database.

    SQL> select O_CallStartTime, CalledNumberAfterRouting from REPORTABLECDR where O_CallStartTime > '2014-04-21T00:00:00Z' and O_CallStartTime < '2014-04-22T00:00:00Z' and O_Total > 1 and O_Total=O_Count and SuccessfulCall='no'

How to Block Calls to Known Bad Numbers

There are two ways the list of called numbers can be used to block calls in OSPrey.

  1. Upload the list to the Blacklist feature of the Fraud Module

  2. Configure routing number manipulation rules for the “default” Account with the list of called numbers and use the key word “block” to block calls to these numbers.