Page History

...

Using HeidiSQL run the following query:

Old Script without Roberts 'remove dups' code - do not use

select concat('INSERT INTO report_utilization VALUES (\'LMD\',\'CCT\',',b.circuit_absid,',',year(a.timestamp),',',month(a.timestamp),',0,0,0,0,',round(avg(((a.rx_octets*8)/900)),2),',',round(avg(((a.tx_octets*8)/900)),2),',',round(max(((a.rx_octets*8)/900)),2),',',round(max(((a.tx_octets*8)/900)),2),',0,0,0,0,0);')
from reports.infinera_utilisation a
inner join reports.opsdb_circuit b on (b.name = a.circuit_id)
where year(a.timestamp) = 2018
and month(a.timestamp) = 5
and b.status = 'Operational'
AND b.new_service_type = 'geant lambda'
group by b.circuit_id;

New script below - please use this one

select concat('INSERT INTO report_utilization VALUES (\'LMD\',\'CCT\',',b.circuit_absid,',',year(a.timestamp),',',month(a.timestamp),',0,0,0,0,',round(avg(((a.rx_octets*8)/900)),2),',',round(avg(((a.tx_octets*8)/900)),2),',',round(max(((a.rx_octets*8)/900)),2),',',round(max(((a.tx_octets*8)/900)),2),',0,0,0,0,0);')
from (select circuit_id, interface_aid, timestamp, duration, max(rx_octets) rx_octets, max(tx_octets) tx_octets, file_name from infinera_utilisation where timestamp between '2020-07-01 00:00:00' and '2020-08-01 00:00:00' group by circuit_id, interface_aid, timestamp) a
inner join reports.opsdb_circuit b on (b.name = a.circuit_id)
where b.status = 'Operational'
AND b.new_service_type = 'geant lambda'
group by b.circuit_id;

** Note: If you are getting binary results in Heidi SQL…click the "View binary data as text (instead of HEX)" button in main toolbar.

...