Handy MySQL queries for Asterisk CDR database

Assumption:

Server: MySQL
Database name : asteriskcdr
Table name holding Call Details Record: cdr




Throughout the examples I will be extracting calldate,src,clid and dst field from 'cdr' table
Date format: YYYY-MM-DD  : calldate field in cdr table

Date1: 2011-01-01
Date2: 2011-01-02
Source number: Caller number : src field in cdr table; clid field stores callerID of caller.

Destination number: Callee number : dst field in cdr table


Date Based Search: 


Example1: Retrieving data from Date1

mysql > select calldate,src,clid,dst
              from cdr
              where DATA(calldate)='2011-01-01';


If you want to save the data to some file you can run following query:


mysql > select calldate,src,clid,dst
              from cdr
              where DATA(calldate)='2011-01-01'
              into outfile '/tmp/myfile';


Example2: Retrieving data from Date1 to Date2


mysql> select calldate,clid,dst
              from cdr
              where DATE(calldate) between '2011-01-01' and '2011-01-02';


Date and Destination Number based Search:

Example3: Retrieving data from Date1 to Date2 where destination number (callee number) is '21132'

mysql> select calldate,src,clid,dst
             from cdr
             where DATE(calldate) between '2011-01-01' and '2011-01-20'
                    and dst='21132';

Example4: Retrieving data from Date1 to Date2 where you are interested in multiple destination number (called number) e.g  '21132', '25962','20002'

mysql> select calldate,src,clid,dst
             from cdr
             where DATE(calldate) between '2011-01-01' and '2011-01-20'
                         and dst in ('21132','25962');


Example5: Use the power of REGEXP (Regular Expression): Retrieving data from Date1 to Date2 where you are interested in destination with pattern '259'


mysql> select calldate,src,clid,dst
             from cdr
             where DATE(calldate) between '2011-01-01' and '2011-01-20'
             and dst regexp '259';



Date and Source Number based Search: 


Example6: Retrieving data from Date1 to Date2 where source number (caller number) is '21132'

mysql> select calldate,src,clid,dst
             from cdr
             where DATE(calldate) between '2011-01-01' and '2011-01-20'
                    and src='21132';

Example7: Retrieving data from Date1 to Date2 where you are interested in multiple source number (callee number) e.g  '21132', '25962','20002'

mysql> select calldate,src,clid,dst
             from cdr
             where DATE(calldate) between '2011-01-01' and '2011-01-20'
                         and src in ('21132','25962');


Example8: Use the power of REGEXP (Regular Expression): Retrieving data from Date1 to Date2 where you are interested in caller number with pattern '259'


mysql> select calldate,src,clid,dst
             from cdr
             where DATE(calldate) between '2011-01-01' and '2011-01-20'
             and src regexp '259';


I hope this helps you. Good Luck.


(I have re-posted my article in my another blog for MySQL)