Exploring IP GeoLocation With PowerShell
Posted on December 04, 2019
- and tagged as
- 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 feasible 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.dllTo 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 StatesNice! 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.exeUsing 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.512051Compared 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.0339029There is a trade-off however, the netstat method does consume more CPU than Get-NetTCPConnection.