Forcer la durée d’expiration d’enregistrement SIP dans Astrad

Oussama Hammami, 2011-06-29

Switzernet

Lors de l’enregistrement d’un téléphone SIP (REGISTER), l’Astrad garde l’adresse IP et le Port (URI) de ce dernier dans sa base de données (table MySQL sipppeer), l’information remonte jusqu’au serveur de routage et de cette manière tous les appels entrant vers ce client seront routés vers l’Asterisk en question. Ce dernier utilisera l’IP et le port utilisés par le téléphone lors du dernier enregistrement pour router les appels entrants de ce client mais il arrive que le routeur NAT du client libère ce port et par conséquent la transformation inverse pour renvoyer les paquets SIP vers l’IP locale devient impossible.

Pour résoudre ce problème, une solution très simple consiste à forcer la durée d’enregistrement à une valeur plus petite que 3 minutes, ce qui est possible avec Asterisk grâce aux options maxexpiry, minexpiry, defautlexpiry dans le fichier sip.conf [eml]

Configuration

http://www.asterisk.org/doxygen/trunk/Config_sip.html

;maxexpiry=3600 ; Maximum allowed time of incoming registrations

; and subscriptions (seconds)

;minexpiry=60 ; Minimum length of registrations/subscriptions (default 60)

;defaultexpiry=120 ; Default length of incoming/outgoing registration

Ces options sont des options globales à ajouter dans sip.conf

[general]

realm=your_realm

canreinvite=no

context=forbidden

rtptimeout=10

qualify=no

defaultexpiry=180

minexpiry=30

maxexpiry=600

disallow=all

allow=ulaw

allow=alaw

allow=g729

allow=g723

allow=g726

...

Enregistrement SIP sans les options expire:

astrad:/etc/asterisk# ngrep -pql -W byline "412155003XX" port 5060

interface: eth0 (94.23.225.0/255.255.255.0)

filter: (ip or ip6) and ( port 5060 )

match: 412155003XX

U 81.62.151.249:63374 -> 94.23.225.212:5060

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-d7c318d7.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=9a606b84d9a22060o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41398 REGISTER.

Max-Forwards: 70.

Contact: "412155003XX" <sip:412155003XX@192.168.1.121:5060>;expires=3600.

User-Agent: Linksys/SPA921-5.1.8.

Content-Length: 0.

Allow: ACK, BYE, CANCEL, INFO, INVITE, NOTIFY, OPTIONS, REFER.

Supported: replaces.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 100 Trying.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-d7c318d7;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=9a606b84d9a22060o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41398 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

Contact: <sip:412155003XX@94.23.225.212>.

Content-Length: 0.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 401 Unauthorized.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-d7c318d7;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=9a606b84d9a22060o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=as50256df4.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41398 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

WWW-Authenticate: Digest algorithm=MD5, realm="switzernet", nonce="68f5dce3".

Content-Length: 0.

U 81.62.151.249:63374 -> 94.23.225.212:5060

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-8aba97f6.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=9a606b84d9a22060o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41399 REGISTER.

Max-Forwards: 70.

Authorization: Digest username="412155003XX",realm="switzernet",nonce="68f5dce3",uri="sip:astrad.switzernet.com",algorithm=MD5,response="f80077ed00790c9cbcebc9b6085cdb28".

Contact: "412155003XX" <sip:412155003XX@192.168.1.121:5060>;expires=3600.

User-Agent: Linksys/SPA921-5.1.8.

Content-Length: 0.

Allow: ACK, BYE, CANCEL, INFO, INVITE, NOTIFY, OPTIONS, REFER.

Supported: replaces.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 100 Trying.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-8aba97f6;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=9a606b84d9a22060o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41399 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

Contact: <sip:412155003XX@94.23.225.212>.

Content-Length: 0.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 200 OK.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-8aba97f6;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=9a606b84d9a22060o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=as50256df4.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41399 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

Expires: 3600.

Contact: <sip:412155003XX@192.168.1.121:5060>;expires=3600.

Date: Wed, 08 Jun 2011 16:23:23 GMT.

Content-Length: 0.

Enregistrement SIP avec les options expire:

astrad:/etc/asterisk# ngrep -pql -W byline "412155003XX" port 5060

interface: eth0 (94.23.225.0/255.255.255.0)

filter: (ip or ip6) and ( port 5060 )

match: 412155003XX

U 81.62.151.249:63374 -> 94.23.225.212:5060

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-e33b8d3a.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=5f0e585cddb55969o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41402 REGISTER.

Max-Forwards: 70.

Contact: "412155003XX" <sip:412155003XX@192.168.1.121:5060>;expires=3600.

User-Agent: Linksys/SPA921-5.1.8.

Content-Length: 0.

Allow: ACK, BYE, CANCEL, INFO, INVITE, NOTIFY, OPTIONS, REFER.

Supported: replaces.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 100 Trying.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-e33b8d3a;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=5f0e585cddb55969o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41402 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

Contact: <sip:412155003XX@94.23.225.212>.

Content-Length: 0.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 401 Unauthorized.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-e33b8d3a;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=5f0e585cddb55969o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=as0c9edba9.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41402 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

WWW-Authenticate: Digest algorithm=MD5, realm="switzernet", nonce="27bdc244".

Content-Length: 0.

U 81.62.151.249:63374 -> 94.23.225.212:5060

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-b9f02f4.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=5f0e585cddb55969o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41403 REGISTER.

Max-Forwards: 70.

Authorization: Digest username="412155003XX",realm="switzernet",nonce="27bdc244",uri="sip:astrad.switzernet.com",algorithm=MD5,response="71b6a7a970bb711b5512f49d1c73db0a".

Contact: "412155003XX" <sip:412155003XX@192.168.1.121:5060>;expires=3600.

User-Agent: Linksys/SPA921-5.1.8.

Content-Length: 0.

Allow: ACK, BYE, CANCEL, INFO, INVITE, NOTIFY, OPTIONS, REFER.

Supported: replaces.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 100 Trying.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-b9f02f4;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=5f0e585cddb55969o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41403 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

Contact: <sip:412155003XX@94.23.225.212>.

Content-Length: 0.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 200 OK.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-b9f02f4;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=5f0e585cddb55969o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=as0c9edba9.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41403 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

Expires: 600.

Contact: <sip:412155003XX@192.168.1.121:5060>;expires=600.

Date: Wed, 08 Jun 2011 16:27:21 GMT.

Content-Length: 0.

Enregistrement SIP avec les options expire dans le profile d’un compte:

Ces options sont des options globales mais essayons de les tester en les mettant dans le profile d’un compte.

sip.conf

[412155003XX]

type=friend

context=fromaccount

host=dynamic

nat=yes

qualify=no

md5secret=7x2dx5f1d98b6x7f3483xef75b1xfcbx

defaultexpiry=180

minexpiry=30

maxexpiry=600

astrad:/etc/asterisk# ngrep -pql -W byline "412155003XX" port 5060

interface: eth0 (94.23.225.0/255.255.255.0)

filter: (ip or ip6) and ( port 5060 )

match: 412155003XX

U 81.62.151.249:63374 -> 94.23.225.212:5060

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-bd734d62.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=cb9f465bddfdab76o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41400 REGISTER.

Max-Forwards: 70.

Contact: "412155003XX" <sip:412155003XX@192.168.1.121:5060>;expires=3600.

User-Agent: Linksys/SPA921-5.1.8.

Content-Length: 0.

Allow: ACK, BYE, CANCEL, INFO, INVITE, NOTIFY, OPTIONS, REFER.

Supported: replaces.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 100 Trying.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-bd734d62;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=cb9f465bddfdab76o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41400 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

Contact: <sip:412155003XX@94.23.225.212>.

Content-Length: 0.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 401 Unauthorized.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-bd734d62;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=cb9f465bddfdab76o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=as32304e74.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41400 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

WWW-Authenticate: Digest algorithm=MD5, realm="switzernet", nonce="3388b3d4".

Content-Length: 0.

U 81.62.151.249:63374 -> 94.23.225.212:5060

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-d49c2bac.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=cb9f465bddfdab76o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41401 REGISTER.

Max-Forwards: 70.

Authorization: Digest username="412155003XX",realm="switzernet",nonce="3388b3d4",uri="sip:astrad.switzernet.com",algorithm=MD5,response="f876b135c4b83b23e35419ea48ab9d24".

Contact: "412155003XX" <sip:412155003XX@192.168.1.121:5060>;expires=3600.

User-Agent: Linksys/SPA921-5.1.8.

Content-Length: 0.

Allow: ACK, BYE, CANCEL, INFO, INVITE, NOTIFY, OPTIONS, REFER.

Supported: replaces.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 100 Trying.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-d49c2bac;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=cb9f465bddfdab76o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41401 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

Contact: <sip:412155003XX@94.23.225.212>.

Content-Length: 0.

U 94.23.225.212:5060 -> 81.62.151.249:63374

SIP/2.0 200 OK.

Via: SIP/2.0/UDP 192.168.1.121:5060;branch=z9hG4bK-d49c2bac;received=81.62.151.249.

From: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=cb9f465bddfdab76o0.

To: "412155003XX" <sip:412155003XX@astrad.switzernet.com>;tag=as32304e74.

Call-ID: 49e2dc5a-43f373ee@192.168.1.121.

CSeq: 41401 REGISTER.

User-Agent: Asterisk PBX.

Allow: INVITE, ACK, CANCEL, OPTIONS, BYE, REFER, SUBSCRIBE, NOTIFY.

Supported: replaces.

Expires: 3600.

Contact: <sip:412155003XX@192.168.1.121:5060>;expires=3600.

Date: Wed, 08 Jun 2011 16:24:45 GMT.

Content-Length: 0.

L’Asterisk ignore ces options si elles ne sont pas définies dans la session globale et on ne peut alors pas personnaliser ces options pour chaque client.

Observation

Ceci est un premier test pour l'activation du forçage de la valeur d'expiration de l’enregistrement du compte 41215500327.

On a enregistré ce téléphone sur tous les serveurs Astrad avec une valeur d'expiration de 3600s [eml]

mysql> select username,domain,last_modified,expires,TIMESTAMPDIFF(SECOND,last_modified,expires) from location where username in (41215500327) order by last_modified desc;

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

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

| 41215500327 | 91.121.XX.XX | 2011-06-24 16:13:40 | 2011-06-24 16:16:09 | 149 |

| 41215500327 | 91.121.XX.XX | 2011-06-24 16:13:25 | 2011-06-24 16:15:55 | 150 |

| 41215500327 | 91.121.XX.XX | 2011-06-24 16:13:10 | 2011-06-24 16:15:40 | 150 |

| 41215500327 | 91.121.XX.XX | 2011-06-24 16:12:58 | 2011-06-24 16:15:27 | 149 |

| 41215500327 | 91.121.XX.XX | 2011-06-24 16:12:44 | 2011-06-24 16:15:13 | 149 |

| 41215500327 | 91.121.XX.XX | 2011-06-24 16:12:31 | 2011-06-24 16:15:00 | 149 |

| 41215500327 | 91.121.XX.XX | 2011-06-24 16:12:18 | 2011-06-24 16:14:47 | 149 |

| 41215500327 | 91.121.XX.XX | 2011-06-24 16:12:04 | 2011-06-24 16:14:34 | 150 |

| 41215500327 | 91.121.XX.XX | 2011-06-24 16:11:50 | 2011-06-24 16:14:20 | 150 |

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

10 rows in set (0.01 sec)

La procédure suivante cherche la liste des clients qui changent de port sans que l’adresse IP change:

delimiter |

DROP PROCEDURE IF EXISTS `Active_Notify`|

CREATE PROCEDURE Active_Notify( IN ST datetime, IN SP datetime)

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE USR VARCHAR(11);

DECLARE cur CURSOR FOR select distinct(username) from location2_history where count=1 and start>ST and stop<SP group by username,domain having count(distinct(port))=count(username) and count(username)>1 and count(distinct(ipaddr))=1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DROP TABLE IF EXISTS tempo;

CREATE TEMPORARY TABLE tempo (

`username` VARCHAR(11),

PRIMARY KEY (`username`)

);

OPEN cur;

read_loop: LOOP

FETCH cur INTO USR;

IF done THEN

LEAVE read_loop;

END IF;

IF (select username from location2_history where username=USR and start>ST and stop<SP group by username,count having count(distinct(ipaddr))=1 and count(distinct(port))=SUM(count) and count(*)=SUM(count)) THEN

INSERT INTO tempo values (USR);

END IF;

END LOOP;

CLOSE cur;

SELECT * FROM tempo;

DELETE FROM tempo;

END|

delimiter ;

Nombre des clients changeants le port sans que IP change

Avant l’activation du forçage de la durée d’expiration

mysql> CALL Active_Notify('2011-06-23 00:00:00','2011-06-24 00:00:00');

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

| username |

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

| 41215500XXX |

| ... |

| 41215500XXX |

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

51 rows in set (0.61 sec)

Après l’activation du forçage de la durée d’expiration

mysql> CALL Active_Notify('2011-06-28 00:00:00','2011-06-29 00:00:00');

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

| username |

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

| 41215500XXX |

| ... |

| 41215500XXX |

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

15 rows in set (0.30 sec)

Résultat de l’exécution de la procédure 'Active_Notify' après l'activation du forçage de l'expire : (nombre de clients qui risquent d’avoir un problème de port mapping) [eml]

From	To	Servers	Customers
2011-06-24 10:00	2011-06-24 13:00	DB3	72
2011-06-24 13:00	2011-06-24 16:00	DB3	10
2011-06-24 10:00	2011-06-24 13:00	DB4	128
2011-06-24 13:00	2011-06-24 16:00	DB4	13

La procédure MySQL utilisée pour obtenir le nombre de clients qui changent de port à chaque enregistrement est StatReg dont le code est [eml] [zip].

Cette dernière est basée sur la table location_history qui a été ajoutée avec la version 3 de DBA [DBA-V3]

Son algorithme est [eml] :

Assume we have a period from A to B.

For each account

Count records

Where

Start < B and Stop > A (meaning any intersection)

If count = 1

Then the account does not loose its port

Else the telephone looses the connection

Résultat d’exécution de cette procédure durant l’activation du forçage de la durée d’expiration

mysql> CALL StatReg('91.121.XX.XX','2011-06-24 09:00:00','2011-06-24 15:30:00',30);

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

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

| 2011-06-24 09:00:00 | 2011-06-24 09:30:00 | 286 | 55 |

| 2011-06-24 09:30:00 | 2011-06-24 10:00:00 | 293 | 54 |

| 2011-06-24 10:00:00 | 2011-06-24 10:30:00 | 284 | 60 |

| 2011-06-24 10:30:00 | 2011-06-24 11:00:00 | 291 | 57 |

| 2011-06-24 11:00:00 | 2011-06-24 11:30:00 | 286 | 58 |

| 2011-06-24 11:30:00 | 2011-06-24 12:00:00 | 290 | 58 |

| 2011-06-24 12:00:00 | 2011-06-24 12:30:00 | 286 | 60 |

| 2011-06-24 12:30:00 | 2011-06-24 13:00:00 | 326 | 23 |

| 2011-06-24 13:00:00 | 2011-06-24 13:30:00 | 328 | 24 |

| 2011-06-24 13:30:00 | 2011-06-24 14:00:00 | 330 | 23 |

| 2011-06-24 14:00:00 | 2011-06-24 14:30:00 | 333 | 19 |

| 2011-06-24 14:30:00 | 2011-06-24 15:00:00 | 329 | 23 |

| 2011-06-24 15:00:00 | 2011-06-24 15:30:00 | 330 | 23 |

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

13 rows in set (17.36 sec)

Durée d’enregistrement SIP clients par clients

Afin de valider le fonctionnement du forçage de la durée d’enregistrement, nous avons ajouté la table stat_expire dont l’objectif est de loguer la date des 3 premiers enregistrements

CREATE TABLE `stat_expire` (

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

`t1` datetime default NULL,

`t2` datetime default NULL,

`t3` datetime default NULL,

`cont` INT(5) unsigned default 1,

PRIMARY KEY (`username`)

) ;

Cette procédure insert ou met à jour la date d’enregistrement d’un client donné.

DELIMITER |

DROP PROCEDURE IF EXISTS `INS_EXP`|

CREATE PROCEDURE `INS_EXP`(IN USR VARCHAR(80))

BEGIN

DECLARE NB_ INT(5) default 0;

SET NB_=(SELECT cont FROM stat_expire WHERE username=USR);

IF (NB_) THEN

IF (NB_=2) THEN

UPDATE stat_expire SET t2=NOW(),cont=cont+1 WHERE username=USR and t1<>NOW();

ELSEIF (NB_=3) THEN

UPDATE stat_expire SET t3=NOW(),cont=cont+1 WHERE username=USR and t2<>NOW();

END IF;

ELSE

INSERT INTO stat_expire (username,cont,t1) value (USR,2,NOW());

END IF;

END |

DELIMITER ;

Lors d’un enregistrement réussi l’Asterisk exécute la procédure REGISTER dans son serveur DBA, et c’est à cet instant qu’il faut garder la date et le compte dans la table stat_expire

DELIMITER |

DROP PROCEDURE IF EXISTS `REGISTER`|

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

BEGIN

DECLARE MAXD INT(5) DEFAULT 0;

DECLARE MAXI INT(5) DEFAULT 0;

IF (SELECT 1 FROM location2 WHERE username=USR AND contact=CON AND domain=DOM) THEN

UPDATE location2 SET expires=EXR WHERE username=USR AND contact=CON AND domain=DOM;

ELSE

INSERT INTO location2 VALUES (USR,DOM,CON,EXR);

END IF;

INSERT INTO location2_history (start,stop,domain,username,ipaddr,port,count) VALUES (NOW(),NOW(),DOM,USR,SUBSTRING_INDEX(SUBSTRING(CON,LOCATE('@', CON)+1), ':', 1),SUBSTRING_INDEX(SUBSTRING(CON,LOCATE('@', CON)+1), ':', -1),1) ON DUPLICATE KEY UPDATE count=count+1, stop=NOW();

SET MAXI=(SELECT value FROM config WHERE name='max_reg_to_check');

IF (SELECT value FROM config WHERE name='tmp_reg_to_check') < MAXI THEN

UPDATE config SET value=value+1 WHERE name='tmp_reg_to_check';

ELSE

UPDATE config SET value=1 WHERE name='tmp_reg_to_check';

SET MAXD=(SELECT value FROM config WHERE name='day_log_history');

DELETE FROM location2_history WHERE stop < DATE_ADD(NOW(), INTERVAL -MAXD DAY);

END IF;

CALL INS_EXP(USR);

END |

DELIMITER ;

Exemple avec le compte 41215500327

mysql> SELECT username,t1,t2,TIMESTAMPDIFF(SECOND,t1,t2) as INTERVAL1,TIMESTAMPDIFF(SECOND,t2,t3) as INTERVAL2 from stat_expire where username=41215500327;

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

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

| 41215500327 | 2011-06-24 17:02:43 | 2011-06-24 17:05:09 | 146 | 148 |

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

1 row in set (0.00 sec)

Nombre des clients qui ont respecté la valeur d’expiration dans db3 (96.7%)

mysql> (SELECT count(username) from stat_expire where TIMESTAMPDIFF(SECOND,t1,t2)<150 and TIMESTAMPDIFF(SECOND,t2,t3)<=160) UNION (SELECT count(username) from stat_expire);

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

| count(username) |

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

| 968 |

| 1001 |

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

2 rows in set (0.00 sec)

Nombre des clients qui ont respecté la valeur d’expiration dans db4 (98.1%)

mysql> (SELECT count(username) from stat_expire where TIMESTAMPDIFF(SECOND,t1,t2)<150 and TIMESTAMPDIFF(SECOND,t2,t3)<=160) UNION (SELECT count(username) from stat_expire);

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

| count(username) |

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

| 996 |

| 1015 |

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

2 rows in set (0.01 sec)

Utilisation CPU

L’activation de ces options augmentera la fréquence d’enregistrement de tous les téléphones SIP, par conséquent on doit remarquer une légère augmentation dans l’utilisation CPU d’Astrad ainsi qu’une augmentation du volume du trafic MySQL sur DBA.

Pour plus d’information sur le fonctionnement des serveurs Astrad & DBA http://switzernet.com/3/public/110524-db3/

Ci-dessous l’utilisation CPU du serveur SIP astrad12 avant (2011-06-24) et après (2011-06-26) l’activation du forçage de la durée d’expiration de 02 :00 à 05 :00

Ci-dessous le graphe qui montre une augmentation du trafic MySQL sur DBA (db4.switzernet.com) au moment de l’activation du forçage de la valeur d’expiration des enregistrements SIP.