IP Geo Location Lookup Using PowerShell

So, you’ve got a list of IP’s 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 geo location API’s to find out where these IP’s are based geographically. I’ll show you how.

Geo Location Lookup Service Usage

One such online IP geo location API provider is ip-api. ip-api allows you to query any IP address and will return a well-formed JSON object with geo location 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 host name 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 normal forensic analysis of a list of IP addresses I suggest to grab 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 of the IP addresses.

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

  1. function Get-IPGeolocation {
  2. Param
  3. (
  4. [string]$IPAddress
  5. )
  6. $request = Invoke-RestMethod -Method Get -Uri "http://ip-api.com/json/$IPAddress"
  7. [PSCustomObject]@{
  8. IP = $request.query
  9. City = $request.city
  10. Country = $request.country
  11. Isp = $request.isp
  12. }
  13. }

Now it’s only a matter of iterating you list of IP’s against this function.

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

Since you may have a long list of +150 IP’s I have saved you the trouble and created a script that will wait 70 seconds for each 140 queries (just to be on the safe side).

The final script looks like this:

  1. function Get-IPGeolocation {
  2. Param
  3. (
  4. [string]$IPAddress
  5. )
  6. $request = Invoke-RestMethod -Method Get -Uri "http://ip-api.com/json/$IPAddress"
  7. [PSCustomObject]@{
  8. IP = $request.query
  9. City = $request.city
  10. Country = $request.country
  11. Isp = $request.isp
  12. }
  13. }
  14. $OutputFile = ".\IP_GeoLocation.csv"
  15. $i = 0
  16. $IPs = Get-Content ".\IPs.txt"
  17. ForEach ($IP In $IPs) {
  18. $i++ # More than 150 queries per minute gets you banned from ip-api.com
  19. If ($i -gt 140) {
  20. Write-Host Just pausing a minute to avoid IP blocking from ip-api.com
  21. Start-Sleep 70
  22. $i = 0
  23. }
  24. Get-IPGeolocation($IP) | Select-Object IP, City, Country, Isp | Export-Csv $OutputFile -NoTypeInformation -Append
  25. }

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:

IP Geo Location Data

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 IP’s use them for further analysis of your log files. If you’re investigating an Office 365 breach put the suspicious IP’s in an input file and run the following script:

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

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.

Did you like this post? Maybe your friends will too!
Facebook
Twitter
LinkedIn