Jan 19

Ubuntu 14.04/Asterisk 13: CDR Reporting to MySQL

On Linux, I am familiar with MySQL, and for me it’s the easiest to get going. Asterisk used to include support for MySQL directly (and the config is still there, but not compiled into Asterisk by default anymore), but has since moved to a ODBC structure which offloads the database handling, making it database server agnostic. This is good in the way that it makes writing reports a lot more flexible, but also bad because you have to learn how to configure ODBC also. It’s not as simple as configuring the single ini anymore.

For a few reasons, it is suggested to install/use a MySQL server on another machine. It is safer and more space could be available if there isn’t enough on your Asterisk box. I don’t have that luxury, so I will have the SQL server sit on the server itself for now.

First, install MySQL:

During the install it will ask you for a root user password. Please enter a strong password, but one you will remember, as you will need it later.

Now we will need to use MySQL’s CLI client to set up our databases and tables. We will be calling the database “asterisk”, and the standard for CDR reports is a table called “cdr”. Of course, you can create the table in a separate database if you want.

Make a new file to copy all of the lines we need to make the table that CDR will use.

Now, copy and paste the following into a new file, I called it cdr.sql:

Putting it in /tmp will make the file disappear automatically on reboot. It can really be put anywhere, but this guide assumes that location, so change the path to suit your needs.

Copy and paste the following into the new file:

Now, save and exit. Don’t forget the semicolon on the last line.

Log in to MySQL:

Create a new database:

If you are new to MySQL, every command that completes correctly responds with something similar to:

If not, it will tell you the error. 99% of the time it’s a syntax error, so check for spelling, etc. Also, every command must end with a semicolon.

Now, let’s go into the database and create the table:

It should now say “Database changed.” We can now create the CDR table. This can be done a few ways. You might be able to copy everything below in directly, or you can copy and paste it into a file on your asterisk box in your home directory (or wherever else that’s convenient, like your home directory).

Next, import the table structure we saved to a file earlier:

If there are no errors, then it might say “0 rows affected” even though it actually imported.

Double check and make sure it’s all there:

It should show you 16 rows (it will say how many on the bottom).

Now, let’s create a user for CDR (and CEL):

I used a random password generator site to generate a very long random password. I highly recommend using the longest, hardest, and strongest password you can bear to use to keep your system secure. These passwords will be stored in plain text in the configuration file, so DO NOT use your “normal” passwords. I immediately wrote the password down in a secure password file I have. Don’t lose this password! It will be needed in a few steps.

Now that all that is done, give this user permissions. For security the user will only be able to add or remove data, not tables or the entire database.

Add permissions:

To double check, you can execute the following command and you should see the permissions listed:

Now we are done with the MySQL side. type “exit” to leave the console.

Next we will need to configure unixODBC to connect to MySQL. This will vary slightly based on your installation. The file we are looking for is “libmyodbc.so”. Once we know where the file is, we can edit the odbc.ini file to set up a MySQL connection.

First, find and make a note of where libmyodbc.so file is located:

This usually should return one line. If there is more, look for a path that’s similar to mine:

Do the same for libodbcmyS.so, but without the updatedb command as it’s not needed. Make sure to note both paths.

Edit the /etc/odbcinst.ini to reflect the MySQL setup correctly:

Note: Make sure the [Default] section exists and specifies a driver, otherwise the res_odbc module in Asterisk will bark.

Now, edit the /etc/odbc.ini file (which might be blank) and add the following:

Edit /etc/asterisk/res_odbc.conf to say the following:

Edit /etc/asterisk/cdr_odbc.conf to say the following:

Note: The dsn in cdr_odbc.conf is the dsn specified in res_odbc.conf, not the dsn specified in odbc.ini.

Edit cdr_manager.conf to say the following:

Finally, edit /etc/ cdr_adaptive_odbc.conf to say the following:

NOTE: If you use the sample configs that come with Asterisk, then there is already a couple sections that are similar to this one. I personally backed up the default one, and then emptied it out to only say the above lines. This way, there are no problems. However, if you already have database connection definitions here, make sure to not delete those of course.

Save and exit, and then reload Asterisk:

Now you can make a test call where the other end answers, and then hang up. There should be no CDR errors.

You can do a quick check to make sure the data made it after the call:

If there are no records, double check for configuration errors. “dsn” names are case sensitive, and must match exactly.

Whew! That’s it!


There are some commands that can be used to troubleshoot any issues you might have:

In the asterisk console, using “cdr show status” should get you something similar:

If not, there are some configuration errors somewhere. Your registered backends section might be different, as I have pared mine down to the minimum, but the there should be at least those 3 listed.

In the asterisk console, the command “odbc show all” should look almost exactly like this:

If not, then there is a database connection issue. Check your odbc.ini and odbcinst.ini files to make sure they are correct, that the user/password is correct, and that the user has proper access to the correct database.

Jan 12

Ubuntu 14.04+/Asterisk 13: Securing Asterisk

A default Asterisk install works, but is pretty insecure, leaving it up to the administrator to decided how to secure it that works for them. Below are some suggestions (and things I have done) to secure Asterisk.


This is a pretty simple implementation, and can be done quickly. I have already setup an email relay on my Asterisk box to email me, so you may need to do that before hand or modify the settings slightly. I really enjoy being able to know by email what bad things are happening.

First, modify Asterisk to spit out errors in a separate log file:

Edit /etc/asterisk/logger.conf and:

– Un-comment the first dateformat line under [general]:

– Then, modify the messages line near the bottom and add security:

Restart the Asterisk logger module to make the changes take effect:

Now, install fail2ban:

Add the folowing to the end of /etc/fail2ban/jail.conf:

Then, move the existing asterisk.conf in filter.d to a backup in the directory below (or wherever else you would like):

Create a new asterisk.conf in filter.d and add the following:

Restart fail2ban:


SIP On Asterisk:

Edit sip.conf and add the following under [general]:

Also, change the context line to either an empty context in extensions.conf, or also edit the default extensions.conf and comment out “include => demo” under [public].

Another configuration tip is to not set the extension number as the SIP username.

Jan 12

Ubuntu 14.04+: Set up Postfix to Send to Local Network Relay

I have an internal SMTP mail relay set up on one server, and I have other servers send emails to that relay. I have a pretty simple setup as there is no security to send emails to the relay from inside. This makes the setup on other servers easier (although it’s not as good as it could be security wise).

To set up your server to send to your local SMTP relay, install postfix (and mailutils for the mail command):

Choose “Internet Site” and accept the defaults.

Edit the main.cf config to specify the relay host:

Restart postfix:

Now you can test by sending a quick email to a valid email address:


Jan 11

Asterisk 13: Set Up Local SIP Channels

First, we will create the user/phone account in sip.conf:

This creates a SIP phone called “phone1″, with the username “phone1″, using the password “password1111″. This also links it to the from-internal context in extensions.conf where I have the rest of my extensions. If you want it to go to a different part of the configuration, change that line here.

Now, edit voicemail.conf and add the new mailbox (The default context is quite a ways down):

In the Asterisk console, reload:

In the SIP client, the SIP address is now phone1@asterisk.ip.address. The password is what was set in sip.conf. You should now be able to dial other extensions! In some SIP clients, you may need to turn off/disable sRTP Encryption.

Now, the next thing we could do is make the SIP phone an extension in Asterisk. Edit extensions.conf and add the following to [from-internal] context (or whatever your normal extensions context is):

In the Asterisk console, reload the dialplan:

Your SIP phone is now extension 300!

There are a TON of different options for SIP phones, so this is just the beginning.


Jan 11

Ubuntu 14/Asterisk 13: Setting Up DAHDI

If you have added new hardware to your Asterisk box like FXO/FXS cards, there are a few configuration steps.

Note: If you didn’t compile Asterisk with DAHDI, you will need to do those first. SeeĀ Ubuntu 14.04 Server: Install Asterisk 13 (opens in new tab/window) for Installing Asterisk, and just do the steps for compiling DAHDI and compiling Asterisk.

I would recommend running sudo -i to switch to root for the following, otherwise, add sudo to the beginning of each command.

At this point I would also recommend opening up 2 SSH sessions to your Asterisk box. One to sit on the Asterisk console, and one to edit different files.

First, make sure DAHDI sees the card (if not, troubleshooting is beyond the scope of this article):

Then, generate the new configuration files:

Then, configure the kernel for the installed modules:

You should see your modules show up in the channel map, and a configuration for the mg2 echo canceller.

Restart DAHDI:

Point file chan_dahdi.conf to /etc/asterisk/dahdi-channels.conf:

Restart Asterisk:

Verify everything is working:

If everything goes ok, and you have an FXS channel, you should now get dialtones!

Well that was easy, let’s set up a basic dialplan and a physical extension. I happen to have 2 FXS modules in my machine, so here is an easy example of how to get started with the new hardware:

vi /etc/asterisk/extensions.conf

Scroll all the way down to the bottom and put the following:

Save and exit. Then, reload the dialplan in the Asterisk console:

“But how did you know to use the [from-internal] context??” you may scream at me over the internet. The answer is the dahdi-channels.conf which told me on line 30.

Now you should be able to call extension 100 or 101 and hear the sweet ring of success.




Jan 11

Ubuntu 14.04 Server: Install Asterisk 13

These instructions are a modification of my earlier FreePBX instructions. I ended up not liking FreePBX installed mostly because it makes Asterisk configuration non-standard, and for module compatibility, makes it produce a lot of errors. These errors could be ignored, but my OCD won’t let me personally ignore them. The huge advantage to FreePBX however is the GUI, which makes configuring things a lot easier. The downside to that though is that troubleshooting is a lot harder. Basically it boils down to forcing myself to learn Asterisk the correct way, through the various configuration files.

This assumes you are starting from a clean empty box and you are installing Ubuntu fresh from CD/USB. This is strongly recommended so that there are no other issues. The instructions install a very basic Asterisk install, but gets it ready for databases and other additions.

Note: These instructions are meant to be followed top down. Skipping non-optional sections will have dire consequences.

Install Ubuntu Server 14.04


During the installation process, select the OpenSSH server option during Software Selection. The rest of the needed packages will be installed later. Otherwise, run the setup as normal.

Setting up the new installation:
At this point, I strongly recommend setting up a static IP address for your Asterisk, but this is optional:
See: Ubuntu 14.04: Changing to Static IP (Opens in new Window/Tab)

Make sure DNS works (and you can resolve names outside of your network):

If not, then troubleshoot your network connectivity before continuing.

Update apt, upgrade the system, and install dependencies then reboot (Make sure to scroll over to get the whole command):

Note: Make sure to remember the password you used for the root user for mysql.

After rebooting, log in and switch to the root user so sudo isn’t needed to be run every time:


Optional Asterisk Prerequisites

Google Voice:

Create a fix for Ubuntu to make libgnutls work:

Insert the following into the file:

Save and exit, then make the file executable:

Get, extract, build, and install iksemel:


DAHDI (if you have/will have physical hardware):

Note: You will see a bunch of messages like “Can’t read private key”. These can be ignored and are non-critical.

LIBPRI (if you have/will have physical E1/T1/J1/ISDN cards):

pjproject (if you need PJSIP, which you probably don’t):



Compile and install Asterisk:

You will be prompted at the point to pick which modules to build. Most of them will be enabled, but if you want to have MP3 support, you need to manually turn on ‘format_mp3′ on the first page. Also, select app_meetme if the MeetMe conference bridge is desired.

Selecting ‘Save & Exit’ to continue.

Install Asterisk-Extra-Sounds:
Note that this installs the (8khz) ‘wav’ sound files. If you’re planning on running G722 (High Definition ‘Wideband’) audio, you also want to download the 722 codec pack, which is the second part. If you’re not planning on using Wideband, you can skip that part.

Start Asterisk and enjoy!

Check out the console:

Console with a lot of feedback (very useful for troubleshooting):

Note: If you don’t plan on connecting Asterisk up to LDAP (or don’t know what LDAP is), you can unload that module now and remove some non-critical startup errors:

In /etc/asterisl/modules.conf, add the following to the bottom:

This module is loaded by default, and can be re-loaded when needed by removing or commenting this line.

Jan 10

FreePBX/Asterisk DAHDI_ATTACH_ECHOCAN failed on channel 1

Currently, on a new install of the latest FreePBX, the DAHDI module has some problems with echo cancellation on FXO/FXS cards. This will cause the module to not load correctly. This may be fixed in the future.

DAHDi seems to want to use oslec echo cancellor by default, however it’s not a module that’s normally loaded. There are 2 solutions to this issue. First is to use the MG2 echo cancellor. This is the easiest, however, the oslec echo cancellor is actually better. I will go through both:

Change to MG2 Echo Cancellor:

Change the line:


You’re line may vary based on how many channels you have.

Run the config again with some output, it should work now:


Fix OSLEC Echo Cancellor:

I haven’t been able to get it work yet… stay tuned.

Jan 09

Raspbian: VIM Syntax Highlighting

I always install VIM, it’s easier to use than VI, however for some reason in Raspbian, syntax highlighting is not enabled by default. It’s such a useful feature I don’t know who wouldn’t want it on. Anyway, to enable it:

Remove the quotes from the line: “syntax on

Write and Quit, done!

Jan 03

Raspbian: Going on a Diet (Remove X11)

I recently set up a pi to act as a dedicated headless server with a default install of Raspbian (from NOOBS). It is solely to serve information and a couple services. In that case, there are a lot of things that aren’t needed for a headless server. One of those things (and the biggest) is X11. On a headless server, you don’t need graphical interfaces 99% of the time. I also don’t need sound or printing capability (although you could share a printer with it). Below are some suggestions on freeing up some of that already thin SD card space:

First, it’s important to understand what is installed so you can see what you have, and what might be a candidate for removal:

This will get you a very long list of what’s all installed. It’s great software, but in my case, completely unneeded. X11 has processes running, and everything I didn’t need took up about 2GB of space. Sure, on a desktop hard-drive that’s just a drop in the bucket these days, but SD cards are still cramped for space. Below are just suggestions on what can be removed.

Note: Be sure to scroll to the right, as these commands are very long.


Removing X11:

The second remove line was done before I learned about the better way to remove X11 with the line above, so some of those packages may not exist, but the command will work as normal. I don’t feel like re-installing Raspbian on my pi just to find out what doesn’t need to be in there anymore. The second command serves to remove all of the programs that need X11 to run.

Doing these 2 commands will free up the most space and resources by far. About 2GB will be freed, and several running processes will be stopped, freeing up memory and CPU. All 3 of the most restricted and important resources on a pi.


Removing Extra Multimedia:

I don’t plan on listening to music on my pi server, so all of this was also removed.


Removing Printing:

I don’t plan on doing any printing, or sharing any printers (my printer is networked anyway), so CUPS was removed.


After removing everything:

This will remove any leftover packages (usually mostly libraries) from other stuff that was removed above.

I ran these commands after just to clean up apt-get, so I don’t run into any problems in the future.

After doing all of the above, I ended up with only 879MB of SD card space used, which is fantastic for a still fully functioning and working pi. You will probably end up with even more free space, as I have other stuff installed. Remember, everything you remove can always be put back.

Jan 02

Raspbian: Setting Up Wi-fi

To set up wifi (WPA/WPA2 Only) on the Pi, I was able to get the following working with my setup. Other instructions (including the ones adding the directives to /etc/network/interfaces) didn’t work for me.

I just installed Raspbian on a Pi today (2015-01-02), so these instructions should be current. My /etc/network/interfaces file already had the following in it (if yours doesn’t, then put these lines in so it looks the same):

Then, edit /etc/wpa_supplicant/wpa_supplicant.conf and add the following onto the end of the file:

Then, restart your network interface. I personally use:

It should take a second or two to finish (and mine spits errors). You can check and make sure you have an IP by running ifconfig.

Note: You can also set the pi to have a static IP by changing the “iface default inet dhcp” line to “iface default inet static” and adding the proper information below. See my other post Ubuntu 14.04+: Changing to Static IP for that information.


Thanks to: http://kerneldriver.wordpress.com/2012/10/21/configuring-wpa2-using-wpa_supplicant-on-the-raspberry-pi/

Older posts «