Mysql Phonebook

Home » Asterisk Users » Mysql Phonebook
Asterisk Users 1 Comment

I do this. I connect back to the mysql server via odbc, and as you, I have two databases for this, one called blacklisted and the other called speeddials.

My dialplan code below:

exten => _5XXXXXXXXX,1,Answer()
exten => _5XXXXXXXXX,n,Gosub(check_blacklist,s,1)
exten => _5XXXXXXXXX,n,Gosub(get_callerid,s,1)

[check_blacklist]

exten => s,1,GotoIf($[“${CALLERID(number)}” = “” ]?2:3)
exten => s,n,Set(CALLERID(all)=Restricted <0>)
exten => s,n,Set(ARRAY(flag,note)=${ODBC_BLACKLIST(${CALLERID(number)})})
exten => s,n,GotoIf($[“${flag}” = “YES”]?blacklisted,s,1)
exten => s,n,NoOP(Caller not blacklisted)
exten => s,n,Return

[blacklisted]

exten => s,1,NoOP(Caller: ${CALLERID(number)} is on the black list)
exten => s,n,NoOP(NOTE: ${note})
exten => s,n,Set(CDR(userfield)=Blacklisted)
exten => s,n,Zapateller(answer)
exten => s,n,Hangup(2)

The ODBC query is:

[BLACKLIST]
dsn=MySQL-blacklisted readsql=SELECT flag, note FROM [putyourdatabasenamehere] WHERE phone=${SQL_ESC(“${ARG1}”)}

[get_callerid]

exten => s,1,Set(ARRAY(speed.dial,speed.name)=${ODBC_GET_CALLERID(${CALLERID(num)})})
exten => s,n,Set(CALLERID(name)=${speed.name})
exten => s,n,Return()

The ODBC query is:

[GET_CALLERID]
dsn=MySQL-speeddials readsql=SELECT phone, name, code FROM [putyourdatabasenamehere] WHERE phone = ${ARG2}

Doug

One thought on - Mysql Phonebook

  • Write an AGI script that expects a phone number as its parameter, performs a database lookup on the number and sets some channel variables with the caller’s name and whether or not they are blacklisted. You probably need only one table, really; use VARCHAR() fields for the number and name and something like a TINYINT(1) for indicating whether or not the number is blacklisted.
    After the script exits, the dialplan will see any variables it set. So you can do something like this;

    exten => s,1,Set(from=${CALLERID(num)})
    exten => s,n,(Incoming call from ${from})
    exten => s,n,AGI(lookup_caller.agi,${from})
    ; /var/lib/asterisk/agi-bin/lookup_caller.agi sets variables `blocked` to true
    ; if the caller is blocked, and `callername` to the caller’s name exten => s,n,GotoIf(${blocked}?unwelcome:permitted)
    exten => s,n(permitted),NoOp(This call is allowed)
    exten => s,n,Set(CALLERID(name)=${callername})
    ; we can maybe do something else funky with callername here exten => s,n,Dial(${ALL_EXTS})
    exten => s,n,Hangup()
    ; tell unwanted callers where to stick that phone exten => s,n(unwelcome),MP3player(/songs/kevin_bloody_wilson/dicktaphone.mp3)
    exten => s,n,Hangup()

    I used some simple example code to implement a little daemon on users’
    workstations; which listened on a UDP port, and created system notifications informing the user of an incoming call. As this was all on the inside of a firewall, I also included the capability to open up a web page. The AGI script was able not only to notified the workstation adjacent to the phone of the incoming call; but if the number was recognised as belonging to a user within our system, would bring up their details on screen (all the work was done through a custom web application).