Just for completeness, here are the mysql queries that are executed upon clicking "Top 25 Bandwith Consumers":
> 150817 7:25:38 477 Query SELECT LOWER(CONCAT( > SUBSTRING(radacct.callingstationid,1,2),':', > SUBSTRING(radacct.callingstationid,3,2),':', > SUBSTRING(radacct.callingstationid,5,2),':', > SUBSTRING(radacct.callingstationid,7,2),':', > SUBSTRING(radacct.callingstationid,9,2),':', > SUBSTRING(radacct.callingstationid,11,2) > )) as callingstationid, > SUM(radacct_log.acctinputoctets) AS acctinputoctets, > SUM(radacct_log.acctoutputoctets) AS acctoutputoctets, > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) AS > accttotaloctets > FROM radacct_log > LEFT JOIN radacct ON radacct_log.acctsessionid = radacct.acctsessionid > WHERE radacct_log.timestamp BETWEEN '2015-07-18' AND '2015-08-17' > GROUP BY radacct.callingstationid > HAVING radacct.callingstationid IS NOT NULL > ORDER BY accttotaloctets DESC > LIMIT 25 > 477 Query SELECT LOWER(CONCAT( > SUBSTRING(radacct.callingstationid,1,2),':', > SUBSTRING(radacct.callingstationid,3,2),':', > SUBSTRING(radacct.callingstationid,5,2),':', > SUBSTRING(radacct.callingstationid,7,2),':', > SUBSTRING(radacct.callingstationid,9,2),':', > SUBSTRING(radacct.callingstationid,11,2) > )) as callingstationid, > SUM(radacct_log.acctinputoctets) AS acctinputoctets, > SUM(radacct_log.acctoutputoctets) AS acctoutputoctets, > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) AS > accttotaloctets > FROM radacct_log > LEFT JOIN radacct ON radacct_log.acctsessionid = radacct.acctsessionid > WHERE radacct_log.timestamp BETWEEN '2015-07-18' AND '2015-08-17' > GROUP BY radacct.callingstationid > HAVING radacct.callingstationid IS NOT NULL > ORDER BY accttotaloctets DESC > LIMIT 25 > 477 Query SELECT LOWER(CONCAT( > SUBSTRING(radacct.callingstationid,1,2),':', > SUBSTRING(radacct.callingstationid,3,2),':', > SUBSTRING(radacct.callingstationid,5,2),':', > SUBSTRING(radacct.callingstationid,7,2),':', > SUBSTRING(radacct.callingstationid,9,2),':', > SUBSTRING(radacct.callingstationid,11,2) > )) as callingstationid, > SUM(radacct_log.acctinputoctets) AS acctinputoctets, > SUM(radacct_log.acctoutputoctets) AS acctoutputoctets, > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) AS > accttotaloctets > FROM radacct_log > LEFT JOIN radacct ON radacct_log.acctsessionid = radacct.acctsessionid > WHERE radacct_log.timestamp BETWEEN '2015-07-18' AND '2015-08-17' > GROUP BY radacct.callingstationid > HAVING radacct.callingstationid IS NOT NULL > ORDER BY accttotaloctets DESC > LIMIT 25 And for "Bandwith per Operating System": > 470 Query SELECT IFNULL(device_class, 'Unknown > Fingerprint') AS dhcp_fingerprint, > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) AS > accttotaloctets, > ROUND( > > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets)/( > SELECT > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) > FROM radacct_log RIGHT JOIN radacct ON > radacct_log.acctsessionid = radacct.acctsessionid > INNER JOIN node n ON n.mac = LOWER(CONCAT( > SUBSTRING(radacct.callingstationid,1,2),':', > SUBSTRING(radacct.callingstationid,3,2),':', > SUBSTRING(radacct.callingstationid,5,2),':', > SUBSTRING(radacct.callingstationid,7,2),':', > SUBSTRING(radacct.callingstationid,9,2),':', > SUBSTRING(radacct.callingstationid,11,2))) > WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' > )*100,1 > ) AS percent > FROM radacct_log > INNER JOIN radacct ON radacct_log.acctsessionid = > radacct.acctsessionid > INNER JOIN node n ON n.mac = LOWER(CONCAT( > SUBSTRING(radacct.callingstationid,1,2),':', > SUBSTRING(radacct.callingstationid,3,2),':', > SUBSTRING(radacct.callingstationid,5,2),':', > SUBSTRING(radacct.callingstationid,7,2),':', > SUBSTRING(radacct.callingstationid,9,2),':', > SUBSTRING(radacct.callingstationid,11,2)) > ) > WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' > GROUP BY device_class > ORDER BY percent DESC > 470 Query SELECT IFNULL(device_class, 'Unknown > Fingerprint') AS dhcp_fingerprint, > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) AS > accttotaloctets, > ROUND( > > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets)/( > SELECT > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) > FROM radacct_log RIGHT JOIN radacct ON > radacct_log.acctsessionid = radacct.acctsessionid > INNER JOIN node n ON n.mac = LOWER(CONCAT( > SUBSTRING(radacct.callingstationid,1,2),':', > SUBSTRING(radacct.callingstationid,3,2),':', > SUBSTRING(radacct.callingstationid,5,2),':', > SUBSTRING(radacct.callingstationid,7,2),':', > SUBSTRING(radacct.callingstationid,9,2),':', > SUBSTRING(radacct.callingstationid,11,2))) > WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' > )*100,1 > ) AS percent > FROM radacct_log > INNER JOIN radacct ON radacct_log.acctsessionid = > radacct.acctsessionid > INNER JOIN node n ON n.mac = LOWER(CONCAT( > SUBSTRING(radacct.callingstationid,1,2),':', > SUBSTRING(radacct.callingstationid,3,2),':', > SUBSTRING(radacct.callingstationid,5,2),':', > SUBSTRING(radacct.callingstationid,7,2),':', > SUBSTRING(radacct.callingstationid,9,2),':', > SUBSTRING(radacct.callingstationid,11,2)) > ) > WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' > GROUP BY device_class > ORDER BY percent DESC > 470 Query SELECT IFNULL(device_class, 'Unknown > Fingerprint') AS dhcp_fingerprint, > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) AS > accttotaloctets, > ROUND( > > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets)/( > SELECT > SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) > FROM radacct_log RIGHT JOIN radacct ON > radacct_log.acctsessionid = radacct.acctsessionid > INNER JOIN node n ON n.mac = LOWER(CONCAT( > SUBSTRING(radacct.callingstationid,1,2),':', > SUBSTRING(radacct.callingstationid,3,2),':', > SUBSTRING(radacct.callingstationid,5,2),':', > SUBSTRING(radacct.callingstationid,7,2),':', > SUBSTRING(radacct.callingstationid,9,2),':', > SUBSTRING(radacct.callingstationid,11,2))) > WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' > )*100,1 > ) AS percent > FROM radacct_log > INNER JOIN radacct ON radacct_log.acctsessionid = > radacct.acctsessionid > INNER JOIN node n ON n.mac = LOWER(CONCAT( > SUBSTRING(radacct.callingstationid,1,2),':', > SUBSTRING(radacct.callingstationid,3,2),':', > SUBSTRING(radacct.callingstationid,5,2),':', > SUBSTRING(radacct.callingstationid,7,2),':', > SUBSTRING(radacct.callingstationid,9,2),':', > SUBSTRING(radacct.callingstationid,11,2)) > ) > WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' > GROUP BY device_class > ORDER BY percent DESC I am not sure how to isolate a single query from the query log above, all my attempts generate failures like: ERROR 1690 (22003): BIGINT value is out of range in '(`pf`.`radacct_log`.`acctinputoctets` + `pf`.`radacct_log`.`acctoutputoctets`)' Any advise on this matter would be highly appreciated. MJ ------------------------------------------------------------------------------ _______________________________________________ PacketFence-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/packetfence-users
