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
In ph_hda.conf add the following line:
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):
Month to Date Minutes used total (excludes local):
Previous Month Outbound minutes (excludes local):
Example Dashboard (used UUIDs to obfuscate domains):
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 md5Restart 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):
SQL:
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):
SQL:
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):
SQL:
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):
