Query Asterisk CDR in MYSQL to list inbound calls by SIP number?

Question

Is it possible to query the CDR database to list the total number of inbound calls per inbound phone number over a given period?

I would like to have the output like:

Inbound Telephone number : Count calls received
------------------------   --------------------

0123456789               : 124

098756431                : 43

0123456798               : 39

0123456788               : 14

I have the CDR database in MYSQL but looking at the data I can't seem to figure out how to identify which calls are incoming and what phone number and SIP provider they used to dial in.

I am trying to assess how often some of our direct dial numbers are used over a given period.

The columns I have to query in MYSQL:

acctid
calldate
clid
src
dst
dcontext
channel
dstchannel
lastapp
lastdata
duration
billsec
disposition
amaflags
accountcode
uniqueid
userfield
did
recordingfile
cnum
cnam
outbound_cnum
outbound_cnam
dst_cnam
import_cdr

Which look to be documented here: http://asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/asterisk-SysAdmin-SECT-1.html

However some columns which look like channel do not clearly tell me what the inbound caller called and if the call was inbound or outbound.

Had a google search, can't seem to find an answer for this.

Thanks,

Knightsbridge Knightsbridge · Accepted Answer · 2015-11-05T10:42:28

The problem was that none of the numbers were being used which is what was throwing me off.

Simply querying the DID column solves it.

SELECT 'xxxxxxxxxxx' as 'tel', count(*) as 'count' from CDR where did = 'xxxxxxxxxxx'

I then used EXCEL to create a huge union with all of the did numbers and found that this works.

--

Would still like to know how to identify inbound and outbound calls however from this list as it would be useful in the future.

Query Asterisk CDR in MYSQL to list inbound calls by SIP number?

1 Answers