5.1 Availability

**Note: Section 3.1 Availability explains how to obtain the raw data. Info ONLY.

Run the following SQL command in the reports database on test-msr.geant.net: (Use Heidi-SQL)

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 = 7
and opsdb_circuit.circuit_usage = 'Interconnect'
and opsdb_circuit.status = 'Operational';

** Pay attention to the year and month values in the 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/int_avail_service.jsp

Note: Make sure WIX, UK-MANLAN and FR-MANLAN do not exist in the MSR page, run the query and delete the entries:

Query:  select * from report_service_availability where year = 2019 and month =11 and base_absid in('41226', '41375', '43223');

Update MTTR and MTBF

After you have run the previous update SQL statement, then you will need to run the following SQL statement to calculate the availability, MTTR, MTBF of the service.

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

UPDATE reports.report_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 number_of_failures > 0;

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