Page History

...

Using Heidi-SQL run the following query:

select concat('INSERT INTO report_utilization VALUES (\'LMD\',\'CCT\',',b.circuit_absid,',a.circuit_id',year(a.timestamp),',',month(a.timestamp),',0,0,0,0,',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,',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

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.jspThis could be better by including the CONCAT command in the SQL statement, then you wouldn't need to export to excel.