Save Lono

I’ve been administering Asterisk servers for many years and everyday I’m learning something new.  As the size of the projects have gotten bigger, so have the demands of my customers.  One common request is database integration.  In the past I’ve used external AGI scripts to run database queries to return values to Asterisk.  But more recently I’ve been avoiding AGI in favor of direct access SQL.  This is achieved by building ODBC modules into Asterisk at build time.  Let me tell you, it’s worth it!

Asterisk & ODBC provide the ability to easily update and retrieve data by defining SQL statements as special variables that can be called from the dial plan.  For instance, we could bill ‘long distance’, take credit cards and let Asterisk access other types of information.  In my tutorial we will configure Asterisk to dial a variable that represents a SQL statement.  This could also be done by calling an AGI script, but why do the extra work?

This ‘How to’ is specifically geared towards MySQL.  However, if you set the up the ODBC driver correctly you can use any SQL server you like.  Which leads me to my next thought, before you start, make sure ODBC is set up correctly.  Follow one of my previous tutorials:

configure Linux ODBC for MS-SQL

configure Linux ODBC for MYSQL

Once you do this we are ready to continue…

The goals of this post:

- create MySQL table with example data
- configure res_odbc.conf
- configure func_odbc.conf
- configure extensions.conf for query

(more…)

[general]

static=yes
writeprotect=no

[bogon-calls]

exten => _x.,1,Congestion

[macro-stdexten]
;
;for dialing internal extensions

exten => s,1,Set(dynext=${DB(dynext/${ARG1})})
exten => s,n,NoOp(${dynext})
exten => s,n,NoOp(${LEN(${dynext})})
exten => s,n,GotoIf($["${LEN(${dynext})}" = "7"]?s,100)
exten => s,n,GotoIf($["${LEN(${dynext})}" = "10"]?s,100)
exten => s,n,GotoIf($["${LEN(${dynext})}" = "11"]?s,100)
exten => s,n,GotoIf($["${LEN(${dynext})}" = "6"]?s,200) ; Calls 6-digit Extension
exten => s,n,GotoIf($["${LEN(${dynext})}" = "0"]?s,300) ; Calls 6-digit Extension
exten => s,n(dial),Dial(SIP/${dynext},20,twW) ; Ring the interface, 20 seconds maximum
exten => s,n,Goto(s-${DIALSTATUS},1) ; Jump based on status
exten => s,100,Dial(ZAP/g1/${dynext},20,twW) ; Ring the interface, 20 seconds maximum
exten => s,101,Goto(s-${DIALSTATUS},1) ; Jump based on status
exten => s,200,Goto(from-sip,${dynext},1); Calls 6-digit Extension
exten => s,300,Set(dynext=${ARG1})
exten => s,301,Goto(dial)

exten => s-BUSY,1,Voicemail(b${ARG1}) ; If busy, send to voicemail w/ busy announce
exten => s-BUSY,2,Hangup
exten => s-NOANSWER,1,Voicemail(u${ARG1}) ; If unavailable, send to voicemail
exten => s-NOANSWER,2,Hangup
exten => _s-.,1,Goto(s-NOANSWER,1) ; Treat anything else as no answer

[from-sip]

include => test
include => from-internal

[test]

exten => 1234,1,agi(matts-test.agi)
exten => 1234,2,wait(2)
;exten => 1234,n,voicemail(8888)
exten => 1234,n,hangup
[from-internal]

;Standard Internal Extensions

exten => _888X,1,Macro(stdexten,${EXTEN},sip/${EXTEN})
exten => _888X,2,Hangup

; Add Member SIP/${CALLERID(num)}
exten => 2000,1,Answer
exten => 2000,n,VMauthenticate(${CALLERID(num)}@default)
exten => 2000,n,AddQueueMember(savelono-support|SIP/${CALLERID(num)}|1|)
exten => 2000,n,Playback(agent-loginok)
exten => 2000,n,Hangup

; Remove Member - Dynamic Agent SIP/${CALLERID(num)}
exten => 2001,1,Answer
exten => 2001,n,VMauthenticate(${CALLERID(num)}@default)
exten => 2001,n,RemoveQueueMember(savelono-support|SIP/${CALLERID(num)})
exten => 2001,n,Playback(agent-loggedoff)
exten => 2001,n,Hangup

exten => 5000,1,goto(test-queue,s,1)

[savelono-queue-out]

include => from-internal

[test-queue]

exten => s,1,Queue(savelono-support)
exten => s,2,hangup

I told a customer for the company I for that I would figure out how to logout agents by CID(Caller ID). So I figured, why not kill two birds with one stone. Today we will create a single Queue, Agent, and dial plan to accomplish this goal. I’m using Asterisk 1.4, Fedora 10 and a Polycom IP SIP phone for my demonstration purposes.

When I started this project four hours ago, I thought I would google my way to another successful blog post(and happy customer), but no…  logging out agents in Asterisk is very unintuitive.  The agentcallbacklogin utility has the exact same prompts for logging in as out.  AgentCallbackLogin (when initiated, from the dial plan)  asks for three things, agent, agent password, and call back number.  To eliminate all these prompts I’m using the ‘$CallerID(num)’ variable to automatically answer the agent and call back number.  So the user 8888 dials ‘1000′ and and AgentCallbackLogin assumes he is AGENT/8888 with a password of ‘8888′.

from /etc/asterisk/extensions.conf

exten => 1000,1,AgentcallbackLogin(${CALLERID(num)}||${CALLERID(num)}@savelono-queue-out)
exten => 1000,n,hangup

(more…)

[general]

static=yes
writeprotect=no

[bogon-calls]

exten => _x.,1,Congestion

[macro-stdexten]
;
;for dialing internal extensions

exten => s,1,Set(dynext=${DB(dynext/${ARG1})})
exten => s,n,NoOp(${dynext})
exten => s,n,NoOp(${LEN(${dynext})})
exten => s,n,GotoIf($["${LEN(${dynext})}" = "7"]?s,100)
exten => s,n,GotoIf($["${LEN(${dynext})}" = "10"]?s,100)
exten => s,n,GotoIf($["${LEN(${dynext})}" = "11"]?s,100)
exten => s,n,GotoIf($["${LEN(${dynext})}" = "6"]?s,200) ; Calls 6-digit Extension
exten => s,n,GotoIf($["${LEN(${dynext})}" = "0"]?s,300) ; Calls 6-digit Extension
exten => s,n(dial),Dial(SIP/${dynext},20,twW) ; Ring the interface, 20 seconds maximum
exten => s,n,Goto(s-${DIALSTATUS},1) ; Jump based on status
exten => s,100,Dial(ZAP/g1/${dynext},20,twW) ; Ring the interface, 20 seconds maximum
exten => s,101,Goto(s-${DIALSTATUS},1) ; Jump based on status
exten => s,200,Goto(from-sip,${dynext},1); Calls 6-digit Extension
exten => s,300,Set(dynext=${ARG1})
exten => s,301,Goto(dial)

exten => s-BUSY,1,Voicemail(b${ARG1}) ; If busy, send to voicemail w/ busy announce
exten => s-BUSY,2,Hangup
exten => s-NOANSWER,1,Voicemail(u${ARG1}) ; If unavailable, send to voicemail
exten => s-NOANSWER,2,Hangup
exten => _s-.,1,Goto(s-NOANSWER,1) ; Treat anything else as no answer

[from-sip]

include => test
include => from-internal

[test]

exten => 1234,1,agi(matts-test.agi)
exten => 1234,2,wait(2)
;exten => 1234,n,voicemail(8888)
exten => 1234,n,hangup

[from-internal]

;Standard Internal Extensions

exten => _888X,1,Macro(stdexten,${EXTEN},sip/${EXTEN})
exten => _888X,2,Hangup

;Logon
exten => 1000,1,AgentcallbackLogin(${CALLERID(num)}||${CALLERID(num)}@savelono-queue-out)
exten => 1000,n,hangup

;Logoff - best alternative I’ve found so far
;could be better with additional logic

exten => 1001,1,System(/usr/sbin/asterisk -rx “agent logoff Agent/${CALLERID(NUM)}”)
exten => 1001,n,RemoveQueueMember(savelono-support|Agent/${CALLERID(NUM)})
exten => 1001,n,Playback(agent-loggedoff)
exten => 1001,n,Playback(auth-thankyou)
exten => 1001,n,Hangup

;Logoff this way works but is not very intuitive because you
;have to hit the # key when prompted for a dial back extension
;it really doesn’t make sense to endusers
;
exten => 1002,1,AgentcallbackLogin(${CALLERID(num)}||)
exten => 1002,n,hangup

[savelono-queue-out]

include => from-internal