...

select concat('UPDATE report_service_availability SET outage="',SPLUNK_DATA.outage_time,'", number_of_failures=',SPLUNK_DATA.no_of_failures,', avail=',SPLUNK_DATA.availability,' WHERE base_absid=',opsdb_circuit.circuit_absid,' AND year=',SPLUNK_DATA.year,' AND month=',SPLUNK_DATA.month,';')
FROM SPLUNK_DATA LEFT JOIN SPLUNK_LINK ON (SPLUNK_LINK.ip_address = SPLUNK_DATA.ip_address)
LEFT JOIN opsdb_circuit ON (opsdb_circuit.name = SPLUNK_LINK.circuit_name)
where SPLUNK_DATA.year = 2017
and SPLUNK_DATA.month = 6 7
and opsdb_circuit.circuit_usage = 'Access'
and opsdb_circuit.status = 'Operational';

...

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:

SELECT opsdb_circuit.status, opsdb_circuit.date_production, opsdb_circuit.date_terminated, opsdb_circuit.name, excel_service_availability.avail
FROM reports.excel_service_availability, reports.opsdb_circuit
WHERE excel_service_availability.base_absid = opsdb_circuit.circuit_absid
AND (excel_service_availability.base_type='CCT')
AND (opsdb_circuit.project='GEANT2')
AND (opsdb_circuit.circuit_usage='Access')
AND (excel_service_availability.year=2017)
AND (excel_service_availability.month=6)
AND ((opsdb_circuit.status='Operational')
OR (date_format(str_to_date(concat(year(opsdb_circuit.date_terminated),',',month(opsdb_circuit.date_terminated),',1'),'%Y,%m,%d'),'%Y%m')<='201706'))
ORDER BY excel_service_availability.avail desc;

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