Page History

...

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:

Study the interface descriptions that should be in column H G of the Spreadsheet.

There are usually of the format:

...

bgp-lu-2 equates to the bgp_lu_access_2 field

In column IH of the Spreadsheet, enter the correct nren name to match those already in the md_vpn.msr_vpn_availability table

In column JI of the Spreadsheet, enter either bgp_lu_access_1 or bgp_lu_access_2 depending on the BGP LU Connection Type

In column KJ of the Spreadsheet paste the following Microsoft Excel Function:

=CONCATENATE("UPDATE msr_vpn_availability SET ",J1I1,"=",TEXT(G1F1*100,"0.00")," WHERE nren_name ='",I1H1,"' AND year =",B1A1," AND month = ",C1B1,";")

Complete the rest of the rows and copy the SQL statements.

Connect to the md_vpn database on prod-cacti02.geant.net using the user mdvpnUser and the password that is stored in Keypass

Run the SQL statements that you have just copied:

Now we need to update the service availability by running the following SQL statements:

UPDATE msr_vpn_availability
SET md_vpn_status = bgp_lu_access_1
where nren_name in ('AMRES','BELnet','BREN','CARnet','CESnet','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.