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. 

  1. lt-2-2-0-unit-31.proxy.mdvpn.mx2.bra.sk.geant.net
  2. lt-0-0-0-unit-31.proxy.mdvpn.mx1.mar.fr.geant.net
  3. lt-1-1-0-unit-31.proxy.mdvpn.mx1.fra.de.geant.net

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



  • No labels