CDRs in ASTPP database
By Yevgeniya Suminova on 2021-03-31
Contents
CDR tables that exist in astpp database
Introduction
In this document we will study how CDRs are stored in ASTPP database.
CDR tables that exist in astpp database
There are 3 tables that contain CDRs:
| cdrs |
| cdrs_day_by_summary |
| cdrs_staging |
cdrs table
“cdrs” table contains following fields:
+---------------------------+-----------------------------------------------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+-----------------------------------------------------------+------+-----+---------------------+-------+
| uniqueid | varchar(60) | NO | | | |
| accountid | int | YES | | 0 | |
| type | tinyint(1) | NO | | 0 | |
| sip_user | varchar(20) | NO | | | |
| callerid | varchar(120) | NO | | NULL | |
| callednum | varchar(30) | NO | | | |
| translated_dst | varchar(30) | NO | | NULL | |
| ct | int | NO | | 0 | |
| billseconds | smallint | NO | | 0 | |
| trunk_id | smallint | NO | | 0 | |
| trunkip | varchar(15) | NO | | | |
| callerip | varchar(15) | NO | | | |
| disposition | varchar(45) | NO | | | |
| callstart | datetime | NO | MUL | 1000-01-01 00:00:00 | |
| debit | decimal(20,6) | NO | | 0.000000 | |
| cost | decimal(20,6) | NO | | 0.000000 | |
| provider_id | int | NO | | 0 | |
| pricelist_id | smallint | NO | | 0 | |
| package_id | int | NO | | 0 | |
| pattern | varchar(20) | NO | | NULL | |
| notes | varchar(80) | NO | | NULL | |
| invoiceid | int | NO | | 0 | |
| rate_cost | decimal(20,6) | NO | | 0.000000 | |
| reseller_id | int | NO | | 0 | |
| reseller_code | varchar(20) | NO | | NULL | |
| reseller_code_destination | varchar(80) | YES | | NULL | |
| reseller_cost | decimal(20,6) | NO | | 0.000000 | |
| provider_code | varchar(20) | NO | | NULL | |
| provider_code_destination | varchar(80) | NO | | NULL | |
| provider_cost | decimal(20,6) | NO | | 0.000000 | |
| provider_call_cost | decimal(20,6) | NO | | NULL | |
| call_direction | enum('outbound','inbound') | NO | | NULL | |
| calltype | enum('STANDARD','DID','FREE','CALLINGCARD','FAX','LOCAL') | NO | | STANDARD | |
| billmsec | int | NO | | 0 | |
| answermsec | int | NO | | 0 | |
| waitmsec | int | NO | | 0 | |
| progress_mediamsec | int | NO | | 0 | |
| flow_billmsec | int | NO | | 0 | |
| is_recording | tinyint(1) | NO | | 1 | |
| call_request | tinyint | NO | | 0 | |
| country_id | int | NO | | 0 | |
| end_stamp | datetime | NO | | 0000-00-00 00:00:00 | |
+---------------------------+-----------------------------------------------------------+------+-----+---------------------+-------+
Here is what we see as “Customer CDRs” in admin web GUI:
Here is hat we see as “Provider CDRs” in admin web GUI:
Tests
Let’s see some interesting outputs:
mysql> select sip_user, callednum, debit, cost, rate_cost, provider_cost, provider_call_cost, callstart, end_stamp from cdrs;
Output:
+-------------+---------------+----------+----------+-----------+---------------+--------------------+---------------------+---------------------+
| sip_user | callednum | debit | cost | rate_cost | provider_cost | provider_call_cost | callstart | end_stamp |
+-------------+---------------+----------+----------+-----------+---------------+--------------------+---------------------+---------------------+
| 41219999999 | 41772664014 | 0.036700 | 0.003500 | 0.550000 | 0.052000 | 0.003500 | 2021-03-26 13:49:28 | 2021-03-26 13:49:32 |
From here we understand:
1. rate_cost = origination rate (what user pay us per minute)
2. provider_cost = termination rate (what we pay to our provider per minute)
3. debit = what we charged customer for the call
4. cost = what we pay to our provider for the call
5. provider_call_cost = what we pay to our provider for the call (so, most probably, “cost” and “provider_call_cost” should always be same)
We will now change the termination and origination rates manually for 41772 prefix and will see how CDRs table show the same data.
1. set termination rate to 0.085 (instead of 0.052)
2. set origination rate to 0.33 (instead of 0.55)
3. data in DB remains same (as it is supposed to be)
mysql> select sip_user, callednum, debit, cost, rate_cost, provider_cost, provider_call_cost, callstart, end_stamp from cdrs;
+-------------+---------------+----------+----------+-----------+---------------+--------------------+---------------------+---------------------+
| sip_user | callednum | debit | cost | rate_cost | provider_cost | provider_call_cost | callstart | end_stamp |
+-------------+---------------+----------+----------+-----------+---------------+--------------------+---------------------+---------------------+
| 41219999999 | 41772664014 | 0.036700 | 0.003500 | 0.550000 | 0.052000 | 0.003500 | 2021-03-26 13:49:28 | 2021-03-26 13:49:32 |
We will now make several calls from 41219999999 to 41772664014
1. Call was sent to voicemail
2. Call was cut before it goes to voicemail = not answered
Here the “Live calls” show that it was answered while it was beeping. This is
not correct.
3. Call answered with duration of 01:01
We will now check CDRs in admin web GUI
1 second of discrepancy for the third call. The rest looks correct.
Checking that in DB:
mysql> select sip_user, callednum, debit, cost, rate_cost, provider_cost, provider_call_cost, callstart, end_stamp from cdrs;
+-------------+---------------+----------+----------+-----------+---------------+--------------------+---------------------+---------------------+
| sip_user | callednum | debit | cost | rate_cost | provider_cost | provider_call_cost | callstart | end_stamp |
+-------------+---------------+----------+----------+-----------+---------------+--------------------+---------------------+---------------------+
| 41219999999 | 41772664014 | 0.036700 | 0.003500 | 0.550000 | 0.052000 | 0.003500 | 2021-03-26 13:49:28 | 2021-03-26 13:49:32 |
| 41219999999 | 41772664014 | 0.039600 | 0.008500 | 0.396000 | 0.085000 | 0.008500 | 2021-03-31 16:43:53 | 2021-03-31 16:43:59 |
| 41219999999 | 41772664014 | 0.000000 | 0.000000 | 0.396000 | 0.085000 | 0.000000 | 2021-03-31 16:44:37 | 2021-03-31 16:44:37 |
| 41219999999 | 41772664014 | 0.409200 | 0.087800 | 0.396000 | 0.085000 | 0.087800 | 2021-03-31 16:46:29 | 2021-03-31 16:47:31 |
+-------------+---------------+----------+----------+-----------+---------------+--------------------+---------------------+---------------------+
1. The data about the call on 2021-03-26 remains same. It is how it should be, so is correct.
2. The “rate_cost” = 0.396000 and it is normal as our Rate group is set with 20% markup. (0.33 + 20% = 0.396)
3. We see that second call is not billed. This is correct.
4. We see that other two calls were billed correctly.
“cdrs_staging” table
“cdrs_staging” table contains following fields:
mysql> describe cdrs_staging;
+---------------------------+---------------------------------------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+---------------------------------------------------+------+-----+---------------------+-------+
| uniqueid | varchar(60) | NO | | | |
| accountid | int | YES | | 0 | |
| type | tinyint(1) | NO | | 0 | |
| sip_user | varchar(20) | NO | | | |
| callerid | varchar(120) | NO | | NULL | |
| callednum | varchar(30) | NO | | | |
| translated_dst | varchar(30) | NO | | NULL | |
| ct | int | NO | | 0 | |
| billseconds | smallint | NO | | 0 | |
| trunk_id | smallint | NO | | 0 | |
| trunkip | varchar(15) | NO | | | |
| callerip | varchar(15) | NO | | | |
| disposition | varchar(45) | NO | | | |
| callstart | datetime | NO | | 0000-00-00 00:00:00 | |
| debit | decimal(20,6) | NO | | 0.000000 | |
| cost | decimal(20,6) | NO | | 0.000000 | |
| provider_id | int | NO | | 0 | |
| pricelist_id | smallint | NO | | 0 | |
| package_id | int | NO | | 0 | |
| pattern | varchar(20) | NO | | NULL | |
| notes | varchar(80) | NO | | NULL | |
| invoiceid | int | NO | | 0 | |
| rate_cost | decimal(20,6) | NO | | 0.000000 | |
| reseller_id | int | NO | | 0 | |
| reseller_code | varchar(20) | NO | | NULL | |
| reseller_code_destination | varchar(80) | YES | | NULL | |
| reseller_cost | decimal(20,6) | NO | | 0.000000 | |
| provider_code | varchar(20) | NO | | NULL | |
| provider_code_destination | varchar(80) | NO | | NULL | |
| provider_cost | decimal(20,6) | NO | | 0.000000 | |
| provider_call_cost | decimal(20,6) | NO | | NULL | |
| call_direction | enum('outbound','inbound') | NO | | NULL | |
| calltype | enum('STANDARD','DID','FREE','CALLINGCARD','FAX') | NO | | STANDARD | |
| billmsec | int | NO | | 0 | |
| answermsec | int | NO | | 0 | |
| waitmsec | int | NO | | 0 | |
| progress_mediamsec | int | NO | | 0 | |
| flow_billmsec | int | NO | | 0 | |
| is_recording | tinyint(1) | NO | | 1 | |
| call_request | tinyint | NO | | 0 | |
| country_id | int | NO | | 0 | |
| end_stamp | datetime | NO | | 0000-00-00 00:00:00 | |
+---------------------------+---------------------------------------------------+------+-----+---------------------+-------+
Fields are exactly same as in “cdrs” table.
But it seams that this table keeps only current date data.
1. “cdrs” output:
mysql> select callstart from cdrs;
+---------------------+
| callstart |
+---------------------+
| 2021-03-26 13:49:28 |
| 2021-03-26 13:49:57 |
| 2021-03-26 13:50:39 |
| 2021-03-26 13:50:58 |
| 2021-03-26 13:51:16 |
| 2021-03-26 13:53:09 |
| 2021-03-26 16:11:36 |
| 2021-03-26 16:12:40 |
| 2021-03-26 16:12:55 |
| 2021-03-26 16:13:24 |
| 2021-03-29 19:17:55 |
| 2021-03-29 19:18:27 |
| 2021-03-29 19:18:34 |
| 2021-03-29 19:18:41 |
| 2021-03-29 19:19:06 |
| 2021-03-29 19:19:28 |
| 2021-03-29 19:20:13 |
| 2021-03-29 19:21:00 |
| 2021-03-29 19:21:07 |
| 2021-03-29 19:22:13 |
| 2021-03-29 20:04:10 |
| 2021-03-29 20:04:32 |
| 2021-03-30 11:50:03 |
etc……
2. “cdrs_staging” output:
mysql> select callstart from cdrs_staging;
+---------------------+
| callstart |
+---------------------+
| 2021-03-31 16:43:53 |
| 2021-03-31 16:44:37 |
| 2021-03-31 16:46:29 |
+---------------------+
“cdrs_day_by_summary” table
This table contains following fields
mysql> describe cdrs_day_by_summary;
+---------------------+---------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------------------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| account_id | int | NO | MUL | NULL | |
| reseller_id | int | NO | | NULL | |
| type | tinyint | NO | | NULL | |
| country_id | int | NO | | NULL | |
| billseconds | int | NO | | NULL | |
| mcd | int | NO | | NULL | |
| total_calls | int | NO | | NULL | |
| debit | decimal(10,5) | NO | | NULL | |
| cost | decimal(10,5) | NO | | NULL | |
| total_answered_call | int | NO | | NULL | |
| total_fail_call | int | NO | | NULL | |
| unique_date | varchar(50) | NO | | 00000000 | |
| calldate | datetime | NO | | 0000-00-00 00:00:00 | |
+---------------------+---------------+------+-----+---------------------+----------------+
We have checked the dates in “unique_date” column. We see that we have records from 2021-03-18 (the day when the server was reinstalled). At that time our settings for CDRs were not set correctly and the data until 2021-03-26 is lost.
mysql> select * from cdrs_day_by_summary where unique_date = 20210318;
+----+------------+-------------+------+------------+-------------+-----+-------------+---------+---------+---------------------+-----------------+-------------+---------------------+
| id | account_id | reseller_id | type | country_id | billseconds | mcd | total_calls | debit | cost | total_answered_call | total_fail_call | unique_date | calldate |
+----+------------+-------------+------+------------+-------------+-----+-------------+---------+---------+---------------------+-----------------+-------------+--------------------
| 1 | 13 | 0 | 0 | 0 | 46 | 25 | 3 | 0.00000 | 0.00000 | 2 | 1 | 20210318 | 2021-03-18 00:00:00 |
| 2 | 15 | 0 | 0 | 0 | 46 | 25 | 3 | 0.00000 | 0.00000 | 2 | 1 | 20210318 | 2021-03-18 00:00:00 |
+----+------------+-------------+------+------------+-------------+-----+-------------+---------+---------+---------------------+-----------------+-------------+--------------------
End
***
© 4z.com