Find All IP’s By State and City

I wanted a way to quickly pull all devices by City. Below are the steps on what I did to accomplish this task for Colorado.

Step one is to download a free GeoIP DB. I downloaded one from https://lite.ip2location.com/file-download . You will need to create a free account. Then go to the bottom of the page and download

[DB11.LITE] IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE Database

Extract the ZIP file and go into that directory. You should see the CSV called “IP2LOCATION-LITE-DB11.CSV” My DB folder is located at

C:\toolshed\GeoIP\IP2LOCATION-LITE-DB11.CSV\

Next we are going to need a script to auto generate the IP’s later in this so lets just go ahead and download it now to the same diretory as our GeoIP DB. It’s called Get-IPRange by BarryCWT. The URL to the file is: https://gallery.technet.microsoft.com/scriptcenter/List-the-IP-addresses-in-a-60c5bb6b . You should now have two files in your folder.

Now lets extract only the state of Colorado from the GeoIP DB. Lets pull only the first 10 lines just to see what we are working with.

gc .\IP2LOCATION-LITE-DB11.CSV -head 10

Alright, Eazy Peazy, lets split on the apostrophe and then filter on the 5th item (it’s 0 based index) for Colorado. The below will create the file with a header then append our filtered data to it.

# Create the file with the headers
'"IP_START","IP_STOP","COUNTRY_ABV","COUNTRY","STATE","CITY","LATITUDE","LONGITUDE","ZIPCODE","TIMEZONE"' | out-file -filepath "colorado.txt"
# Append all the filtered lines to the file
gc .\IP2LOCATION-LITE-DB11.CSV | ?{$($_.split(',')[4]) -like "*colorado*"} | out-file -filepath "colorado.txt" -append

Check out our cool new “colorado.txt” csv file. It has roughly all the IP’s used in Colorado with some pretty good information. You’ll see the IP’s need to be converted from decimal to and actual IP so we can figure out the range of each location

To convert from Decimal to IP we can use

# Needs the quotes to be read as STRING
$decimalIPAddress = "67371008"
# Convert Decimal to IP Address (string)
$(([System.Net.IPAddress]$decimalIPAddress).IPAddressToString)

Last part we need to figure out is how to pull all the city names from the “colorado.txt” csv so we can neatly organize the ip addresses into files by city name.

# Function to return all city names
Function Get-GeoIPAllCities([string]$InputFile){
  $fileContents = Import-Csv -Delimiter ',' -Path $InputFile
  $arrayAllCity = @()
foreach ($l in $fileContents){
    $arrayAllCity += $l.City
  }
$arrayAllCity = $arrayAllCity | sort -unique
  RETURN $arrayAllCity
}

Let’s store all the city names in an array with our new fancy function and then see if we pulled the right information

# Get all the unique city names
$allCityNames = Get-GeoIPAllCities -InputFile .\colorado.txt
# Select the first 10 items from the array
$allCityNames | select -First 10

Remember that Get-IPRange.ps1 script we downloaded earlier? Let’s go ahead and import that so we can use the function in the next function we are about to put together.

Import-Module .\Get-IPrange.ps1

Last function we need will take all the information based off a city and extract it out of the “colorado.txt” csv file. I put comments in the function to better explain what I am doing.

Function Get-GeoIPCityInfo([string]$InputFile, [string]$City, [string]$OutputFile){
  # Import as a CSV the Colorado.txt CSV file
  $fileContents = Import-Csv -Delimiter ',' -Path $InputFile
# Create a new blank file that will be named colorado-%city_name%.txt
  '"IP","COUNTRY_ABV","COUNTRY","STATE","CITY","LATITUDE","LONGITUDE","ZIPCODE","TIMEZONE"' | out-file -filepath $OutputFile
# Iterate through the CSV line by line
  foreach ($l in $fileContents){
    # Filter out the results by city
    if ($($l.city).ToString() -eq "$City"){
      Write-host "   [+] $($l.IP_START),$($l.IP_STOP),$($l.COUNTRY),$($l.STATE),$($l.CITY),$($l.ZIPCODE),$($l.TIMEZONE)" -foregroundcolor CYAN
      
      # Use the IP Range script to get the list of IP's
      $ipRange = Get-IPrange -start $(([System.Net.IPAddress]$($l.IP_START)).IPAddressToString) -end $(([System.Net.IPAddress]$($l.IP_STOP)).IPAddressToString)
      
      # Iterate through each IP and append the results to the file we created earlier
      foreach ($ip in $ipRange){
        if ($ip.split('.')[-1] -notlike 0 -AND $ip.split('.')[-1] -notlike 255){
          "$($ip),$($l.COUNTRY),$($l.STATE),$($l.CITY),$($l.LATITUDE),$($l.LONGITUDE),$($l.ZIPCODE),$($l.TIMEZONE)" | out-file -filepath $OutputFile -append
        }
      }
    }
  }
}

Last part is to iterate through each city running the above function.

# Go through each city and export the ips into individual files by city
foreach ($city in $allCityNames){
  $formatedCityName = $city.trim().replace(" ","_")
  Write-host " [+] Exporting: $($City)" -foregroundcolor green
  Get-GeoIPCityInfo -InputFile .\colorado.txt -City "$($city)" -OutputFile ".\colorado-$($formatedCityName).txt"
}

Now we have individual files organized by city with all the corresponding ip’s we can pump into NMAP with -iL

Next step is to get future me to inject all this information into a DB so we can then pull this information and run NMAP against it, then store the nmap resultsin a DB with a web frontend. That will be in a another tutorial.

I’ve provided the entire working script below.