7.2 Utilisation

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 run the following query:

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;

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

Export the data using Heidi-SQL using the Ouput format as Delimited Text. Paste the results back into Heidi-SQL and run the queries.

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);

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.base_absid, a.utilization_in/1000000 as 'utili in Mbps',b.speed as 'Speed in Mbps', utilization_in / (b.speed* 1000000) as 'avg_capacity_in', utilization_out/ (b.speed* 1000000) as 'avg_capacity_out', peak_in/ (b.speed* 1000000) as 'Max_capacity_in', peak_out/(b.speed* 1000000) as 'Max_capacity_out'
from report_utilization a
inner join opsdb_circuit b on (b.circuit_absid = a.base_absid)
where a.year = 2018
and a.month = 8
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;"

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.

To find the correct circuits to update use the below query:

select * from report_utilization a where year = 2018 and month = 8
and a.service_type = 'LMD'
and (a.base_absid = 24091 or a.base_absid = 24241 or a.base_absid = 24147 or a.base_absid = 22211 or a.base_absid = 44051 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 = 33083 or a.base_absid = 33089
or a.base_absid = 40143 or a.base_absid = 40435) order by base_absid;