...

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

UPDATE reports.report_composed_service_availability SET
outage = '13:15:53',
number_of_failures = 27
WHERE year = 2017 AND month = 7 AND name = 'AMRES';

** 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.

28 Days = 2419200 seconds

29 Days = 2505600 seconds

30 Days = 2592000 seconds

31 Days = 2678400 seconds

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

UPDATE 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';

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

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

update report_composed_service_availability set MTBF = '00:00:00', MTTR = '00:00:00'
where MTBF is NULL and MTTR is null
and year = 2017 and month = 7;

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

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

** WARNING sometimes this page does not display properly, and may only show one or two lines of data. The reason for this is the query behind the scenes uses a left join to a table called reports.msr_sla. If a new circuit has been created, a row will be missing from the reports.msr_sla table. To fix this, run the query below and note the name of the circuit that has a corresponding null value in the sla column. Then, insert a corresponding row into the reports.msr_sla and check the results.

SELECT report_composed_service_availability.name, report_composed_service_availability.avail, report_composed_service_availability.MTTR, msr_sla.sla
FROM reports.report_composed_service_availability LEFT JOIN reports.msr_sla ON (msr_sla.name = report_composed_service_availability.name)
WHERE (report_composed_service_availability.project='GEANT2')
AND (report_composed_service_availability.circuit_usage='Access')
AND (report_composed_service_availability.year=2017)
AND (report_composed_service_availability.month=7)
AND (report_composed_service_availability.custom_type='CCT')
ORDER BY name;

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

Summary View

The Java Server Page ms_ip_summ_avail_new.jsp (found at /var/lib/tomcat6/webapps/msr on the server) uses the following query to generate the data. The following is for information only:

...