Detailed View

Availability data comes from the SPLUNK System as this was deemed more accurate than obtaining network outages from the current Dashboard.

...

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/ms_ip_avail_new.jsp

Overall View

You will notice that some NRENS have one connection to GEANT such as: 

AMRES, ASNET-NM, AzScienceNet,BASNET,MREN,RASH,SANET,UoM and URAN

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

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