Page History

...

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:

select a.service_type, a.base_type, a.base_absid, a.year,a.month, round(utilization_in / (b.speed* 1000000),2) as 'avg_capacity_in',

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

Then import the CSV file