I've become disenchanted with PIA VPN, so I'm trying NordVPN

[Jafadmin]

PIA has gotten really bad lately. When you connect to a Canadian server and do an IP lookup, it resolves to somewhere in the US. In fact, half the time ip's will never resolve to the advertised location.

So I signed up for NordVPN, but it has a big problem for us linux users. Their '.ovpn' files are not organized by city names. They are all organized by latitude/longitude, and you can't figure it out by the '.ovpn' file names. All the filenames give you is the country name.

I prefer to connect to vpn's based on which city they're in, not just country.

I found out the NordVPN database that maps lat/lon to a config file is a JSON file. So I decided to convert the json file to a spreadsheet so I could at least look up the filenames by referencing lat/lon coordinates of various cities using https://informationfreeway.org/
I had to format the lon/lat columns to '0.00', then I could sort the spreadsheet by longitude to find what I needed. It would then show me all config filenames related to that geolocation!

It ain't pretty, but it works:
get all the 'ovpn' config files: wget https://downloads.nordcdn.com/conf ... s/ovpn.zip
get the JSON file: curl -s https://api.nordvpn.com/server ... rvers.json

Use jq to extract fields from json file:

Code: Select all

jq -r 'map({name,domain,ip_address, location})' nordservers.json > nord-servers.txt

Remove [ "location": {" ] line from file:

Code: Select all

sed -i /'"location": {'/d  nord-servers.txt

Remove brackets and squeeze spaces:

Code: Select all

cat nord-servers.txt | tr -d '[]{}' | tr -s ' ' > servers1.txt

Remove blank lines:

Code: Select all

grep "\S" servers1.txt >servers2.txt

Now remove all commas:

Code: Select all

sed 's/,//g'  servers2.txt > NordServers.txt

then unzip and run 'srv2csv.sh' script "# ./srv2csv.sh" to create 'NordServers.csv' file (takes awhile)

The "Domain" column contains the name we are looking for. everything up to the first dot '.' Then go to the directory that has all the '.ovpn' files and type: ls au518* (for example), and it will give you the full name of the '.ovpn' file you need.

servers-csv.png (188.46 KiB) Viewed 698 times

srv2csv.sh.tar.gz

(364 Bytes) Downloaded 60 times

[Trapster]

I don't know how I missed this one.
This is awesome!
Thank you.

But....lol.....I'd like to insert a column with the city.
Is there any way I can use

Code: Select all

curl ipinfo.io/"IP"/city

in a cell in gnumeric?
Any other method to pull in the city automatically?