Creating a registration table in MySQL for asterisk
André Guimarães 2011-07-20
Switzernet
This document describes how to create a table with all the registration requests in MySQL, with the fields, login, IP, port, expiration date and registration date.
Access Asterisk database in MySQL as root or with a user with sufficient privileges to create tables and triggers. Type:
BEGIN;
Past this SQL in the command line or execute it: SQL. This SQL creates a new table location and a trigger to add a new line to it when there’s an update on the sip registration table. It assumes that the asterisk MySQL registration table is named ‘sippeers’. If this is not the name of the table replace it by the correct name.
If it runs without errors type:
COMMIT;
else type
ROLLBACK;
and try again.
# mysql asterisk -uroot -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 122
Server version: 5.0.51a-24+lenny5 (Debian)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS `location`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `location` (
-> `id` int(11) NOT NULL auto_increment,
-> `name` varchar(80) NOT NULL default '',
-> `ipaddr` varchar(15) NOT NULL default '',
-> `port` smallint(5) unsigned NOT NULL default '0',
-> `regrequest` int(11) NOT NULL default '0',
-> `regexpire` int(11) NOT NULL default '0',
-> `regtime` DATETIME NOT NULL default '2011-01-01 00:00:00',
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `id` (`id`),
-> KEY `name` (`name`)
-> ) ENGINE=MyISAM ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)
mysql> DROP TRIGGER IF EXISTS `registration_trigger`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TRIGGER registration_trigger BEFORE UPDATE ON sippeers
-> FOR EACH ROW INSERT INTO location (name, ipaddr, port, regrequest, regexpire, regtime) VALUES (NEW.name, NEW.ipaddr, NEW.port, UNIX_TIMESTAMP(NOW()), NEW.regseconds, NOW());
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
Force a phone to register or wait for one to register by itself. If everything was done correctly the table location in the asterisk’s database should be increasing one line by each successful registration attempt.
Here you’ll find an example location table filled with registrations. In the example there are two phones registering with the same login phone1 (configured to register each 60 s) and another using the login phone2 that registers (each 1800 s). You can noticed in this example that the phone X-Lite with IP address 212.147.8.99 takes an additional 24 seconds to send a new REGISTER after the registration expiration.
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/