Page History

A CRON job is run on Robert Latta's development box ch-dev-rl01.win.dante.org.uk (193.63.90.245) every day which populates the reports.infinera_utilisation table on the test-msr.geant.net box.

Using Heidi-SQL 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.

** Pay attention to the year and the month value in the above SQL statement

...

select * from report_utilization where year = 2018 and month = 11 and service_type = 'lmd' and base_type = 'cct' and base_absid in (33083,33089);

================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),24) 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 

round(utilization_out/ (b.speed* 1000000),24) as 'avg_capacity_out', round(peak_in/ (b.speed* 1000000),24) as 'Max_capacity_in',
round(peak_out/(b.speed* 1000000),24) as 'Max_capacity_out',
a.utilization_in, a.utilization_out, a.peak_in, a.peak_out, a.95th_percentile_in,
a.95th_percentile_out, a.volume_in, a.volume_out,a.assymetry
from report_utilization a
inner join opsdb_circuit b on (b.circuit_absid = a.base_absid)
where a.year = 2018
and a.month = 12
and a.service_type = 'LMD'
and b.status = 'Operational'
and b.new_service_type = 'geant lambda'
and (a.base_absid = 24091 or a.base_absid = 24241 or a.base_absid = 24147 or a.base_absid = 22211 or a.base_absid = 24167 or a.base_absid = 41759 or a.base_absid = 42367
or a.base_absid = 27069 or a.base_absid = 36741 or a.base_absid = 28565 or a.base_absid = 19095 or a.base_absid = 37917 or a.base_absid = 24193 or a.base_absid = 23603
or a.base_absid = 23661 or a.base_absid = 43729 or a.base_absid = 34043 or a.base_absid = 34041 or a.base_absid = 5765 or a.base_absid = 40143 or a.base_absid = 40435) order by base_absid;

...