Joining tables sippeers and sipusers

Created on 111102 by André Guimarães, Switzernet

This document describes the use of sippers and sipusers in Astrad V9 and how to use only one table for this information. Newer versions of asterisk don’t support both sippeers and sipusers tables configured in real time DB, only one table and these differences are going to be phased out. ^[3]

Registration and new calls scenarios in AstradV9

Registration and new calls scenarios after table merger

Asterisk sip type ^[1]

Values for "type" keywords:
peer: A SIP entity to which Asterisk sends calls (a SIP provider for example). If you want a user (extension) to have multiple phones, define an extension that calls two SIP peers. The peer authenticates at registration (a registration is save in the peer).

user: A SIP entity which places calls through Asterisk (A phone which can place calls only). Users authenticate to reach services with their context (to use the defined context user must authenticate on INVITE).

friend: An entity which is both a user and a peer. Used for most desk handsets and other devices. Asterisk will create two objects, one peer and one user, with the same name.

Contexts and NAT in Astrad V9

- table sipusers

This table is used by incoming calls from customers. It defines the context used by them. The context in this table is always fromaccount. The value for NAT is always ‘yes’ in this table

- table sippeers

The context used by incoming calls from vendors or nodes is defined at the context field of table sippeers2. If the peer is not defined (IP unknown or login unknown) it uses context forbidden (which hang-ups the call). The context can take 4 values in this table:

fromhost – for all nodes from ip_customers where is_node is no (all vendors)

forbidden – for all customer accounts, for all multiple multiple_UA that are generated when a call is made to a telephone that has multiple telephones registered with the same account and for all vendors that are not included in table ip_customers. Customer never fall in this context has they already matched in sipusers.

fromnode – for all our nodes (Astrads and porta-sips)

The value for NAT is always ‘yes’ in this table.

How Asterisk matches calls that come in (INVITES) ^[2]:

1. Asterisk checks the SIP From: address username and matches against the name field of devices with type=user (or in table sipuser). Every customer account falls in this category.

2. Asterisk checks the From: address and matches the list of devices with a type=peer (or table sippeers2)

3. Asterisk checks the IP address (and port number) that the INVITE was sent from and matches against any devices with type=peer (or table sippeers2)

Content of the table sippeers2 and sipusers

For a customer account:

In red we see the new required fields for the new asterisk version (they don’t exist in Astrad v9). In blue the common fields in the two tables. The field context is always different in the two tables. In green fields that are updated in asterisk. Brown is the same as green plus red. It defines the context in asterisk where customer calls start.

mysql> select * from sipusers where name = '41215502419'\G

*************************** 1. row ***************************

id: 20695

context: fromaccount

md5secret: 8e82bf5fdf2bb144dc2824283cf40f1c

nat: yes

deny: 176.31.247.50;212.249.15.3;212.249.15.4;212.249.15.5;212.249.15.6;212.249.15.9;213.251.169.218;213.251.174.129;66.234.138.73;82.103.128.3;82.103.133.176;91.121.101.126;91.121.117.76;91.121.121.115;91.121.122.64;91.121.138.5;91.121.142.9;91.121.143.56;91.121.147.45;91.121.151.58;91.121.151.75;91.121.16.79;91.121.167.75;91.121.172.156;91.121.178.108;91.121.205.108;91.121.25.159;91.121.70.119;91.121.75.124;91.121.99.16;94.23.242.200;94.23.50.74

type: user (implicit type on this table)

1 row in set (0.00 sec)

mysql> select * from sippeers2 where name = '41215502419'\G

*************************** 1. row ***************************

id: 13389

context: forbidden

md5secret: 8e82bf5fdf2bb144dc2824283cf40f1c

nat: yes

host: dynamic

insecure: no

ipaddr: 212.147.8.99

port: 60877

regseconds: 1320227118

username: NULL

type: peer (implicit type on this table)

fullcontact:

useragent: X-Lite 4 release 4.1

lastms: 0

regserver: NULL

defaultuser: 4121550241

1 row in set (0.00 sec)

This is the same has:

id: 20695

context: fromaccount

md5secret: 8e82bf5fdf2bb144dc2824283cf40f1c

nat: yes

host: dynamic

insecure: no

ipaddr: 212.147.8.99

port: 60877

regseconds: 1320227118

username: NULL

type: friend

fullcontact:

useragent: X-Lite 4 release 4.1

lastms: 0

regserver: NULL

defaultuser: 4121550241

1 row in set (0.00 sec)

For the customers the field’s type and context should be merged. The context field is set has fromaccount and type is friend (type friend is the same has user plus peer). The fields that are not common will be added to the new table and use the values that were in each of the tables.

Vendors

mysql> select * from sipusers where name='212.249.15.4' limit 1\G

Empty set (0.00 sec)

mysql> select * from sippeers2 where name='212.249.15.4' limit 1\G

*************************** 1. row ***************************

id: 1

host: 212.249.15.4

context: fromhost

insecure: port

ipaddr: NULL

port: 5060

regseconds: NULL

username: NULL

md5secret: NULL

nat: yes

1 row in set (0.00 sec)

For these types of accounts type should be always set as peer and context should be fromhost. No change is needed: do the same as it is done to sippeers. The previously inexistent fields should be NULL.

Our nodes

mysql> select * from sipusers where name='91.121.70.119' limit 1\G

Empty set (0.00 sec)

mysql> select * from sippeers2 where name='91.121.70.119' limit 1\G

*************************** 1. row ***************************

id: 20

host: 91.121.70.119

context: fromnode

insecure: port

ipaddr: NULL

port: 5060

regseconds: NULL

username: NULL

md5secret: NULL

nat: yes

1 row in set (0.00 sec)

For these types of accounts type should be always set as peer and context should be fromnode. No change is needed: do the same as it is done to sippeers. The previously inexistent fields should be NULL.

Other cases

mysql> select * from sipusers where name='212.227.20.185' limit 1\G

Empty set (0.00 sec)

mysql> select * from sippeers2 where name='212.227.20.185' limit 1\G

*************************** 1. row ***************************

id: 13

host: 212.227.20.185

context: forbidden

insecure: no

ipaddr: NULL

port: 5060

regseconds: NULL

username: NULL

md5secret: NULL

useragent: NULL

fullcontact:

lastms: NULL

regserver: NULL

defaultuser: NULL

type: peer

1 row in set (0.00 sec)

mysql> select * from sipusers where name= 'UA1' limit 1\G

Empty set (0.00 sec)

mysql> select * from sippeers2 where name= 'UA1' limit 1\G

*************************** 1. row ***************************

id: 13353

host: 212.147.8.99

context: forbidden

insecure: no

ipaddr: 212.147.8.99

port: 64489

regseconds: NULL

username: NULL

md5secret: NULL

nat: yes

useragent: NULL

fullcontact:

lastms: NULL

regserver: NULL

defaultuser: NULL

type: peer

1 row in set (0.00 sec)

All other cases must have type peer and context forbidden. No change is needed: do the same as it is done to sippeers. The previously inexistent fields should be NULL.