Connect to the reports database on test-msr.geant.net using the user reportsUser (Use MySQL Workbench)
Run the following query:
select * from reports.SPLUNK_DATA
where interface_description like '%VPN%'
and year = 2017
and month = 8
order by interface_description, year, month;
*** Note the year and the month values
Copy the results and paste them into a Microsoft Excel Spreadsheet. Tip: Right-click on the rows in MySQL Workbench, choose "Copy Row (Tab separated)"
In the spreadsheet, delete column A (absid). You will end up with something like this:
Study the interface descriptions that should be in column G of the Spreadsheet.
There are usually of the format:
Nren Name . BGP Lu connection Type . mdvpn . router name
Note: If you get any of the below "nren_name" These are just internal links between our Main router and a virtual instance of the router. They are internal to GEANT (this was confirmed by Sebastiano). You can ignore them in the Excel spreadshseet.
The BGP LU connection Type can be three values and equate to the following field names in that md_vpn.msr_vpn_availability table:
bgp-lu equates to the bgp_lu_access_1 field
bgp-lu-1 equates to the bgp_lu_access_1 field
bgp-lu-2 equates to the bgp_lu_access_2 field
In column H of the Spreadsheet, enter the correct nren name to match those already in the md_vpn.msr_vpn_availability table
In column I of the Spreadsheet, enter either bgp_lu_access_1 or bgp_lu_access_2 depending on the BGP LU Connection Type. See below:
In column J of the Spreadsheet paste the following Microsoft Excel Function:
=CONCATENATE("UPDATE msr_vpn_availability SET ",I1,"=",TEXT(F1*100,"0.00")," WHERE nren_name ='",H1,"' AND year =",A1," AND month = ",B1,";")
In column K of the spreadsheet paste the following Microsoft Excel Function:
=CONCATENATE("UPDATE msr_service_availability SET outage ='",TEXT(E1,"hh:mm:ss"),"', avail = ROUND(",F1,",4) WHERE service_name ='",H1,"' AND year =",A1," AND month = ",B1,";")
Complete the rest of the rows and copy the SQL statements. See below:
Connect to the md_vpn database on prod-cacti02.geant.net using the user mdvpnUser and the password that is stored in LastPass.
Run the SQL statements that you have just copied: (Columns J and K from the Spreadsheet)
Now we need to update the service availability by running the following SQL statement:
UPDATE msr_vpn_availability
SET md_vpn_status = bgp_lu_access_1
where nren_name in ('AMRES','BELnet','BREN','CARnet','FCCN','GRnet','HUNGARnet')
and year = 2017
and month = 8;
*** Note the year and the month values
*** With NRENS with a single connection, if the bgp_lu_access_1 fails then the whole service fails.
Now we need to run the following SQL statement to correct the bgp_lu_access_2 availability
update msr_vpn_availability set bgp_lu_access_2 = 100
where nren_name in ('DFN','FUnet','GARR','HEAnet','NORDUnet','PIONIER','RENATER','RedIRIS','SUnet')
and bgp_lu_access_2 = 0
and year = 2017
and month = 8;
*** Note the year and the month values