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