How to enable milliseconds precision

By default voipmonitor sniffer service stores CDR data with seconds precision. If you need to get calldate stored with ms precision:

Contents

Database is empty

  • If database is empty, add into sniffer's config (voipmonitor.conf) option time_precision_in_ms=yes and restart sniffer service (it will create or modify columns needed for storing CDRs in milliseconds pecission)

Database is not empty

  • If database contains data you need to additionally to above:

With the GUI

Login to GUI and in tools->check mysql schema, mark all tables with description 'missing support for time accuracy in milliseconds' and click on start upgrade/run sql. (beware that action can take hours/days and the GUI may be unusable until done.

Without the GUI

If you have no GUI you need to ALTER the tables manually:

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;
ALTER TABLE cdr_next modify column calldate datetime(3) not null;
ALTER TABLE cdr_proxy modify column calldate datetime(3) not null;
ALTER TABLE cdr_rtp modify column calldate datetime(3) not null;
ALTER TABLE cdr_dtmf modify column calldate datetime(3) not null;
ALTER TABLE cdr_sipresp modify column calldate datetime(3) not null;
ALTER TABLE cdr_siphistory modify column calldate datetime(3) not null;
ALTER TABLE cdr_tar_part modify column calldate datetime(3) not null;
ALTER TABLE cdr_country_code modify column calldate datetime(3) not null;
ALTER TABLE cdr_sdp modify column calldate datetime(3) not null;
ALTER TABLE cdr_txt modify column calldate datetime(3) not null;
ALTER TABLE cdr_flags modify column calldate datetime(3) not null;
ALTER TABLE message modify column calldate datetime(3) not null;
ALTER TABLE message_proxy modify column calldate datetime(3) not null;
ALTER TABLE message_country_code modify column calldate datetime(3) not null;
ALTER TABLE message_flags modify column calldate datetime(3) not null;
ALTER TABLE register modify column calldate datetime(3) not null;
ALTER TABLE register_state modify column created_at datetime(3) not null;
ALTER TABLE register_failed modify column created_at datetime(3) not null;
ALTER TABLE sip_msg modify column time datetime(3) not null, modify column request_time datetime(3) not null, modify column response_time datetime(3) not null;

Beware, that if you use cdr/messages/subs_notify custom_headers you need to alter also datetime column in tables

cdr_next_1, cdr_next_2 ...,cdr_next_N (column calldate)
messages_next_1,messages_next_2 ...,messages_next_N (column calldate)
sip_msg_next_1,sip_msg_next_2, ...,sip_msg_next_N, (column time)

For example:

ALTER TABLE cdr_next_1 modify column calldate datetime(3) not null;

After db tables altered

After above changes to tables done, you need to restart sniffer service for getting the call data stored with ms precission, and you need to logout/login into GUI to get CDR view in the GUI in ms precision.

Experienced issues during ALTERS

Warning code 1478 InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope.

We experienced on a version 5.5.60 of the mariadb this error when innodb_file_format not set to barracuda was Antelope. The solution was to disable strict_mode and enable the Barracuda as the file format for innodb as suggested in our scale section for [mysql]

sql_mode=NO_ENGINE_SUBSTITUTION
innodb_file_format = barracuda