Simple MySQL CallerID name lookup for Asterisk
Because there's no 411.com reverse lookup in the UK :-(
There's no equivalent to the US's 411.com reverse lookup in the UK, so I set out to
create something simple[1] and local. A table is created on a MySQL database that contains
one column for the CallerID number [callerid_number], and one column for the CallerID name
[callerid_name]. The name you give your table and it's columns is pretty much irrelevant,
so long as you modify the query below to match.
Asterisk takes
an incoming call, and queries the table for the name associated with the number. If
a name is found in the table, all the phones that ring display the name of the caller
as well as the number. Simple, eh?
This macro does the legwork. It goes in your extensions.conf:
[macro-sqlcalleridlookup]
; Query an SQL table for a CallerID number, return the name if
; it exists, then assign the name to ${CALLERID(name)}.
exten => s,1,MYSQL(Connect connid localhost tableuser tablepass CliDB_db)
exten => s,n,MYSQL(Query resultid ${connid} SELECT\ callerid_name\ from\ callerid\ where\ callerid_number=${ARG1} )
exten => s,n,MYSQL(Fetch fetchid ${resultid} callernameid)
exten => s,n,Set(CALLERID(name)=${callernameid})
exten => s,n,MYSQL(Clear ${resultid})
exten => s,n,MYSQL(Disconnect ${connid})
Now we've got our macro in place in extensions.conf, we need to invoke it.
This is the part of my incoming call path where I invoke the macro:
exten => 1234567,1,Macro(sqlcalleridlookup,${CALLERID(num)})
exten => 1234567,n,Dial(SIP/201&SIP/202&SIP/203&SIP/204,45)
; '1234567' is where Sipgate sends my incoming calls to
So, I hear you cry, "What's the point of all this? Surely it makes more sense to use
LookupCIDName()?"
Well, you'd be right, if all you ever want to do with this table is query it from Asterisk.
However, I have a Cisco 7941G handset, and one of it's features is the ability to query an XML
directory over HTTP. Using the following bit of PHP we can, in theory, supply the same data to
a 7941G:
<CiscoIPPhoneDirectory>
<Title>Phone Directory</Title>
<Prompt>Dial that number</Prompt>
<?php
$db = mysql_connect("localhost", "tableuser", "tablepass");
mysql_select_db("CliDB_db",$db);
$result = mysql_query("SELECT `callerid_name`,`callerid_number` FROM callerid ORDER BY `callerid_name` ASC",$db);
while ($myrow = mysql_fetch_row($result)) {
printf("<DirectoryEntry><Name>%s</Name><Telephone>%s</Telephone></DirectoryEntry>\n", $myrow[0], $myrow[1] );
}
?>
</CiscoIPPhoneDirectory>
Todo
- Get Cisco XML directory stuff working
- Query on last-N digits of the CallerID number, as sometimes calls appear in International rather than National format
- Get a copy of the UK area code DB into the table, so that at least if we don't have an exact match, we can give a rough geographical location.
Caveats
- The XML directory stuff doesn't work. I'm working on it
- What happens if the caller hangs up before we get to the MYSQL(Clear) bit of the macro?
- No input sanitisation of the CallerID number - someone could send malicious SQL in there and drop your database, or worse!
