So I have decided to make public the most useful SQL queries I have developed while dealing with FusionPBX. Please note that I am a MariaDB user, I have no intention to translate to PostgreSQL for free. Remember to change them to fit your specific needs.
Since many crapy carriers have been starting to charge for short calls, it is important to monitor and know this. This query will give you an insight into this:
SELECT accountcode, AVG(billsec) AS average, SUM(CASE WHEN billsec = 0 THEN 1 ELSE 0 END) AS zero_calls, SUM(CASE WHEN billsec BETWEEN 1 AND 6 THEN 1 ELSE 0 END) AS short_calls, SUM(CASE WHEN billsec BETWEEN 1 AND 60 THEN 1 ELSE 0 END) AS medium_calls, SUM(CASE WHEN billsec > 6 THEN 1 ELSE 0 END) AS long_calls, SUM(CASE WHEN billsec > 0 THEN 1 ELSE 0 END) AS total_answered_calls, SUM(1) AS total_calls FROM v_xml_cdr WHERE start_stamp >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY accountcode;
If you are using more than one carrier you can label the call to have some statistics. I do mark the calls with my LCR for FusionPBX. The following query helps to know some useful information:
SELECT JSON_VALUE(json,'$.variables.lcr_carrier') AS json_carrier, COUNT(*) AS c, SUM(call_sell) AS sold, SUM(call_buy) AS bought FROM v_xml_cdr WHERE start_stamp >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY json_carrier;
This query assumes all the records and carriers are using the same query.
As the VoIP business grows, servers will need more capacity. Knowing how many simultaneous calls you can handle is a very important matter. The following query will give statistics about the simultaneous calls you can handle.
SELECT YEAR(start_stamp) y, MONTH(start_stamp) m, MIN(c), AVG(c), MAX(c), STDDEV(c) FROM (SELECT start_epoch, start_stamp, COUNT(*) AS c FROM v_xml_cdr WHERE start_stamp >= DATE_SUB(NOW(), INTERVAL 30 DAY) AND ((start_epoch BETWEEN start_epoch AND end_epoch) OR (end_epoch BETWEEN start_epoch AND end_epoch)) GROUP BY start_epoch ) a GROUP BY y, m ORDER BY y DESC, m DESC;
ASR and ALOC are two metrics that are used to know if a carrier is failing or if a customer is misbehaving. The following query will give you these metrics:
SELECT accountcode, SUM(CASE WHEN billsec > 0 THEN 1 ELSE 0 END) / sum(1) * 100 AS asr, SUM(billsec) / SUM(CASE WHEN billsec > 0 THEN 1 ELSE 0 end) AS aloc FROM v_xml_cdr WHERE start_stamp >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY accountcode;
If you are using my LCR for FusionPBX, this query gives an insight into whom is your better carrier with some price statistics.
SELECT destination, description, MIN(lcr_rate), AVG(lcr_rate), MAX(lcr_rate), STDDEV(lcr_rate), GROUP_CONCAT(lcr_carrier_name ORDER BY lcr_rate) FROM (SELECT 1809327 as destination, l.description, c.carrier_name as lcr_carrier_name, l.rate as lcr_rate, l.connect_rate as lcr_second_rate FROM v_lcr l JOIN v_carriers c ON l.carrier_uuid=c.carrier_uuid WHERE c.enabled = 'true' AND l.enabled = 'true' AND l.lcr_direction = 'outbound' AND NOW() >= l.date_start AND NOW() < l.date_end AND l.digits = (SELECT MAX(digits) AS max FROM v_lcr lll WHERE lll.enabled='true' AND lll.digits IN ('1809327', '180932', '18093', '1809', '180', '18', '1') AND lll.lcr_direction = 'outbound' AND lll.carrier_uuid = c.carrier_uuid) ORDER BY c.priority ASC, lcr_rate ASC, l.digits DESC, l.date_start DESC) a;
There is not an easy way to query all the destinations at once, especially because of the lack of a function to create the series for the IN clause.
I will add more queries as I create them.
Good luck!blog comments powered by Disqus