Creating a registration history table in MySQL for asterisk
André Guimarães 2011-07-20
Switzernet
This document describes how to create a table with the condensed history of all the registration requests in MySQL, with the fields, login, IP, port, expiration date, number of registrations and first and last registration dates. This document assumes you have a similar configuration to the one described in http://switzernet.com/3/public/110920-Andre-Guimaraes-5exercice.
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_history, a trigger location_trigger and a procedure location_history_function. Each time a new line is added to location the trigger executes the procedure which decides if a new entry should be added to location_history or if the entry should be just updated and the count field incremented.
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_history`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `location_history` (
-> `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',
-> `regexpire` int(11) NOT NULL default '0',
-> `count` int(11) NOT NULL default '0',
-> `start` int(11) NOT NULL default '0',
-> `stop` int(11) NOT NULL default '0',
-> `datestart` DATETIME NOT NULL default '2011-01-01 00:00:00',
-> `datestop` 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.03 sec)
mysql> DROP PROCEDURE IF EXISTS `location_history_function`;
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER $$
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `location_history_function`(location_name varchar(80), location_ipaddr varchar(15), location_port smallint(5) unsigned, location_regrequest int(11), location_regexpire int(11))
-> BEGIN
-> DECLARE lastid int;
-> DECLARE lastip varchar(15);
-> DECLARE lastport smallint(5) unsigned;
-> DECLARE aux integer;
-> SELECT max(id) INTO lastid FROM location_history WHERE name = location_name;
-> SET aux = 0;
-> IF lastid is not NULL THEN
-> SELECT ipaddr INTO lastip FROM location_history WHERE id = lastid;
-> IF lastip = location_ipaddr THEN
-> SELECT port INTO lastport FROM location_history WHERE id = lastid;
-> SET aux = 1;
-> IF lastport = location_port THEN
-> SET aux = 2;
-> UPDATE location_history SET count = count+1, stop = location_regrequest, datestop = DATE_FORMAT(FROM_UNIXTIME(location_regrequest), '%Y-%m-%d %H:%i:%S'), regexpire = location_regexpire WHERE id = lastid;
-> END IF;
-> END IF;
-> END IF;
-> IF aux != 2 THEN
-> IF location_port != 0 THEN
-> IF location_ipaddr != '' THEN
-> IF location_ipaddr is not NULL THEN
-> INSERT INTO location_history (name, ipaddr, port, regexpire, count, start, stop, datestart, datestop) VALUES (location_name, location_ipaddr, location_port, location_regexpire, 1, location_regrequest, location_regrequest, DATE_FORMAT(FROM_UNIXTIME(location_regrequest), '%Y-%m-%d %H:%i:%S'), DATE_FORMAT(FROM_UNIXTIME(location_regrequest), '%Y-%m-%d %H:%i:%S'));
-> END IF;
-> END IF;
-> END IF;
-> END IF;
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> DROP trigger IF EXISTS `location_trigger`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE trigger `location_trigger` AFTER INSERT ON location
-> FOR EACH ROW CALL location_history_function(NEW.name, NEW.ipaddr, NEW.port, NEW.regrequest, NEW.regexpire);
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 and its respective location_history 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). After some time one of the phones with login phone1 is turned off.
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
http://dev.mysql.com/doc/refman/5.0/en/if-statement.html