Get ip address of registered endpoint in Asterisk 13 with realtime (ODBC) PJSIP

Sorry for my english.

I setup Asterisk 13.19.0 on CentOS7 with ODBC and PostgreSQL 9.6. I configured PJSIP realtime connection throught ODBC to PostgreSQL. The is my configuration files:

enabled => yes
dsn => dnsPgSQL
username => asterisk
password => <my_password>
pre-connect => yes
sanitysql => select 1
connect_timeout => 10
share_connections => yes





ps_endpoints => odbc,pgsql
ps_auths => odbc,pgsql
ps_aors => odbc,pgsql
ps_domain_aliases => odbc,pgsql
ps_endpoint_id_ips => odbc,pgsql
ps_contacts => odbc,pgsql
ps_endpoint_id_ips => odbc,pgsql

ODBC connection working well

CLI> odbc show all
ODBC DSN Settings

Name: pgsql
Last connection attempt: 1970-01-01 03:00:00
Number of active connections: 1 (out of 1)

In pjsip.conf file I create static transport to use for all endpoints (static and realtime).

Realtime tables was created in PostgreSQL by alembic script. I populate tables ps_endpoints, ps_aors, ps_auth manualy with some data for test endpoints. My test endpoints can connect and register on asterisk and I have messages in CLI

– Added contact ‘sip:XXX@AAA.BBB.CCC.DDD:45387;transport=UDP;rinstance=0a0d4ddc3e1fe4ea’ to AOR ‘XXX’ with expiration of 60 seconds
== Contact XXX/sip:XXX@AAA.BBB.CCC.DDD:45387;transport=UDP;rinstance=0a0d4ddc3e1fe4ea has been created
== Endpoint XXX is now Reachable
– Contact XXX/sip:XXX@AAA.BBB.CCC.DDD:45387;transport=UDP;rinstance=0a0d4ddc3e1fe4ea is now Unknown. RTT: 0.000 msec
where AAA.BBB.CCC.DDD is IP address of endpoint with name XXX.

Then I do “show contacts” i get

CLI> pjsip show contacts
Contact: <Aor/ContactUri…> <Hash…> <RTT(ms)…>

Contact: XXX/sip:XXX@AAA.BBB.CCC.DDD:45387;transport=UD b00258b3e8 Unknown nan

So all seems works fine.

The question is - There in PostgreSQL data tables i can find IP address of registered endpoints? To be clear - I want to get from data table IP address AAA.BBB.CCC.DDD for endpoint XXX. In Asteris version 11 with chan_sip and realtime odbc, after endpoint registration on asterisk server ip address was updated in fiel ipaddr of sip table. Where can I find such data in Asterisk 13 with realtime pjsip connetion to PostgreSQL thhrought ODBC?

Thanks for any help.

An endpoint itself doesn’t have an IP address or port. An AOR does. You would need to look at the AORs on an endpoint and then query the contacts table for any entries that start with the name of the AOR.

1 Like

Thank You for for very fast response.

I also think so. But table ps_contacts is empy. When I make SQL query “SELECT * FROM ps_contacts;” I get only column header without any row. May be I have some mistake in configuration of realtime engine for contacts tabel and asterisk can not update this table on endpoint registration?

It’s possible. Nothing stands out in your configuration.

Is there some logs there I can check messages about updating table ps_contacts in moment of endpoint registration? Can You give me any clue to direction where to search deeper?

You can do a “database show” command from the CLI. If you see contacts there then it is the sorcery.conf mapping. You should also look at the console output at startup to see if there is anything of note.

On database show I receive

CLI> database show
/dundi/secret : n4LOLAkj8EniX+QfikvdBQ==;4sIeNZ0rFn9XW5N1d9nNbQ==
/dundi/secretexpiry : 1517068317
/pbx/UUID : dcc88a84-2bbd-4ea7-9d32-54bd5caa51a7
and there are no any “contacts”. So as You say it must be mistake in sorcery.conf. Is it righ?

Console output on startup is very long I cant put it here (limit on post length is only 32000 of cheracters, but output have 92000). But I do not see in it any critical error messages. The only warning message that I think can have ony influence is warning in line 3 of this peace of output.

[Jan 31 18:56:25] NOTICE[17472]: res_odbc.c:509 load_odbc_config: Adding ENV var: ODBCSYSINI=/etc
[Jan 31 18:56:25] NOTICE[17472]: res_odbc.c:509 load_odbc_config: Adding ENV var: ODBCINI=/etc/odbc.ini
[Jan 31 18:56:25] WARNING[17472]: res_odbc.c:526 load_odbc_config: The ‘pooling’, ‘shared_connections’, ‘limit’, and ‘idlecheck’ options were replaced by ‘max_connections’. See res_odbc.conf.sample.
[Jan 31 18:56:25] NOTICE[17472]: res_odbc.c:617 load_odbc_config: Registered ODBC class ‘pgsql’ dsn->[dnsPgSQL]
[Jan 31 18:56:25] NOTICE[17472]: res_odbc.c:1089 load_module: res_odbc loaded.

If you don’t see contacts in “database show” and you don’t see them in your actual database I don’t know where they would be…

Will it be appropriate if I report in bugtracker about this issue? What additional information I have to report in bugtracker?

I don’t believe this is a bug, but configuration or something else unique to your environment. You would need to narrow it down further.