Suggestions

[scharrua]

Hello,

Hope you don't mind, I would like to suggest some additional improvements on VoIPMonitor's datamodel.

• Tables CDR and CDR_NEXT : on MySQL/Mariadb (5.6) the calldate column lacks of granularity. It seems the DateTime object does not contain Milliseconds , but only saves up to Seconds, which leads that there is no way of getting a true list of CDR events ordered by calldate ASC or DESC. In fact, the way it is set nowadays, and when you have special requirements such as multi-leg calls (in my case, from SIP Trunk to Proxy to B2BUA to SIP Trunk) , most of the time the CDRs will not appear in an ordered way (from 1st Leg to last Leg ordered). Ordering by CDR ID is of no use neither as the order is not strict: I do have CDRs of the last LEG C in between LEG A and LEG B when ordering by CDR ID or Calldate....

• there is no direct way to have a call hierarchy: how can one know which CDR originated the next CDR? i could not find a 100% certain/100% sure way of doing it that apart from JOINing CDR_NEXT and CDR with CallDate and filtering by CALLED and CALLER. The problem is that the ORDER is not correct as the CDRs are not saved in order of coming. Having a hierarchical order in CDR_NEXT (for example) will allow anyone to check the full flow a call, from LEG A to LEG X. If anyone has a suggetion on how to do this, I will be happy to order a pizza and a beer and make it delivered to your home! .... as long as you have Uber Eats nearby :)

[voipmonitor]

Hi,

please check: https://www.voipmonitor.org/doc/How_to_enable_milliseconds_precision

and let me know if this solves your problem

[scharrua]

Thanks Martin,

I followed the documentation, added time_precision_in_ms=yes parameter to voipmonitor.conf and executed the ALTER TABLE lines, as requested. After restarting the sniffer, CDRs were still recorded in normal datetime (no milliseconds were added).

select *, cast(calldate as datetime(3)) from cdr_next order by calldate desc limit 10;

[image: image.png] I checked the messages log file (/var/log/messages) and found several Warning lines with content as :

Sep 21 18:25:40 KAM001 voipmonitor[26854]: !!! New feature was added. If you want to use it then you need to alter cdr database table and add new columns t o support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmo nitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE cdr modify column calldate datetime(3) not null, modify column callend datetime(3) no t null, modify column duration decimal(9,3) unsigned default null, modify column connect_duration decimal(9,3) unsigned default null, modify column progress_ti me decimal(9,3) unsigned default null, modify column first_rtp_time decimal(9,3) unsigned default null, modify column a_last_rtp_from_end decimal(9,3) signed default null, modify column b_last_rtp_from_end decimal(9,3) signed default null;

Those Warning lines included all Alter Tables statements that are present in the URL you've shared, and found 2 lines that were not: ALTER TABLE cdr_rtp ADD COLUMN sport smallint unsigned DEFAULT NULL AFTER saddr; ALTER TABLE cdr_rtp ADD COLUMN duration decimal(9,3) DEFAULT NULL;

All others are indeed in the list of statements to execute. However, even after executing those lines, I get the following Warnings:

ALTER TABLE cdr_rtp ADD COLUMN duration decimal(9,3) DEFAULT NULL 43574 row(s) affected, 64 warning(s): 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

it seems that the Voipmonitor schema was created and is being compressed with a INNODB File Format that is not quite the correct one (??).

I'm not sure what are the next steps, without breaking anything (though thiis is only DEV environment....)

Cheers,

Sérgio Charrua

www.voip.pt http://www.voip.pt/ Tel.: +351 <callto:+351+91+104+12+66>21 130 71 77

Email : sergio.charrua@voip.pt sergio.charrua@voip.pt

This message and any files or documents attached are strictly confidential or otherwise legally protected.

It is intended only for the individual or entity named. If you are not the named addressee or have received this email in error, please inform the sender immediately, delete it from your system and do not copy or disclose it or its contents or use it for any purpose. Please also note that transmission cannot be guaranteed to be secure or error-free.

On Mon, Sep 21, 2020 at 3:44 PM Martin Vit notifications@github.com wrote:

Hi,

please check: https://www.voipmonitor.org/doc/How_to_enable_milliseconds_precision

and let me know if this solves your problem

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/voipmonitor/sniffer/issues/69#issuecomment-696162005, or unsubscribe https://github.com/notifications/unsubscribe-auth/AC7BU62K4UVSAYDDANXE3RTSG5RDXANCNFSM4RUMBROQ .

[voipmonitor-petr]

Hello, did you done all the alters manually or in the GUI?

https://www.voipmonitor.org/doc/How_to_enable_milliseconds_precision#Without_the_GUI

[scharrua]

Yes, and all returned some warnings like:

1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. 1478 InnoDB: assuming ROW_FORMAT=COMPACT.

when viewing the table definition, all datetime objects are scaled only up to seconds, no milliseconds are being considered. [image: image.png]

[image: image.png]

Sérgio Charrua

www.voip.pt http://www.voip.pt/ Tel.: +351 <callto:+351+91+104+12+66>21 130 71 77

Email : sergio.charrua@voip.pt sergio.charrua@voip.pt

This message and any files or documents attached are strictly confidential or otherwise legally protected.

It is intended only for the individual or entity named. If you are not the named addressee or have received this email in error, please inform the sender immediately, delete it from your system and do not copy or disclose it or its contents or use it for any purpose. Please also note that transmission cannot be guaranteed to be secure or error-free.

On Tue, Sep 22, 2020 at 12:52 PM voipmonitor-petr notifications@github.com wrote:

Hello, did you done all the alters manually or in the GUI?

https://www.voipmonitor.org/doc/How_to_enable_milliseconds_precision#Without_the_GUI

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/voipmonitor/sniffer/issues/69#issuecomment-696671827, or unsubscribe https://github.com/notifications/unsubscribe-auth/AC7BU64BFIH656FC7LFSMODSHCFXTANCNFSM4RUMBROQ .

[voipmonitor-petr]

The warnings shouldn't be problem,

if you check the calldate column inside cdr table in voipmonitor database what type is it now? what if you check the a_last_rtp_from_end column what type is this?

If you restart the sniffer service are there some errors in syslog/messges or some advices right aftert the service start?

[scharrua]

Calldate column inside CDR Table is set as datetime only:

CREATE TABLE cdr ( ID bigint(20) unsigned NOT NULL AUTO_INCREMENT, calldate datetime NOT NULL, callend datetime NOT NULL, duration mediumint(8) unsigned DEFAULT NULL, connect_duration mediumint(8) unsigned DEFAULT NULL, progress_time mediumint(8) unsigned DEFAULT NULL, first_rtp_time mediumint(8) unsigned DEFAULT NULL, caller varchar(255) DEFAULT NULL, caller_domain varchar(255) DEFAULT NULL, caller_reverse varchar(255) DEFAULT NULL, callername varchar(255) DEFAULT NULL, callername_reverse varchar(255) DEFAULT NULL, called varchar(255) DEFAULT NULL, called_domain varchar(255) DEFAULT NULL, called_reverse varchar(255) DEFAULT NULL,

[ ... many other fields ... ]

a_last_rtp_from_end smallint(5) unsigned DEFAULT NULL, b_last_rtp_from_end smallint(5) unsigned DEFAULT NULL,

[ ..... many other fields..... ] ) ENGINE=InnoDB AUTO_INCREMENT=73521 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED ) ;

As for the logs, after a restart of the sniffer service, I get these lines:

Sep 22 16:38:36 KAM001 voipmonitor[51113]: CONNECTION PROCESS CMD: {"type_connection":"service"} Sep 22 16:38:36 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter cdr database table and add new columns to support time accuracy in mil liseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE cdr modify column calldate datetime(3) not null, modify column callend datetime(3) not null, modify column duration decimal(9,3) unsigned default null, modify column connect_duration decimal(9,3) unsigned default null, modify column progress_time decimal(9,3) unsigned default null, modify column first_rtp_time decimal(9,3) unsigned default null, modify column a_last_rtp_from_end decimal(9,3) signed default null, modify column b_last_rtp_from_end decimal(9,3) signed default null; Sep 22 16:38:36 KAM001 systemd-logind: Removed session 1240141. Sep 22 16:38:37 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter cdr_next database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE cdr_next modify column calldate datetime(3) not null; Sep 22 16:38:37 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter cdr_proxy database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE cdr_proxy modify column calldate datetime(3) not null; Sep 22 16:38:37 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter cdr_rtp database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE cdr_rtp modify column calldate datetime(3) not null; Sep 22 16:38:37 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter cdr_dtmf database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE cdr_dtmf modify column calldate datetime(3) not null; Sep 22 16:38:37 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter cdr_siphistory database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE cdr_siphistory modify column calldate datetime(3) not null; Sep 22 16:38:37 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter cdr_tar_part database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE cdr_tar_part modify column calldate datetime(3) not null; Sep 22 16:38:37 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter cdr_country_code database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE cdr_country_code modify column calldate datetime(3) not null; Sep 22 16:38:38 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter cdr_sdp database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block: Sep 22 16:38:38 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter cdr_flags database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE cdr_flags modify column calldate datetime(3) not null; Sep 22 16:38:38 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter register_state database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE register_state modify column created_at datetime(3) not null; Sep 22 16:38:38 KAM001 voipmonitor[51113]: !!! New feature was added. If you want to use it then you need to alter register_failed database table and add new columns to support time accuracy in milliseconds. This operation can take hours based on ammount of data, CPU and I/O speed of your server. The alter table will prevent the database to insert new rows and will probably block other operations. It is recommended to alter the table in non working hours. Login to the mysql voipmonitor database (mysql -uroot voipmonitor) and run on the CLI> ALTER TABLE register_failed modify column created_at datetime(3) not null;

All the above suggested SQL Statements for ALTER TABLE were executed (and in fact, they are all described in the documentation you shared) but Mysql/MariaDB is throwing Warnings and just ignores the SQL statements. Using MariaDB Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

I tried to change the DB Engine for VoipMonitor database, but my LAB server has about 90% of disk space usage and not enough space to process the change.... but that is another issue.

hope this helps.

Sérgio Charrua

www.voip.pt http://www.voip.pt/ Tel.: +351 <callto:+351+91+104+12+66>21 130 71 77

Email : sergio.charrua@voip.pt sergio.charrua@voip.pt

This message and any files or documents attached are strictly confidential or otherwise legally protected.

It is intended only for the individual or entity named. If you are not the named addressee or have received this email in error, please inform the sender immediately, delete it from your system and do not copy or disclose it or its contents or use it for any purpose. Please also note that transmission cannot be guaranteed to be secure or error-free.

On Tue, Sep 22, 2020 at 3:43 PM voipmonitor-petr notifications@github.com wrote:

The warnings shouldn't be problem,

if you check the calldate column inside cdr table in voipmonitor database what type is it now? what if you check the a_last_rtp_from_end column what type is this?

If you restart the sniffer service are there some errors in syslog/messges or some advices right aftert the service start?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/voipmonitor/sniffer/issues/69#issuecomment-696767869, or unsubscribe https://github.com/notifications/unsubscribe-auth/AC7BU647S62R7Z6IUFSZLBLSHCZY3ANCNFSM4RUMBROQ .

[voipmonitor-petr]

What format is currently used there? show variables like 'innodb_file_format';

https://www.voipmonitor.org/doc/Scaling#MySQL_performance

[scharrua]

Hi!

[image: image.png] Might this be the issue?

Sérgio Charrua

www.voip.pt http://www.voip.pt/ Tel.: +351 <callto:+351+91+104+12+66>21 130 71 77

Email : sergio.charrua@voip.pt sergio.charrua@voip.pt

This message and any files or documents attached are strictly confidential or otherwise legally protected.

It is intended only for the individual or entity named. If you are not the named addressee or have received this email in error, please inform the sender immediately, delete it from your system and do not copy or disclose it or its contents or use it for any purpose. Please also note that transmission cannot be guaranteed to be secure or error-free.

On Tue, Sep 22, 2020 at 5:26 PM voipmonitor-petr notifications@github.com wrote:

What format is currently used there? show variables like 'innodb_file_format';

https://www.voipmonitor.org/doc/Scaling#MySQL_performance

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/voipmonitor/sniffer/issues/69#issuecomment-696832376, or unsubscribe https://github.com/notifications/unsubscribe-auth/AC7BU62CPXR5A5O353NHDODSHDF2LANCNFSM4RUMBROQ .

[voipmonitor-petr]

please send me the image to support email support@voipmonitor.org and into subject write "altering columns for ms precision"

[scharrua]

Another "would-be-nice" feature is a way to reload configuration file without restarting the service, avoiding down times. (at least, I haven't found a way for reload conf file....)

[voipmonitor-petr]

Hello,

this is already possible using the GUI ( when used new type of configuration server in new client/server mode) and in the sniffer instances is option mysqlloadconfig enabled.

More about configuration types and how the new server/client mode can be enabled: https://www.voipmonitor.org/doc/Sniffing_modes#NEW_client.2Fserver_.28aka_remote.2Fcentral.29_sensor_mode

(After you enabled server_bind,server_bind_port,server_password options in /etc/voipmonitor.conf , there will appears in the GUI->settings->sensors The wrench icon next to id_sensor value (local). Click on it and most of the config values can be changed in a runtime without need to restart sniffer service.