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 Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. 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 *

*