3.2 MD VPN Availability Detailed

Connect to the reports database on test-msr.geant.net using the user reportsUser and the password that is stored in Keypass

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:

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

There are usually of the format:

Nren Name . BGP Lu connection Type . mdvpn . router name

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 I of the Spreadsheet, enter the correct nren name to match those already in the md_vpn.msr_vpn_availability table

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

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

=CONCATENATE("UPDATE msr_vpn_availability SET ",J1,"=",TEXT(G1*100,"0.00")," WHERE nren_name ='",I1,"' AND year =",B1," AND month = ",C1,";")