Page History

...

We now manually insert the data in into 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 = \'',a.outage,'\', number_of_failures = ',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

NRENS that have dual connection that have had failures in both connections at the same time (determined by reading the issues page: http://test-msr.geant.net:8888/msr/mo_issues_new.jsp ) , will require the manual insertion of data into 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 = 'CYNET';

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

...

** IMPROVEMENT - You could use the following SELECT following (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.

...

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 number_of_failures > 0;

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

...

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;

...

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=67)
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')<='201706201707'))
ORDER BY excel_service_availability.avail desc;

...