Usage visualizations using ReDash

[timothias]

Hi all! I have been wanting more granular control over visualizing usage without tying into a massive billing platform. I realize ASTPP and other platforms automate a lot if this too, but I like dashboards and thought I would share my experiment.

Step 1. Install an instance of ReDash. https://redash.io/ There are paid instances, but I used the self hosted community version. One-click install to AWS, or follow the guides to get it working where you want it. Complete intial setup, and be ready to connect a Data Source. Make note of the IP of the ReDash server.

Step 2. SSH into your FusionPBX box and make some tweaks.(depending in base OS your locations may vary)
in /etc/postgresql/10/main/postgresql.conf uncomment listen_addresses '*'

In ph_hda.conf add the following line: host fusionpbx all REDASHIP/32 md5
Restart postgresql.

Add an entry to your firewall (usually IPTables) to allow port 5234 from your ReDash server. (Highly suggest limiting that port by IP).

Step 3: Connect ReDash to the FusionPBX Database.
In ReDash, go to Add Data Source (follow their docs). Click Postgresql.
Fill in the information for the DB. You can find the DB user and password and port in /etc/fusionpbx/config.php
Click Connect. Resolve any errors.

Step 4: Have Fun with queries and charts.


A couple of useful queries I made:
Month To Date minutes used per extension and domain (excludes local):

SELECT
    SUM (v_xml_cdr.duration) / 60
        AS durtotal,
    v_extensions.extension,
    v_xml_cdr.context
FROM
    v_xml_cdr
JOIN
    v_extensions
        ON v_xml_cdr.extension_uuid = v_extensions.extension_uuid
WHERE
    start_stamp >= date_trunc('month', CURRENT_DATE)
    AND v_xml_cdr.direction <> 'local'
GROUP BY v_xml_cdr.context, v_extensions.extension;

Month to Date Minutes used total (excludes local):

SELECT SUM (duration) / 60 AS durtotal
FROM v_xml_cdr
WHERE direction = 'inbound'
    AND start_stamp >= date_trunc('month', CURRENT_DATE);

Previous Month Outbound minutes (excludes local):

SELECT SUM (duration) / 60 AS durtotal
FROM v_xml_cdr
WHERE direction = 'outbound'
    AND start_stamp >= date_trunc('month', current_date - interval '1' month)
  AND start_stamp < date_trunc('month', current_date);

Example Dashboard (used UUIDs to obfuscate domains):