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.

...

================NO NEED TO DO THE BELLOW. ONLY DO IT IF THE % PAGE IS NOT SHOWING RESULTS, THEN BELOW IS THE MANUAL PROCESS========================

This % problem was fixed in January 2019 so there's no need to do the below queries, but they are here for reference.

Now, run the below queries in order to obtain the (%) values. The results need to be inserted in the "report_utilization" table for the specific month and year:

...

select a.service_type, a.base_type, a.base_absid, a.year,a.month, round(utilization_in / (b.speed* 1000000),4) as 'avg_capacity_in',   ##changed the round from 2 to 4 in order to show 4 decimal values as requested by Emma in ticket 2019091834001379 

...