Asterisk – Database-driven CallerID

One of the nice things Asterisk can do is manipulate Caller ID information on the fly. Since I’m too lazy to update the stored numbers within my individual cordless handsets, I use Asterisk to cheat. Asterisk looks up the incoming phone number (“08XXXXXXXX”) in a MySQL table, finds matching text (“Bob”) and passes that along to the handsets for display.

This assumes you have a MySQL database on the same box as asterisk, with a username of asterisk, password of mypassword, your database is called asterisk, and your final internal destination is SIP/myphone. (I’m inventive, I am… ;). Extension 99 in this example is where your inbound calls end up in order to ring your phone.

exten => 99,1,NoOp(Inbound)
exten => 99,n,MYSQL(Connect connid localhost asterisk mypassword asterisk)
exten => 99,n,GotoIf($["${connid}" = ""]?nodb)
exten => 99,n,MYSQL(Query resultid ${connid} SELECT\ name\ FROM\ addressbook\ WHERE\ phone_number="${CALLERID(num)}"\ LIMIT\ 1)
exten => 99,n,MYSQL(Fetch fetchid ${resultid} name)
exten => 99,n,MYSQL(Clear ${resultid})
exten => 99,n,Set(CALLERID(name)=${name})
exten => 99,n,MYSQL(Disconnect ${connid})
exten => 99,n(nodb),NoOp(DoneDB)
exten => 99,n,Dial(SIP/myphone,30,)
exten => 99,n,Congestion()

To actually make it work, you also need the database filled in.

CREATE TABLE addressbook (
    phone_number VARCHAR(40),
    name VARCHAR(40),
    PRIMARY KEY(phone_number)
);
INSERT INTO addressbook VALUES ('08XXXXXXX1', 'Bob');
INSERT INTO addressbook VALUES ('08XXXXXXX2', 'Mary');

(and so forth).

So, what next? Well, you’re logging your CDR (Call Data Records) into MySQL, right? Let’s make a ‘view’ of the CDR that includes the names:

CREATE VIEW cdr_with_names AS
    SELECT cdr.*,ab_src.name AS src_name,ab_dst.name AS dst_name
    FROM cdr
    LEFT JOIN addressbook ab_src
        ON ((cdr.src = ab_src.phone_number)
        OR (CONCAT('08',cdr.src) = ab_src.phone_number))
    LEFT JOIN addressbook ab_dst
        ON ((cdr.dst = ab_dst.phone_number)
        OR (CONCAT('08',cdr.dst) = ab_dst.phone_number))
;

What’s this CONCAT('08',cdr.src) stuff? ‘08’ is my local area code, and I can leave it off outbound calls to the same area code if I so choose. This way, my query finds both variants (‘XXXXXXXX’ and ‘08XXXXXXXX’).

It’s such a messy view for direct use, though. Let’s clean it up with a view that only shows us the columns we really care that much about:

CREATE VIEW cdr_easy AS SELECT calldate,src,src_name,dst,dst_name,disposition,billsec FROM cdr_with_names

In a few minutes of hacking at extensions.conf and MySQL, you now have database-driven CallerID text and a straightforward CDR view that includes human-readable names. What can’t Asterisk do?