Page History

...

AMRES, ASNET-NM, AzScienceNet,BASNET,MREN,RASH,SANET ,UoM and URANUoM 

We now manually We have to take note of the outage time and number of outages from the Detailed View: http://test-msr.geant.net:8888/msr/ms_ip_avail_new.jsp (created above) and manually insert the data in the reports.report_composed_service_availability table.

An example of such a query would be

SELECT CONCAT('UPDATE reports.report_composed_service_availability SET
outage = '13:15:53\'',a.outage,'\',
number_of_failures = 27
',a.number_of_failures,' WHERE year = 2017 AND month = 7 AND name = \'',SUBSTRING_INDEX(b.name,' ',1),'\';') as 'SQL Commands'
FROM report_service_availability a
LEFT JOIN reports.opsdb_circuit b ON (b.circuit_absid = a.base_absid)
WHERE SUBSTRING_INDEX(b.name,' ',1) in ('AMRES', 'ASNET-NM', 'AzScienceNet', 'BASNET', 'MREN', 'RASH', 'SANET', 'UoM')
AND b.name NOT LIKE '%IAS'
AND a.year = 2017
AND a.month = 7
AND a.number_of_failures > 0;

** Pay attention to the year and month values in the SQL statement

** WARNING in order to obtain accurate availability, availability during maintenance, mean-time-to-repair (MTTR), mean-time-between-failures (MTBF), we need to use the correct number of seconds for the particular month.

...

31 Days = 2678400 seconds

** IMPROVEMENT - You could use the following SELECT (day(last_day(CURDATE() - INTERVAL 1 MONTH))*86400); within the SQL statement then you would not need to change the number of seconds in the month as it would be calculated for you in the query.

Now run the following example query after you have run the above query:

UPDATE reports.report_composed_service_availability SET
MTTR = TIME_FORMAT(SEC_TO_TIME(time_to_sec(outage)/number_of_failures),'%H:%i:%s'),
MTBF = TIME_FORMAT(SEC_TO_TIME((2678400  - time_to_sec(outage))/number_of_failures),'%H:%i:%s'),
avail = (2678400  - time_to_sec(outage))/2678400  ,
avail_maint = (2678400  - time_to_sec(out_maint))/2678400 
WHERE year = 2017
AND month = 7 AND name = 'AMRES'
AND number_of_failures > 0;

** Pay attention to the year and month and seconds values in the SQL statement

...