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 b.circuit_absid,a.circuit_id, round(avg(((a.rx_octets*8)/900)),2) as avg_utilisation_in, round(avg(((a.tx_octets*8)/900)),2) as avg_utilisation_out, round(max(((a.rx_octets*8)/900)),2) as peak_in, round(max(((a.tx_octets*8)/900)),2) as peak_out

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

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

Using Heidi-SQL export the results and paste them into an Excel Spreadsheet

In the next available column, cut and paste the following Excel Function, and create the necessary SQL statements for the remaining rows:

=CONCATENATE("INSERT INTO report_utilization VALUES ('LMD','CCT',",A1,",2017,7,0,0,0,0,",C1,",",D1,",",E1,",",F1,",0,0,0,0,0);")

** Pay attention to the year and the month value in the above Excel Command

Copy the contents in the column back into Heidi-SQL and run.

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

This could be better by including the CONCAT command in the SQL statement, then you wouldn't need to export to excel.