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?