IP Geo Location Lookup Using PowerShell

So, you’ve got a list of IPs and wonder where they are based geographically.

Maybe you compiled the IP list from your Office 365 audit logs using the Search-UnifiedAuditLog as part of researching a hacked Office 365 account? Or maybe it’s from your VPN service, password-protected extranet, or remote desktop session list.

In any case, you can use PowerShell and one of the many free online IP geolocation APIs to find out where these IPs are based geographically. I’ll show you how.

Geolocation Lookup Service Usage

One such online IP geolocation API provider is ip-api. ip-api allows you to query any IP address and will return a well-formed JSON object with geolocation information. As an example, let’s try to look up whitehouse.gov using PowerShell:

PS C:\> Resolve-DnsName whitehouse.gov -Type A | ft Name,IPAddress

Name           IPAddress
----           ---------
whitehouse.gov 23.197.12.199


PS C:\> Invoke-RestMethod -Method Get -Uri "http://ip-api.com/json/23.197.12.199"


as          : AS16625 Akamai Technologies, Inc.
city        : Düsseldorf
country     : Germany
countryCode : DE
isp         : Akamai Technologies
lat         : 51.2277
lon         : 6.77346
org         : Akamai International, BV
query       : 23.197.12.199
region      : NW
regionName  : North Rhine-Westphalia
status      : success
timezone    : Europe/Berlin
zip         : 40213

The DNS hostname is resolved to an IP address, and the IP address is used in a regular web query to ip-api.com.

To no surprise, whitehouse.gov is, of course, based in the western part of Germany! 🙂

Multiple properties are returned in the JSON response. For standard forensic analysis of a list of IP addresses, I suggest grabbing these four properties:

Query (the IP address being queried)
City
Country
ISP

Analyzing a List of IP Addresses

Assuming you have a long list of (unique) IP’s you want to analyze, let’s make a script that will provide you with valuable geographical information for each IP address.

To make life more easy let’s start by making a PowerShell function that will objectify the geolocation information:

function Get-IPGeolocation {
  Param
  (
    [string]$IPAddress
  )
 
  $request = Invoke-RestMethod -Method Get -Uri "http://ip-api.com/json/$IPAddress"
 
  [PSCustomObject]@{
    IP      = $request.query
    City    = $request.city
    Country = $request.country
    Isp     = $request.isp
  }
}

Now it’s only a matter of iterating your list of IPs against this function.

But – be warned! The ip-api.com provider is protected from abuse by a 45-queries-per-minute limit! If you exceed this limit, your IP will get blocked, and you will have to log in to their website to unblock it again (which is not easy when you’re blocked!).

Since you may have a long list of +45 IPs, I have saved you the trouble and created a script that will wait 70 seconds for every 40 queries (to be on the safe side).

Please note! If you’re already a customer at ip-api.com, you need to use a slightly different URL and include your API key in the body of the request, like so:

$request = Invoke-RestMethod -Method Get -Uri "http://pro.ip-api.com/json/$IPAddress" -Body @{key='API_KEY'}

The final script looks like this:

function Get-IPGeolocation {
  Param
  (
    [string]$IPAddress
  ) 
  $request = Invoke-RestMethod -Method Get -Uri "http://ip-api.com/json/$IPAddress"
  [PSCustomObject]@{
    IP      = $request.query
    City    = $request.city
    Country = $request.country
    Isp     = $request.isp
  }
}
$OutputFile = ".\IP_GeoLocation.csv"
$i = 0
$IPs = Get-Content ".\IPs.txt"
ForEach ($IP In $IPs) {
  $i++    # More than 45 queries per minute gets you banned from ip-api.com
  If ($i -gt 40) {
    Write-Host Just pausing a minute to avoid IP blocking from ip-api.com
    Start-Sleep 70
    $i = 0
  }
  Get-IPGeolocation($IP) | Select-Object IP, City, Country, Isp | Export-Csv $OutputFile -NoTypeInformation -Append
}

The script assumes you have an input file, “IPs.txt” with a list of unique IP addresses.

Looking at the Data

The above script generates an output to Excel that may look similar to this:

Now, this is just an example – I’m not trying to insinuate there’s anything suspicious about Nigeria. But assuming you don’t have any employees working or visiting that country, it may appear strange that a Nigerian IP address is found connecting to your Office 365 environment (or VPN or remote desktop or whatever log file you’re analyzing).

Further Analysis

When you have identified one or more suspicious IPs, use them to analyze your log files further. For example, if you’re investigating an Office 365 breach, put the suspicious IP’s in an input file and run the following script:

$IPs = get-content ".\Suspicious_IPs.txt"
$OutputFile = ".\UnifiedAuditLog_IPs.csv"
$EndDate = Get-Date -Date (Get-Date -Format “yyyy-MM-dd”)
$intDays = 90
For ($i=0; $i -le $intDays; $i++){
  $Audit = Search-UnifiedAuditLog -EndDate $EndDate.AddDays(-$i + 1) -StartDate $EndDate.AddDays(-$i) -IPAddresses $IPs -ResultSize 5000
  $ConvertAudit = $Audit | Select-Object -ExpandProperty AuditData | ConvertFrom-Json
  $ConvertAudit | Select-Object CreationTime,UserId,Operation,Workload,ObjectID,SiteUrl,SourceFileName,ClientIP,UserAgent | Export-Csv $OutputFile -NoTypeInformation -Append
  Write-Host $i `t $Audit.Count
}

This will generate an output file which lists exactly what credentials were used and what resources were accessed using the suspicious IP’s. This is very useful information for further damage control and as evidence in case you want to pursue legal action.

Wrapping It Up

I highly recommend proactive use of this technique: If your user base is limited geographically (certain countries or regions) you can set up alerts for critical resources in case of irregular accesses being detected.

For more details on analysis of the Office 365 audit log using PowerShell refer to this post.