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

================NO NEED TO DO THE BELLOW. ONLY DO IT IF THE % PAGE IS NOT SHOWING RESULTS, THEN BELOW IS THE MANUAL PROCESS========================

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),4) as 'avg_capacity_in',   ##changed the round from 2 to 4 to show 4 decimal values as requested in ticket 2019091834001379 

round(utilization_out/ (b.speed* 1000000),4) as 'avg_capacity_out', round(peak_in/ (b.speed* 1000000),4) as 'Max_capacity_in',
round(peak_out/(b.speed* 1000000),4) 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.