This is the second and final part for my Pinger utility. The most recent up to date code is on my GitHub account. This post is about configuring the MySQL database and data visualisation.

MySQL configuration

For MySQL installation I followed this blog post: https://www.stewright.me/2016/04/install-mysql-server-raspberry-pi/

Also it’s a good idea to set MySQL to autostart when PI reboots: https://unix.stackexchange.com/questions/21314/start-mysql-server-at-boot-for-debian

When finished, we may continue with the database, table and user creation:

CREATE DATABASE pinger;
CREATE USER 'monitor'@'%' IDENTIFIED BY 'your_password_goes_here';
GRANT ALL ON pinger.* TO monitor@'%' IDENTIFIED BY 'your_password_goes_here';

CREATE TABLE `latency` (
  `loc_id` tinyint(4) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `latency` smallint(5) unsigned NULL,
  `lag_flag` boolean NOT NULL,
  PRIMARY KEY (`loc_id`,`ts`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Data visualisation

Once we’ve collected some data, we may proceed with their visualisation. My tool of choice for this is Tableau as it produces sleek and modern-looking reports in a short time-frame. After some drag and dropping I came up with a satisfactory result:

Data are aggregated on the minute level. Average latency gives one an idea about connection trends over time. Maximal latency provides an insight to the connection stability. When connection lags and latency jumps well over 200ms values, one can experience a dropout in the VoIPs or a headshot. Lag detection chart depicts the worst situations when more than 5 request time-out. This results in disconnects and calls dropping.

The setup report may also be found in the Git repository.