Marge d’expiration d’enregistrement SIP

Oussama Hammami, 2011-07-01

Switzernet.com

L’objectif de ce document est de déterminer la marge entre la date d’enregistrement et la date d’expiration de l’enregistrement SIP précèdent.

Procédure MySQL

Il faut modifier la procédure REGISTER dans les serveurs Astrad [html]:

alter table location3 add column `last_modified` timestamp(14) NOT NULL;

drop table if exists`expire_stat`;

CREATE TABLE `expire_stat` (

`username` varchar(80) NOT NULL default '',

`contact_diff` enum('Y','N') default NULL,

`old_expires` datetime default NULL,

`old_last_modified` datetime default NULL,

`expires_duration` INT(10) default 0,

`expires_must_be` datetime default NULL,

`expires_is` datetime default NULL,

`expires_diff` INT(10) default 0,

PRIMARY KEY (`username`)

) ;

DELIMITER |

DROP PROCEDURE IF EXISTS `REGISTER`|

CREATE PROCEDURE `REGISTER`(IN USR VARCHAR(80), IN CON VARCHAR(255), IN EXR DATETIME)

BEGIN

DECLARE TMP1 DATETIME DEFAULT NULL;

DECLARE TMP2 DATETIME DEFAULT NULL;

DECLARE TMP4 DATETIME DEFAULT NULL;

DECLARE TMP3 INT(10) DEFAULT 0;

DECLARE TMP5 INT(10) DEFAULT 0;

IF (SELECT 1 FROM expire_stat WHERE username=USR) IS NULL THEN

SET TMP1=(SELECT expires FROM location3 WHERE username=USR order by last_modified desc limit 1);

SET TMP2=(SELECT last_modified FROM location3 WHERE username=USR order by last_modified desc limit 1);

SET TMP3=TIMESTAMPDIFF(SECOND,TMP2,TMP1);

SET TMP4=ADDDATE(NOW(), INTERVAL TMP3 SECOND);

SET TMP5=TIMESTAMPDIFF(SECOND,TMP4,EXR);

IF TMP1 IS NOT NULL AND TMP2 IS NOT NULL AND TMP2 NOT LIKE '0000-00-00 00:00:00' THEN

IF (SELECT 1 FROM location3 WHERE username=USR AND contact=CON) THEN

INSERT INTO expire_stat VALUES (USR,'N',TMP1,TMP2,TMP3,TMP4,EXR,TMP5);

ELSE

INSERT INTO expire_stat VALUES (USR,'Y',TMP1,TMP2,TMP3,TMP4,EXR,TMP5);

END IF;

IF (SELECT 1 FROM location3 WHERE username=USR AND contact=CON) THEN

UPDATE location3 SET expires=EXR WHERE username=USR AND contact=CON;

ELSE

INSERT INTO location3 (username,contact,expires) VALUES (USR,CON,EXR);

END IF;

END |

DELIMITER ;

Avec cette procédure on calculera la durée d’expiration de l’enregistrement SIP d’un client donnée (différence entre last_modified et expires), la valeur de l’expiration du prochain enregistrement (expires_must_be) et la différence entre ce dernier avec l’expiration donnée par Asterisk (expires_is), ce qui nous donne ‘expires_diff’.

Le champ contact_diff indiquera si le contact utilisé lors du l’enregistrement courant et le même que le précèdent.

Y: le contact (URI) est différent.
N. le téléphone a gardé la même adresse IP et port utilisé lors du dernier enregistrement.

Exemple

mysql> select * from expire_stat;

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

| 41215504126 | N | 2011-07-03 15:19:17 | 2011-07-03 15:16:47 | 150 | 2011-07-03 15:21:42 | 2011-07-03 15:21:42 | 0 |

| 41215500329 | N | 2011-07-03 15:20:20 | 2011-07-03 15:17:50 | 150 | 2011-07-03 15:22:45 | 2011-07-03 15:22:45 | 0 |

| 41215507392 | N | 2011-07-03 15:20:45 | 2011-07-03 15:18:15 | 150 | 2011-07-03 15:22:55 | 2011-07-03 15:22:55 | 0 |

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

Observation

On a lancé ce statistique sur :

Astrad8 (246 clients)

mysql> select count(distinct(username)) from location3;

+---------------------------+

| count(distinct(username)) |

+---------------------------+

| 246 |

+---------------------------+

Astrad11 (297 clients)

mysql> select count(distinct(username)) from location3;

+---------------------------+

| count(distinct(username)) |

+---------------------------+

| 297 |

+---------------------------+

1 row in set (0.00 sec)

Astrad12 (335 clients)

mysql> select count(distinct(username)) from location3;

+---------------------------+

| count(distinct(username)) |

+---------------------------+

| 335 |

+---------------------------+

1 row in set (0.00 sec)

Résultat de ce teste : [xls-protected]

Astrad8

mysql> select * from expire_stat where expires_diff<>0;

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

| 4121550XXXX | N | 2011-07-03 15:21:26 | 2011-07-03 15:20:27 | 59 | 2011-07-03 15:22:11 | 2011-07-03 15:22:12 | 1 |

| 4122550XXXX | N | 2011-07-03 15:21:35 | 2011-07-03 15:20:36 | 59 | 2011-07-03 15:22:29 | 2011-07-03 15:22:30 | 1 |

| 4122550XXXX | N | 2011-07-03 15:22:15 | 2011-07-03 15:19:45 | 150 | 2011-07-03 15:24:06 | 2011-07-03 15:24:05 | -1 |

| 4133504XXXX | N | 2011-07-03 15:24:16 | 2011-07-03 15:21:46 | 150 | 2011-07-03 15:24:25 | 2011-07-03 15:24:24 | -1 |

| 4121550XXXX | N | 2011-07-03 15:22:24 | 2011-07-03 15:19:55 | 149 | 2011-07-03 15:24:34 | 2011-07-03 15:24:35 | 1 |

| 4122550XXX1 | N | 2011-07-03 15:23:41 | 2011-07-03 15:21:41 | 120 | 2011-07-03 15:24:45 | 2011-07-03 15:25:15 | 30 |

| 4121550XXXX | N | 2011-07-03 15:23:28 | 2011-07-03 15:20:58 | 150 | 2011-07-03 15:25:53 | 2011-07-03 15:25:52 | -1 |

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

Ce client 4122550XXX1 utilise deux téléphones SIP

mysql> select * from location3 where username=4122550XXX1;

+-------------+-----------------------------------+---------------------+

| username | contact | expires |

+-------------+-----------------------------------+---------------------+

| 4122550XXX1 | sip:41225500717@88.85.25.21:5060 | 2011-07-03 16:17:46 |

| 4122550XXX1 | sip:41225500717@88.85.25.21:55012 | 2011-07-03 16:19:12 |

+-------------+-----------------------------------+---------------------+

Recherché des clients avec plusieurs enregistrements dans location3

mysql> select username,count(*) from location3 group by username having count(*)>1;

+-------------+----------+

| username | count(*) |

+-------------+----------+

| 4122550XXX1 | 2 |

| 4121550XXX2 | 2 |

+-------------+----------+

2 rows in set (0.00 sec)

Ce client 4121550XXX2 a changé le port

mysql> select * from location3 where username=4121550XXX2;

+-------------+------------------------------------+---------------------+

| username | contact | expires |

+-------------+------------------------------------+---------------------+

| 4121550XXX2 | sip:4121550XXX2@144.85.226.4:44247 | 2011-07-03 16:24:44 |

| 4121550XXX2 | sip:4121550XXX2@144.85.226.4:47016 | 2011-07-03 16:22:17 |

+-------------+------------------------------------+---------------------+

2 rows in set (0.00 sec)

Vérification dans la table expire_stat

mysql> select * from expire_stat where username=41215501625;

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

| 41215501625 | Y | 2011-07-03 15:33:14 | 2011-07-03 15:30:44 | 150 | 2011-07-03 15:35:41 | 2011-07-03 15:35:41 | 0 |

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

Astrad11

mysql> select * from expire_stat where expires_diff<>0;

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

| 4121550XXXX | N | 2011-07-03 15:32:45 | 2011-07-03 15:30:15 | 150 | 2011-07-03 15:35:13 | 2011-07-03 15:35:12 | -1 |

| 4124504XXXX | N | 2011-07-03 15:33:18 | 2011-07-03 15:30:48 | 150 | 2011-07-03 15:35:43 | 2011-07-03 15:35:42 | -1 |

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

Astrad12

mysql> select * from expire_stat where expires_diff<>0;

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

| 4121550XXXX | N | 2011-07-03 15:30:53 | 2011-07-03 15:29:53 | 60 | 2011-07-03 15:31:22 | 2011-07-03 15:31:21 | -1 |

| 4181509XXXX | N | 2011-07-03 15:32:11 | 2011-07-03 15:29:42 | 149 | 2011-07-03 15:34:35 | 2011-07-03 15:34:36 | 1 |

| 4121550XXXX | N | 2011-07-03 15:32:46 | 2011-07-03 15:30:16 | 150 | 2011-07-03 15:35:11 | 2011-07-03 15:35:10 | -1 |

| 4121550XXXX | N | 2011-07-03 15:32:49 | 2011-07-03 15:30:19 | 150 | 2011-07-03 15:35:14 | 2011-07-03 15:35:13 | -1 |

| 4121550XXXX | N | 2011-07-03 15:32:57 | 2011-07-03 15:30:27 | 150 | 2011-07-03 15:35:22 | 2011-07-03 15:35:21 | -1 |

| 4121550XXXX | N | 2011-07-03 15:33:15 | 2011-07-03 15:30:45 | 150 | 2011-07-03 15:35:43 | 2011-07-03 15:35:42 | -1 |

+-------------+--------------+---------------------+---------------------+------------------+---------------------+---------------------+--------------+

Référence

Master Mysql Astrad DBA V002 (V003 mise à jour)

http://switzernet.com/3/public/110524-db3/