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.
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.