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
left join reports.opsdb_circuit b on (b.circuit_id = a.circuit_id)
where year(a.timestamp) = 2017
and month(a.timestamp) = 7
and b.status = 'Operational'
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