VBScript & SQL: Programatically Find the Location of an IP Address

This is more of a post for proper keywords. My previous entry titled “Import MaxMind City CSVs into SQL Server 2005” assumes the user has already conducted research to find that MaxMind’s free GeoIP database provides the functionality to look up the location of an IP address. So, if you wish to quickly and easily find an IP address’ location, do the following.

1. Ensure you have SQL Server installed somewhere. If you don’t currently have it installed,
    you can download SQL Server 2005 Express for free.
2. Read this post about importing the necessary (and free) database for IP lookups.
    a. Download zip & unzip
    b. Read README.txt
    c. Copy the unzip.exe to your Windows directory
    d. Run the included SQL File
    e. Run the included VBS file
3. Create a VBS file to access the information.
4. Double-click the VBS file and enter the IP address

LookupIPAddress.vbs

  strSQLServer = "localhost"
  strDBName = "maxmindGeoIP"

  strIPaddr = InputBox("Enter the IP address:","IP To Location")

  If Len(strIPaddr) = 0 Then Wscript.Quit

  Set objRS = CreateObject("ADODB.recordset")
  strConnstring =  "Driver={SQL Server};Server=" & strSQLServer & ";Database=" & strDBName & ";Trusted_Connection=Yes;"
  strSQL = "EXEC usp_IPtoLocation '" & strIPaddr & "'"
  objRS.Open strSQL, strConnstring, 1, 1

  If objRS.eof And objRS.bof Then
   MsgBox "Something is ultra-broken. Is your database populated? " 'Everything should return at least something.
  Else
   strLocation = objRS("location")
   If InStr(strLocation,"Unknown") > 0 Then
   MsgBox "The IP address, " & strIPaddr & ", cannot be found in the database :(",64,"IP To Location"
   Else
  MsgBox  strIPaddr & " resolves to.." & vbCrLf & vbCrLf & objRS("location"),64,"IP To Location"
End If
End If

Set objRS = Nothing

Then, finding the location is as easy as double-clicking the file, entering an IP, and pressing “OK”….

In less than 1 millisecond, (after the initial stored procedure execution), your results will show up

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in Networking, SQL Server, VBScript
2 comments on “VBScript & SQL: Programatically Find the Location of an IP Address
  1. Lucian says:

    You can use also http://www.ipgp.net , it shows good results.

  2. Personally i use http://www.ipaddressgeolocation.com . They provide location via Google Maps, which is more informative.

Leave a Reply

Your email address will not be published. Required fields are marked *

*