Exploring IP GeoLocation With PowerShell

IP Geolocation is the mapping of an IP address to its geographical location, typically a country, state, or city. We can be confident that an IP block allocated by a regional internet registry (e.g, APNIC) to an Australian ISP will mean that IP range will be assigned to a service or device in Australia. However, this confidence decreases as we attempt to narrow down which state and or which city a specific IP is located in.

Why?

I’ve always had an interest in using IP Geolocation data in various tooling/projects to either gather information or take action based on the location of the IP. IP geoblocking is also becoming a popular security mechanism, being implemented in products such as Azure AD through its Conditional Access feature. Interestingly, geoblocking was also supposed to play a vital part in protecting Australia’s 2018 Census which suffered massive DDoS attacks due to alleged geoblocking implementation failures.

While geoblocking is trivially defeated through the use of VPNs, this isn’t feasable for DDoS traffic, and at the very least it filters out a lot of background noise. If you have a service that should only be available within a certain country, why expose it to the entire world?

How?

There are various APIs available which will return IP location data, in fact, I’m using one on the IP page. However, making API requests is detrimental to performance if we want near realtime answers or if we want perform bulk queries. It’s also nice to be able to remove a dependency on an external service.

One of the most popular sources of IP location data is MaxMind, a company which offers a slightly less accurate version of their commercial GeoIP2 Database for free under the name of GeoLite2. There are two formats available, a MaxMind DB binary format, and CSV. The CSV would be a nightmare to traverse if we’re concerned about performance, so the MaxMind DB format will be our choice. For the examples in this post I’ll be using the GeoLite2 Country database.

At this point I need give credit to David F. Severski for creating a PowerShell module for querying the MaxMind database format.

PSGeoLocate Installation

Download all .dll files from the latest release into a folder. Windows will most likely block the files as a security measure and they will need to be unblocked prior to importing. You may also need to run an elevated PowerShell prompt for the import to be successful.

Get-ChildItem *.dll | Unblock-File
Import-Module .\PSGeoLocate.dll

To make life easier, if you place the DLLs into C:\Program Files\WindowsPowerShell\Modules\PSGeoLocate it can be imported like any other module: Import-Module PSGeoLocate.

Basic example

Import-Module .\PSGeoLocate.dll
Get-GeoLocation -IPAddress 104.198.14.52 -Path $PWD\GeoLite2-Country.mmdb

IPAddress       : 104.198.14.52
CountryCode     : US
CountryName     : United States

Nice! Let’s put together something more useful though.

IP Geolocation with Netstat and PowerShell

Let’s determine what countries each process with open network connections is talking to.

$ProcessPath = @{Label="ProcessPath";Expression={(Get-Process -PID $_.PID | Select Path).Path}}
$CC =@{Label="CountryCode"; Expression={(Get-GeoLocation -IPAddress $_.RemoteAddress -Path $PWD\GeoLite2-Country.mmdb).CountryCode}}
$CN = @{Label="CountryName"; Expression={(Get-GeoLocation -IPAddress $_.RemoteAddress -Path $PWD\GeoLite2-Country.mmdb).CountryName}}

While ($true) {
    Get-Date

    $Netstat = netstat -n -o
    $Netstat = ($Netstat[4..($Netstat.length -1)] -replace "\s+"," " -replace ":", " ").trim()
    $Netstat = $Netstat | ConvertFrom-Csv -Delimiter " " -Header Protocol,LocalAddress,LocalPort,RemoteAddress,RemotePort,State,PID 
    $Netstat = $Netstat | where {$_.RemoteAddress -notmatch '^10.|^192.168.|(^172.[0-2]|3[0-2])|127.0.0.1|\[|\]|0.0.0.0'} 
    
    $Netstat | select Protocol,LocalAddress,LocalPort,RemoteAddress,RemotePort,State,PID,$CC,$CN,$ProcessPath | ft -auto
    Start-Sleep -Seconds 5
}

There is a bit going on here so let’s go through it.

Firstly we define 3 variables ($ProcessPath, $CC, and $CN) which will be used as calculated properties. More on that here if you’re unfamiliar with the syntax.

Notably, the IP address lookup is performed in $CC and $CN expressions where we take the remote IP address of each network connection and retrieve the country code and country name. We then have a loop that shows a timestamp, followed by retrieving netstat output. The options were passing to netstat are -n, which shows all addresses in numerical form (instead if hostnames/FQDNs), and -o which displays the PID that owns the connection.

Netstat also has the -b option that displays the name of the executable responsible for each connection or listening port. We’re not using this as it changes the output formatting and makes parsing significancy more complicated. Further, it only shows the name of the executable (program.exe), and not the full path (C:\Program Files\Application\program.exe), which is what we’re after.

The next few lines clean up the netstat output and convert it into an array of PowerShell objects which are much easier to work with, followed by a bit of regex to exclude all localhost and local network connections as performing Geo IP lookups on RFC1918 address space is not a productive use of our time.

Finally, we print the output and start a short sleep timer before the process repeats. The output resembles the following truncated example.

Wednesday, 4 December 2019 11:38:24 PM


Protocol LocalAddress LocalPort RemoteAddress   RemotePort State       PID   CountryCode CountryName   ProcessPath
-------- ------------ --------- -------------   ---------- -----       ---   ----------- -----------   --------
TCP      10.250.1.100 22350     18.214.x.x      443        ESTABLISHED 36900 US          United States C:\Users\md\AppData\Local\Google\Chrome\Application\chrome.exe
TCP      10.250.1.100 22351     52.206.x.x      443        ESTABLISHED 36900 US          United States C:\Users\md\AppData\Local\Google\Chrome\Application\chrome.exe
TCP      10.250.1.100 25271     52.63.x.x       443        ESTABLISHED 36900 AU          Australia     C:\Users\md\AppData\Local\Google\Chrome\Application\chrome.exe
TCP      10.250.1.100 56862     198.252.x.x     443        ESTABLISHED 36900 US          United States C:\Users\md\AppData\Local\Google\Chrome\Application\chrome.exe
TCP      10.250.1.100 59045     35.186.x.x      443        ESTABLISHED 36900 US          United States C:\Users\md\AppData\Local\Google\Chrome\Application\chrome.exe
TCP      10.250.1.100 59176     216.58.x.x      443        ESTABLISHED 21352 US          United States C:\Program Files\Google\Drive\googledrivesync.exe
TCP      10.250.1.100 59750     35.186.x.x      443        ESTABLISHED 41944 US          United States C:\Users\md\AppData\Roaming\Spotify\Spotify.exe
TCP      10.250.1.100 59795     35.186.x.x      443        ESTABLISHED 36088 US          United States C:\Users\md\AppData\Roaming\Spotify\Spotify.exe
TCP      10.250.1.100 60355     23.205.x.x      443        ESTABLISHED 36088 US          United States C:\Users\md\AppData\Roaming\Spotify\Spotify.exe

Using Get-NetTCPConnection instead of Netstat

Exactly the same result as above can be achieved with Get-NetTCPConnection, in fact it would be less work as there wouldn’t be the need to parse netstat text output, however, my (limited) testing has shown it to be slower.

PS C:\> 1..10 | % {(Measure-Command -Expression {Get-NetTCPConnection}).TotalSeconds}
0.5291759
0.5057628
0.5355282
0.5181683
0.5378689
0.5191421
0.5159379
0.5205644
0.5346007
0.512051

Compared to netstat, including the string parsing.

PS C:\> 1..10 | % {(Measure-Command -Expression {$Netstat = netstat -n -o; $Netstat = ($Netstat[4..($Netstat.length -1)] -replace "\s+"," " -replace ":", " ").trim(); $Netstat = $Netstat | ConvertFrom-Csv -Delimiter " " -Header Protocol,LocalAddress,LocalPort,RemoteAddress,RemotePort,State,PID}).TotalSeconds}
0.0443476
0.0340975
0.0378669
0.0340559
0.0342759
0.033554
0.0335578
0.0347057
0.0345213
0.0339029

There is a trade-off however, the netstat method does consume more CPU than Get-NetTCPConnection.