Your Choice of Database

In Chapter 3, we installed and configured MySQL, plus the ODBC connector to it, and we’ve been using the tables that Asterisk provides to allow various configuration options to be stored in the database.

We chose MySQL primarily because it is still the most popular open source database engine, and rather than bouncing around, duplicating trivial commands on various different engines, we left implementing other types of databases to the skill set of the reader. If you want to use a different database such as MariaDB, PostGreSQL, Microsoft SQL, or in fact dozens (perhaps hundreds) of other databases supported by unixODBC, it’s quite likely that Asterisk will work with it.

Asterisk also offers native connectors to several databases; however, ODBC works so well we’ve never found any obvious reason to do things any other way. We’re going to both recommend ODBC, and also focus exclusively on it. If you have a preference for something else, this chapter should still provide you with the fundamentals, as well as some working examples, and from there you are of course free to branch out into other methodologies.

Note that regardless of the database you choose, this book cannot teach you about databases. We have tried as best as we can to provide examples that do not require too much expertise in database administration (DBA), but the simple fact is that basic DBA skills are a prerequisite for being able to fully harness the power of any database, including any you might wish to integrate with your Asterisk system. Database skills are essential to nearly all system administrative disciplines these days, so we felt it was appropriate to assume at least a basic level of familiarity with database concepts.

Managing Databases

While it isn’t within the scope of this book to teach you how to manage databases, it is at least worth noting briefly some of the applications you could use to help with database management. There are many options, some of which are local client applications running from your computer and connecting to the database, and others being web-based applications that could be served from the same computer running the database itself, thereby allowing you to connect remotely.

Some of the ones we’ve used include:

• phpMyAdmin

• MySQL Workbench

• Navicat (commercial)

In our examples we will be using the MySQL command line, not because it is superior, but simply because it’s ubiquitous on any system with MySQL, so you’ve already got it and have been using it in this book.

For more heavy-duty database design, the command line is probably not as powerful as a well-designed GUI would be. Grab a copy of MySQL Workbench at least and give it a whirl.

Powering Your Dialplan with func_odbc

The func_odbc dialplan function module allows you to define and use relatively simple functions in your dialplan that will retrieve information from databases as calls are being processed. There are all kinds of ways in which this might be used, such as managing users or allowing the sharing of dynamic information within a clustered set of Asterisk machines. We won’t claim that this will make designing and writing dialplan code easier, but we will promise that it will allow you to add a whole new level of power to your dialplans, especially if you are comfortable working with databases. We don’t know anybody in the Asterisk community who does not love func_odbc.

The way func_odbc works is by allowing you to define SQL queries, to which you assign function names. The func_odbc.conf file is where you specify the relationships between the functions you create and the SQL statements you wish them to perform. You use the named functions you have created in your dialplan to retrieve and update values in the database.

In order to get you into the right frame of mind for what follows, we want you to picture a Dagwood sandwich.²

Can you relay the total experience of such a thing by showing someone a picture of a tomato, or by waving a slice of cheese about? Hardly. That is the conundrum we faced when trying to give useful examples of why func_odbc is so powerful. So, we decided to build the whole sandwich for you. It’s quite a mouthful, but after a few bites of this, peanut butter and jelly is never going to be the same.

Figure 15-1. Relationships between func_odbc.conf, res_odbc.conf, /etc/odbc.ini (unixODBC), and the database connection

A Gentle Introduction to func_odbc

Before we dive into func_odbc, we feel a wee bit of history is in order.

The very first use of func_odbc, which occurred while its author was still writing it, is also a good introduction to its use. A customer of one of the module’s authors noted that some people calling into his switch had figured out a way to make free calls with his system. While his eventual intent was to change his dialplan to avoid those problems, he needed to blacklist certain caller IDs in the meantime, and the database he wanted to use for this was a Microsoft SQL Server database.

With a few exceptions, this is the actual dialplan:

[span3pri]
exten => _50054XX,1,NoOp()
   same => n,Set(CDR(accountcode)=pricall)
   ; Does this callerID appear in the database?
   same => n,GotoIf($[${ODBC_ANIBLOCK(${CALLERID(number)})}]?busy)
   same => n(dial),Dial(DAHDI/G1/${EXTEN})
   same => n(busy),Busy(10) ; Yes, you are on the blacklist.
   same => n,Hangup

This dialplan, in a nutshell, passes all calls to another system for routing purposes, except those calls whose caller IDs are in a blacklist. The calls coming into this system used a block of 100 seven-digit DIDs. You will note a dialplan function is being used that you won’t find listed in any of the functions that ship with Asterisk: ODBC_ANIBLOCK(). This function was instead defined in another configuration file, func_odbc.conf:

[ANIBLOCK]
dsn=telesys
readsql=SELECT IF(COUNT(1)>0, 1, 0) FROM Aniblock WHERE NUMBER='${ARG1}'

So, your ODBC_ANIBLOCK()³ function connects to a data source in res_odbc.conf named telesys and selects a count of records that have the NUMBER specified by the argument, which is (referring to the preceding dialplan) the caller ID. Nominally, this function should return either a 1 (indicating the caller ID exists in the Aniblock table) or a 0 (if it does not). This value also evaluates directly to true or false, which means we don’t need to use an expression in our dialplan to complicate the logic.

And that, in a nutshell, is what func_odbc is all about: writing custom dialplan functions that return a result from a database. Next up, a more detailed example of how one might use func_odbc.

Getting Funky with func_odbc: Hot-Desking

OK, back to the Dagwood sandwich we promised.

We believe the value of func_odbc will become very clear to you if you work through the following example, which will produce a new feature on your Asterisk system that depends heavily on func_odbc.

Picture a small company with a sales force of five people who have to share two desks. This is not as cruel as it seems, because these folks spend most of their time on the road, and they are each only in the office for at most one day each week.

Still, when they do get into the office, they’d like the system to know which desk they are sitting at, so that their calls can be directed there. Also, the boss wants to be able to track when they are in the office and control calling privileges from those phones when no one is there.

This need is typically solved by what is called a hot-desking feature. We have built one for you in order to show you the power of func_odbc.

Let’s start with the easy stuff, and create two new phone credentials in our database.

First, the endpoints table:

MySQL> INSERT INTO asterisk.ps_endpoints (id,transport,aors,auth,context,disallow,allow, 
direct_media,callerid) 

VALUES
('HOTDESK_1','transport-tls','HOTDESK_1','HOTDESK_1','hotdesk','all','ulaw','no', 
'HOTDESK_1'),
('HOTDESK_2','transport-tls','HOTDESK_2','HOTDESK_2','hotdesk','all','ulaw','no', 
'HOTDESK_2');

Then, the auths:

MySQL> INSERT INTO asterisk.ps_auths (id,auth_type,password,username) 

VALUES
('HOTDESK_1','userpass','notsohot1','HOTDESK_1'), 
('HOTDESK_2','userpass','notsohot2','HOTDESK_2'); 

Finally, the aors:

MySQL> INSERT INTO asterisk.ps_aors
(id,max_contacts) 

VALUES
('HOTDESK_1',1), 
('HOTDESK_2',1); 

Notice that we’ve told these two endpoints to enter the dialplan at a context named [hotdesk]. We’ll define that shortly.

That’s all for our endpoint configuration. We’ve got a few slices of bread, which is hardly a sandwich yet.

Now let’s get the custom database built that we’re going to use for this.

Connect to your MySQL console as root:

$ mysql -u root -p

First we want a new schema to put all this in. It’s technically possible to put this in the asterisk schema, but we prefer to leave that schema alone, reserved only for whatever Asterisk’s Alembic scripts do with it during upgrades.

MySQL> CREATE SCHEMA pbx;

MySQL> GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,SHOW VIEW ON pbx.* TO 'asterisk'@'::1'; 

MySQL> GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,SHOW VIEW ON pbx.* TO 
'asterisk'@'127.0.0.1'; 

MySQL> GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,SHOW VIEW ON pbx.* TO 
'asterisk'@'localhost'; 

MySQL> GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE,SHOW VIEW ON pbx.* TO 
'asterisk'@'localhost.localdomain'; 

MySQL> FLUSH PRIVILEGES;

Then create the table with the following bit of SQL:

CREATE TABLE pbx.ast_hotdesk
(
  id serial NOT NULL,
  extension text,
  first_name text,
  last_name text,
  cid_name text,
  cid_number varchar(10),
  pin int,
  status bool DEFAULT false,
  endpoint text,
  CONSTRAINT ast_hotdesk_id_pk PRIMARY KEY (id)
);

After that, populate the database with the following information (some of the values that you see actually will change only after the dialplan work is done, but we include it here by way of example).

At the MySQL console, run the following command:

MySQL> INSERT INTO pbx.ast_hotdesk 
(extension, first_name, last_name, cid_name, cid_number, pin, status) 

VALUES 
('1101','Herb','Tarlek','WKRP','1101','110111',0)
('1102','Al','Bundy','Garys','1102','110222',0), 
('1103','Willy','Loman','','1103','110333',0), 
('1104','Jerry','Lundegaard','Gustafson','1104','110444',0), 
('1105','Moira','Brown','Craterside','1105','110555',0); 

Repeat these commands, changing the VALUES as needed, for all entries you wish to have in the database.⁴ After you’ve input your sample data, you can view the data in the ast_hotdesk table by running a simple SELECT statement from the database console:

MySQL> SELECT * FROM pbx.ast_hotdesk;

Which might give you something like the following output:

+--+---------+----------+----------+----------+----------+------+------+--------+
|id|extension|first_name|last_name |cid_name  |cid_number|pin   |status|endpoint|
+--+---------+----------+----------+----------+----------+------+------+--------+
| 1|1101     |Herb      |Tarlek    |WKRP      |1101      |110111|     0|NULL    |
| 2|1102     |Al        |Bundy     |Garys     |1102      |110222|     0|NULL    |
| 3|1103     |Willy     |Loman     |          |1103      |110333|     0|NULL    |
| 4|1104     |Jerry     |Lundegaard|Gustafson |1104      |110444|     0|NULL    |
| 5|1105     |Moira     |Brown     |Craterside|1105      |110555|     0|NULL    |
+--+---------+----------+----------+----------+----------+------+------+--------+

We’ve got the condiments now, so let’s get to our dialplan. This is where the magic is going to happen.

Somewhere in extensions.conf we are going to create the [hotdesk] context. To start, let’s define a pattern-match extension that will allow the users to log in:

[hotdesk]
include => sets

exten => _*99110[1-5],1,Noop(Hotdesk login)
  same => n,Set(HotExten=${EXTEN:3}) ; strip off the leading *99
  same => n,Noop(Hotdesk Extension ${HotExten} is changing status) ; for the log
  same => n,Set(${HotExten}_STATUS=${HOTDESK_INFO(status,${HotExten})})
  same => n,Set(${HotExten}_PIN=${HOTDESK_INFO(pin,${HotExten})})
  same => n,Noop(${HotExten}_PIN is now ${${HotExten}_PIN})
  same => n,Noop(${HotExten}_STATUS is ${${HotExten}_STATUS})})

We’re not done writing this extension yet, but we need to digress for a few pages to discuss where we’re at so far.

When a sales agent sits down at a desk, they log in by dialing *99 plus their extension number. In this case we have allowed the 1101 through 1105 extensions to log in with our pattern match of _99110[1-5]. You could just as easily make this less restrictive by using _9911XX (allowing 1100 through 1199). This extension uses func_odbc to perform a lookup with the HOTDESK_INFO() dialplan function. This custom function (which we will define in the func_odbc.conf file) performs an SQL statement and returns whatever is retrieved from the database.

So, let’s create the /etc/asterisk/func_odbc.conf file, and within that define the new function HOTDESK_INFO():

$ sudo -u asterisk vim /etc/asterisk/func_odbc.conf

[INFO]
prefix=HOTDESK
dsn=asterisk
synopsis=Select value of field in ARG1, where 'extension' matches ARG2
description=Allow dialplan to extract data from any field in pbx.ast_hotdesk table.
readsql=SELECT ${ARG1} FROM pbx.ast_hotdesk WHERE extension = '${ARG2}'

That’s a lot of stuff in just a few lines. Let’s quickly cover them before we move on.

First of all, the prefix is optional (default prefix is 'ODBC'). This means that if you don’t define a prefix, Asterisk adds 'ODBC' to the function name (in this case, INFO), which means this function would become ODBC_INFO(). This is not very descriptive of what the function is doing, so it can be helpful to assign a prefix that helps to relate your ODBC functions to the tasks they are performing. We chose 'HOTDESK', which means that this custom function will be named HOTDESK_INFO() in the dialplan.

The dsn attribute tells Asterisk which connection to use from res_odbc.conf. Since several database connections could be configured in res_odbc.conf, we specify which one to use here. In Figure 15-1, we show the relationship between the various file configurations and how they reference down the chain to connect to the database.

Finally, we define our SQL statement with the readsql attribute. Dialplan functions can be called with two different formats: one for retrieving information, and one for setting information. The readsql attribute is used when we call the HOTDESK_INFO() function with the retrieve format (we could execute a separate SQL statement with the writesql attribute; we’ll discuss the format for that attribute a little bit later in this chapter).

Reading values from this function would take this format in the dialplan:

exten => s,n,Set(RETURNED_VALUE=${HOTDESK_INFO(status,1101)})

This would return the value located in the database within the status column where the extension column equals 1101. The status and 1101 we pass to the HOTDESK_INFO() function are then placed into the SQL statement we assigned to the readsql attribute, available as ${ARG1} and ${ARG2}, respectively. If we had passed a third option, this would have been available as ${ARG3}.

After the SQL statement is executed, the value returned (if any) is assigned to the RETURNED_VALUE channel variable.

So, in the first two lines of the following block of code, we are passing the value status and the value contained in the ${HotdeskExtension} variable (e.g., 1101) to the HOTDESK_INFO() function. The two values are then replaced in the SQL statement with ${ARG1} and ${ARG2}, respectively, and the SQL statement is executed. Finally, the value returned is assigned to the ${HotdeskExtension}_STATUS channel variable.

Let’s finish writing the pattern-match extension now:

exten => _*99110[1-5],1,Noop(Hotdesk login)
  same => n,Set(HotdeskExtension=${EXTEN:3}) ; strip off the leading *99
  same => n,Noop(Hotdesk Extension ${HotdeskExtension} is changing status) ; for the log
  same => n,Set(${HotdeskExtension}_STATUS=${HOTDESK_INFO(status,${HotdeskExtension})})
  same => n,Set(${HotdeskExtension}_PIN=${HOTDESK_INFO(pin,${HotdeskExtension})})
  same => n,Noop(${HotdeskExtension}_PIN is now ${${HotdeskExtension}_PIN})
  same => n,Noop(${HotdeskExtension}_STATUS is ${${HotdeskExtension}_STATUS})})
  same => n,GotoIf($["${${HotdeskExtension}_PIN}" = ""]?invalid_user)
  same => n,GotoIf($[${ODBCROWS} < 0]?invalid_user)
  same => n,GotoIf($[${${HotdeskExtension}_STATUS} = 1]?logout:login,1)

We’ll be writing some labels to handle invalid_user and logout a bit later, so don’t worry if it seems something is missing.

... = 1] ? hotdesk,${EXTEN},logout : hotdesk,login,1
           ^same    ^same   ^diff    ^same   ^diff ^diff

After assigning the value of the status column to the ${HotdeskExtension}_STATUS variable (if the user identifies themself as extension 1101, the variable name will be 1101_STATUS), we check if we’ve received a value back from the database using the ${ODBCROWS} channel variable.

The last row in the block checks the status of the phone and, if the agent is currently logged in, logs them off. If the agent is not already logged in, it will go to the login extension.

At the login extension the dialplan runs some initial checks to verify the PIN code entered by the agent. (Additionally, we’ve used the FILTER() function to make sure only numbers were entered to help avoid some SQL injection issues.) We allow them three tries to enter the correct PIN, and if all tries are invalid we’ll hang up:

exten => login,1,NoOp() ; set initial counter values
   same => n,Set(PIN_TRIES=1)     ; pin tries counter
   same => n,Set(MAX_PIN_TRIES=3) ; set max number of login attempts
   same => n,Playback(silence/1)  ; play back some silence so first prompt is
                                  ; not cut off
   same => n(get_pin),NoOp()
   same => n,Set(PIN_TRIES=$[${PIN_TRIES} + 1])   ; increase pin try counter
   same => n,Read(PIN_ENTERED,enter-password,${LEN(${${HotdeskExtension}_PIN})})
   same => n,Set(PIN_ENTERED=${FILTER(0-9,${PIN_ENTERED})})
   same => n,GotoIf($["${PIN_ENTERED}" = "${${HotdeskExtension}_PIN}"]?valid:invalid)
   same => n,Hangup()

   same => n(invalid),Playback(vm-invalidpassword)
   same => n,GotoIf($[${PIN_TRIES} <= ${MAX_PIN_TRIES}]?get_pin)
   same => n,Playback(goodbye)
   same => n,Hangup()

   same => n(valid),Noop(Valid PIN)

If the PIN entered matches, we continue with the login process through the (valid) label. First we utilize the CHANNEL variable to figure out which phone device the agent is calling from. The CHANNEL variable is usually populated with something similar to PJSIP/HOTDESK_1-ab4034c, so we make use of the CUT() function to first strip off the PJSIP/ component of the string. We then strip off the -ab4034c part of the string, and what remains is what we want (HOTDESK_1):⁵

   same => n(valid),Noop(Valid PIN)
; CUT off the channel technology and assign it to the LOCATION variable
   same => n,Set(LOCATION=${CUT(CHANNEL,/,2)})
; CUT off the unique identifier and save the remainder to the LOCATION variable
   same => n,Set(LOCATION=${CUT(LOCATION,-,1)})
; we'll come back to this shortly

We’re going to create and use some more functions in the func_odbc.conf file: HOTDESK_CHECK_SET(), which will determine if other users are already assigned to this phone; HOTDESK_STATUS(), which will assign the phone to this agent; and HOTDESK_CLEAR_SET(), which will clear any other users currently assigned to this phone (who perhaps forgot to log out).

In our func_odbc.conf file we’ll need to create the following functions:

; func_odbc.conf
[CHECK_SET]
prefix=HOTDESK
dsn=asterisk
synopsis=Check if this set is already assigned to somebody.
readsql=SELECT COUNT(status) FROM pbx.ast_hotdesk WHERE status = '1'
readsql+= AND endpoint = '${ARG1}'

[STATUS]
prefix=HOTDESK
dsn=asterisk
synopsis=Assign hotdesk extension to this endpoint/set.
writesql=UPDATE pbx.ast_hotdesk SET status = '${SQL_ESC(${VAL1})}',
writesql+= endpoint = '${SQL_ESC(${VAL2})}'
writesql+= WHERE extension = '${SQL_ESC(${ARG1})}'

[CLEAR_SET]
prefix=HOTDESK
dsn=asterisk
synopsis=Clear all instances of this endpoint
writesql=  UPDATE pbx.ast_hotdesk SET status=0,endpoint=NULL 
writesql+= WHERE endpoint='${SQL_ESC(${VAL1})}'

In our dialplan, we’ll need to call the function we just created, and pass call flow to the forcelogout label if somebody is already logged into this set:

  same => n(valid),Noop(Valid PIN)
  same => n,Set(LOCATION=${CUT(CHANNEL,/,2)})
  same => n,Set(LOCATION=${CUT(LOCATION,-,1)})
; We'll come back to this shortly ; you can remove this comment/line
  same => n(checkset),Set(SET_USED=${HOTDESK_CHECK_SET(${LOCATION})})
  same => n,GotoIf($[${SET_USED} > 0]?forcelogout)

; Set status for agent  to '1' and update the location/endpoint
  same => n(set_login_status),Set(HOTDESK_STATUS(${HotdeskExtension})=1,${LOCATION})
  same => n,Noop(ODBCROWS is ${ODBCROWS})
  same => n,GotoIf($[${ODBCROWS} < 1]?error,1)
  same => n,Playback(agent-loginok)
  same => n,Hangup()

  same => n(forcelogout),NoOp()
; set all currently logged-in users on this device to logged-out status
  same => n,Set(HOTDESK_CLEAR_SET()=${LOCATION})
  same => n,Goto(checkset)      ; return to logging in

There are some potentially new concepts we’ve just introduced in the examples. Specifically, the syntax in the HOTDESK_STATUS() function has a few new tricks you might have noticed. We now have both ${VALx} and ${ARGx} variables in our SQL statement.

These contain the information we pass to the function from the dialplan. In this case, we have two VAL variables and a single ARG variable that were set from the dialplan via this statement:

same => n(set_login_status),Set(HOTDESK_STATUS(${HotdeskExtension})=1,${LOCATION})

Notice the syntax is slightly different from that of the read-style function. This signals to Asterisk that you want to perform a write (this is the same structural syntax as that used for other dialplan functions).

We are including the value of the ${HotdeskExtension} variable in our call to the HOTDESK_STATUS() function (which then becomes the ${ARG1} variable for that function in func_odbc.conf). However, we are also passing two values, '1' and ${LOCATION}. These will be associated in the function by the ${VAL1} and ${VAL2} variables, respectively.

OK, we’ve digressed a bit. Let’s wrap up a few parts of the agent components that we haven’t handled yet.

In the _*99110[1-5] extension, we need the following labels:

  same => n,GotoIf($[${${HotdeskExtension}_STATUS} = 1]?logout:login,1)

  same => n(invalid_user),Noop(Hot Desk extension ${HotdeskExtension} does not exist)
  same => n,Playback(silence/2&login-fail)
  same => n,Hangup()

  same => n(logout),Noop()
  same => n,Set(HOTDESK_STATUS(${HotdeskExtension})=0,) ; Note VAL2 is empty
  same => n,GotoIf($[${ODBCROWS} < 1]?error,1)
  same => n,Playback(silence/1&agent-loggedoff)
  same => n,Hangup()

We also include the hotdesk_outbound context, which will handle our outgoing calls after we have logged the agent into the system:

include => hotdesk_outbound ; this line can go anywhere in the [hotdesk] context

The [hotdesk_outbound] context utilizes many of the same principles already discussed. This context uses a pattern match to catch any numbers dialed from the hot-desk phones. We first set our LOCATION variable using the CHANNEL variable, then determine which extension (agent) is logged into the system and assign that value to the WHO variable. If this variable is NULL, we reject the outgoing call. If it is not NULL, then we get the agent information using the HOTDESK_INFO() function and assign it to several CHANNEL variables.

include => hotdesk_outbound

; put this code right below your [hotdesk] context
[hotdesk_outbound]
exten => _NXXXXXX.,1,NoOp()
 same => n,Set(LOCATION=${CUT(CHANNEL,/,2)})
 same => n,Set(LOCATION=${CUT(LOCATION,-,1)})
 same => n(checkset),Set(VALID_AGENT=${HOTDESK_CHECK_SET(${LOCATION})})
 same => n,Noop(VALID_AGENT is ${VALID_AGENT})
 same => n,Set(${CALLERID(name)}=${HOTDESK_INFO(cid_name,${VALID_AGENT})})
 same => n,Set(${CALLERID(num)}=${HOTDESK_INFO(cid_number,${VALID_AGENT})})
 same => n,GotoIf($[${VALID_AGENT} = 0]?notallowed) ; Nobody logged in--calls not allowed
 same => n,Dial(${LOCAL}/${EXTEN}) ; See the Outside Connectivity chapter
 same => n,Hangup()

 same => n(notallowed),Playback(sorry-cant-let-you-do-that2)
 same => n,Hangup()

If you are not logged in, the call will fail with a message. If you are logged in, the call will be passed to the Dial() application (which might also fail if you don’t have a carrier configured, but that’s something covered in earlier chapters, so we’re going to leave it as this for this section).

There’s one last bit of dialplan required. We have built this complex environment that lets our agents log in and out, but there isn’t actually any way of calling them!

We’re going to fix that now, by doing four things:

1. We’re going to include the [sets] context in the [hotdesk] context, so that our agents can use the other parts of our dialplan.

2. We’re going to give our agents mailboxes.

3. We’re going to create a new subroutine that will check the hotdesk for an agent, and a) ring them if they’re there, or b) fire the call off to voicemail if they’re not.

4. We’re going to build dialplan in the [sets] context so that everyone can call our agents.

Let’s get the mailboxes out of the way first:

MySQL> insert into `asterisk`.`voicemail`
(mailbox,fullname,context,password)
VALUES 
('1101','Herb Tarlek','default','110111'), 
('1102','Al Bundy','default','110222'), 
('1103','Willy Loman','default','110333'), 
('1104','Jerry Lundegaard','default','110444'), 
('1105','Moira Brown','default','110555');

All the rest of the work is in extensions.conf:

Way down at the bottom, let’s craft a subroutine that’ll handle things for us:

[subDialHotdeskUser]
exten => _[a-zA-Z0-9].,1,Noop(Call Hotdesk)
 same => n,Set(HOTDESK_ENDPOINT=${HOTDESK_INFO(endpoint,${EXTEN})}) ; Get assigned device
 same => n,GotoIf($["${HOTDESK_ENDPOINT}" = ""]?voicemail) ; if blank, send to voicemail
 same => n(ringhotdesk),Dial(PJSIP/${HOTDESK_ENDPOINT},${ARG1}) 
 same => n(voicemail),Voicemail(${EXTEN})
 same => n,Hangup()

And somewhere far closer to the top, we’ll add our hotdesk users to the section of dialplan where our other users live:

exten => 110,1,Dial(${UserA_DeskPhone}&${UserA_SoftPhone}&${UserB_SoftPhone})

exten => 1101,1,GoSub(subDialHotdeskUser,${EXTEN},1(12))
exten => 1102,1,GoSub(subDialHotdeskUser,${EXTEN},1(12))
exten => 1103,1,GoSub(subDialHotdeskUser,${EXTEN},1(12))
exten => 1104,1,GoSub(subDialHotdeskUser,${EXTEN},1(12))
exten => 1105,1,GoSub(subDialHotdeskUser,${EXTEN},1(12))

exten => 200,1,Answer()
     same => n,Playback(hello-world)
     same => n,Hangup()

And finally, back in our [hotdesk] context, we’re going to allow our agents to use the rest of the phone system:

[hotdesk]

include => sets

exten => _*99110[1-5],1,Noop(Hotdesk login)

Try a few scenarios:

1. Call from an agent internally.

2. Call from a normal user to a logged-in agent.

3. Call from a normal user to an unavailable agent.

Marvel at this technological terror you’ve constructed.

Now that we’ve implemented a fairly complex feature in the dialplan, using func_odbc to retrieve and store data in a remote relational database, you can see that with a handful of fairly simple functions in the func_odbc.conf file and a couple of tables in a database, you can create some powerful telephony applications.

OK, let’s move on to the Asterisk Realtime Architecture, which has in many cases been made obsolete by ODBC, but can still be useful.

Using Realtime

The Asterisk Realtime Architecture (ARA) allows you to store all the parameters normally stored in your Asterisk configuration files (commonly located in /etc/asterisk) in a database. There are two types of realtime: static and dynamic.

The static version is similar to the traditional method of reading a configuration file (information is only loaded when triggered from the CLI), except that the data is read from the database instead.⁷

The Dynamic Realtime method, which loads and updates the information as it is used by the live system, is commonly used for things such as SIP (or IAX2, etc.) user and peer objects, as well as voicemail boxes.

Making changes to static information requires a reload, just as if you had changed a text file on the system, but dynamic information is polled by Asterisk as needed, so no reload is required when changes are made to this data. Realtime is configured in the extconfig.conf file located in the /etc/asterisk directory. This file tells Asterisk what to load from the database and where to load it from, allowing certain files to be loaded from the database and other files to be loaded from the standard configuration files.

; /etc/asterisk/extconfig.conf
[settings]
filename.conf => driver,database[,table]

; extconfig.conf
[settings]
sippeers => driver,database[,table]

Storing Call Detail Records

Call detail records (CDR) contain information about calls that have passed through your Asterisk system. They are discussed further in Chapter 21. Storing CDR is a popular use of databases in Asterisk, because it makes them easier to work with. Also, by placing records into a database you open up many possibilities, including building your own web interface for tracking statistics such as call usage and most-called locations, billing, or phone company invoice verification.

You should always implement CDR storage to a database on any production system (you can always store CDR to a file as well, so there’s nothing lost).

The best way to store your call detail records is via the cdr_adaptive_odbc module. This module allows you to choose which columns of data built into Asterisk are stored in your table, and it permits you to add additional columns that can be populated with the CDR() dialplan function. You can even store different parts of CDR data to different tables and databases, if that is required.

To create the table, we have Alembic. The process is almost identical to the one you performed during the system installation, except of course the .ini file is different.

$ cd ~/src/asterisk-15.<TAB>/contrib/ast-db-manage

$ cp cdr.ini.sample cdr.ini

$ egrep ^sqlalchemy config.ini

sqlalchemy.url = mysql://asterisk:YouNeedAReallyGoodPasswordHereToo@localhost/asterisk

The same credentials we used before will also work for CDR.

$ sudo vim cdr.ini

Add the line you just got back from grep to this file, and save.

$ alembic -c ./cdr.ini upgrade head

INFO  [alembic.runtime.setup] Creating new alembic_version_cdr table.
INFO  [alembic.runtime.migration] Running upgrade  -> 210693f3123d, Create CDR table.
INFO  [alembic.runtime.migration] Running upgrade 210693f3123d -> 54cde9847798

Alembic doesn’t do too much bragging, so the output is terse, but it appears to have completed successfully. Let’s check.

$ mysql -u asterisk -p

MySQL> describe asterisk.cdr

You should get a list of all the fields in the table (which means Alembic was successful). If you get a message like Table 'asterisk.cdr' doesn't exist, that indicates Alembic didn’t complete the configuration, and you need to review the messages from the Alembic output to see what went wrong (credentials is usually what causes grief here).

Well, that wasn’t too hard, eh? The next step is to tell Asterisk to use this new table for CDR going forward.

$ sudo -u asterisk touch /etc/asterisk/cdr_adaptive_odbc.conf

$ sudo -u asterisk vim /etc/asterisk/cdr_adaptive_odbc.conf

Into this new file, paste the following:

[adaptive_connection]
connection=asterisk
table=cdr

This is almost too easy, wouldn’t you say? Alrighty, now we just have to reload the ccdr_adaptive_odbc.so module in Asterisk:

$ sudo asterisk -rvvvvvvv

*CLI> module reload cdr_adaptive_odbc.so 

You can verify that the Adaptive ODBC backend has been loaded by running the following:¹⁰

*CLI> cdr show status

Call Detail Record (CDR) settings
----------------------------------
  Logging:                    Enabled
  Mode:                       Simple
  Log unanswered calls:       No
  Log congestion:             No

* Registered Backends
  -------------------
    cdr-syslog
    Adaptive ODBC
    cdr-custom
    csv
    cdr_manager

Now place a call that gets answered (e.g., using Playback(), or Dial()ing another channel and answering it). You should get some CDRs stored into your database. You can check by running SELECT * FROM CDR; from your database console.

With the basic CDR information stored in the database, you might want to add some additional information to the cdr table, such as the route rate. You can use the ALTER TABLE directive to add a column called route_rate to the table:

sql> ALTER TABLE cdr ADD COLUMN route_rate varchar(10);

Now reload the cdr_adaptive_odbc.so module from the Asterisk console:

*CLI> module reload cdr_adaptive_odbc.so

and populate the new column from the Asterisk dialplan using the CDR() function, like so:

exten => _NXXNXXXXXX,1,Verbose(1,Example of adaptive ODBC usage)
   same => n,Set(CDR(route_rate)=0.01)
   same => n,Dial(SIP/my_itsp/${EXTEN})
   same => n,Hangup()

After the alteration to your database and dialplan, you can place a call and then look at your CDRs. You should see something like the following:

+--------------+----------+---------+------------+
| src          | duration | billsec | route_rate |
+--------------+----------+---------+------------+
| 0000FFFF0008 | 37       | 30      | 0.01       | 
+--------------+----------+---------+------------+

In reality, storing rating in the call record might not be ideal (CDR is typically used as a raw resource, and things such as rates are added downstream by billing software). The ability to add custom fields to CDR is very useful, but be careful not to use your call records to replace a proper billing platform. Best to keep your CDR clean and do further processing downstream.

Database Integration of ACD Queues

With a Call Center (often referred to as ACD queues), it can be very useful to be able to allow adjustment of queue parameters without having to edit and reload configuration files. Management of a call center can be a complex task, and allowing for simpler adjustment of parameters can make everyone’s life a whole lot easier.

The queues themselves we’ve already placed in the database in Chapter 12. If, however, you also want to store dialplan parameters relating to your queues, the database can do that too.

[Syntax]
Queue(queuename[,options[,URL[,announceoverride[,timeout[,AGI[,macro[,gosub[,
  rule[,position]]]]]]]]])

MySQL> CREATE TABLE `pbx`.`QueueDialplanParameters` (
  `QueueDialplanParametersID` mediumint(8) NOT NULL auto_increment,
  `Description` varchar(128) NOT NULL,
  `QueueID` mediumint(8) unsigned NOT NULL COMMENT 'Pointer to asterisk.queues table',
  `options` varchar(45) default 'n',
  `URL` varchar(256) default NULL,
  `announceoverride` bit(1) default NULL,
  `timeout` varchar(8) default NULL,
  `AGI` varchar(128) default NULL,
  `macro` varchar(128) default NULL,
  `gosub` varchar(128) default NULL,
  `rule` varchar(128) default NULL,
  `position` tinyint(4) default NULL,
  `queue_tableName` varchar(128) NOT NULL,
  PRIMARY KEY  (`QueueDialplanParametersID`)
);

[QUEUE_DETAILS]
prefix=GET
dsn=asterisk
readsql=SELECT * FROM pbx.QueueDialplanParameters
readsql+= WHERE QueueDialplanParametersID='${ARG1}'

exten => s,1,Verbose(1,Call entering queue named ${SomeValidID)
  same => n,Set(QueueParameters=${GET_QUEUE_DETAILS(SomeValidID)})
  same => n,Queue(${QueueParameters})

CREATE TABLE queue_log (
  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  time char(26) default NULL,
  callid varchar(32) NOT NULL default '',
  queuename varchar(32) NOT NULL default '',
  agent varchar(32) NOT NULL default '',
  event varchar(32) NOT NULL default '',
  data1 varchar(100) NOT NULL default '',
  data2 varchar(100) NOT NULL default '',
  data3 varchar(100) NOT NULL default '',
  data4 varchar(100) NOT NULL default '',
  data5 varchar(100) NOT NULL default '',
  PRIMARY KEY (`id`)
);

[settings]
queue_log => odbc,asterisk,queue_log

mysql> select * from queue_log;
+----+----------------------------+----------------------+-----------+
| id | time                       | callid               | queuename |
+----+----------------------------+----------------------+-----------+
|  1 | 2013-01-22 15:07:49.772263 | NONE                 | NONE      |
|  2 | 2013-01-22 15:07:49.809028 | toronto-1358885269.1 | support   |
+----+----------------------------+----------------------+-----------+


+------------------+------------+-------+-------+-------+-------+-------+
| agent            | event      | data1 | data2 | data3 | data4 | data5 |
+------------------+------------+-------+-------+-------+-------+-------+
| NONE             | QUEUESTART |       |       |       |       |       |
| SIP/0000FFFF0001 | ADDMEMBER  |       |       |       |       |       |
+------------------+------------+-------+-------+-------+-------+-------+

Conclusion

In this chapter, you learned about several areas where Asterisk can integrate with a relational database. This is useful for systems where you need to start scaling by clustering multiple Asterisk boxes working with the same centralized information, or when you want to start building external applications to modify information without requiring a reload of the system (i.e., not requiring the modification of flat files).