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

...

Check your results by using the link: http://test-msr.geant.net:8888/msr/lambda_util.jsp

SINET circuits need to be removed. Use the below query and then remove them (base_absid = 33083 or a.base_absid = 33089)

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:"

Step 1: (use MySQL workbench )

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;

Step 2: Export the results to a CSV (from workbench export record set to an external file)

The 4 columns returned above (avg_capacity_in/out, max_capacity_in/out) need to be inserted manually into report_utilization table (very carefully). Make sure to match the absid , year and month.

Step 3:

delete from reports.report_utilization
where (report_utilization.service_type='LMD')
AND (report_utilization.year="2018")
AND (report_utilization.month="12")
and base_absid in (24091 ,24241 ,24147 ,22211 ,24167 ,41759 ,42367
,27069 ,36741 ,28565 ,19095 ,37917 ,24193 ,23603
,23661 ,43729 ,34043 ,34041 ,5765 ,40143 ,40435);

Step 4:

The below statement will return zero records, this is to confirm the deletion took place in the above query. Also, you will need to import the CSV file here (using MySQL Workbench)

SELECT *
FROM reports.report_utilization ru
where (ru.service_type='LMD')
AND (ru.year="2018")
AND (ru.month="12")
and base_absid in (24091 ,24241 ,24147 ,22211 ,24167 ,41759 ,42367
,27069 ,36741 ,28565 ,19095 ,37917 ,24193 ,23603
,23661 ,43729 ,34043 ,34041 ,5765 ,40143 ,40435)
order by base_absid;

Step 5:

Import the CSV file (import records from an external file) icon in MySQL Workbench, find the CSV on your machine (generated in Step 2 above) and then click the "Apply" button.