IP Logger

• Download source - 1.96 KB

Introduction

My Internet service provider gives me a static IP number but constantly changes it. This makes it difficult to remotely access my home PC from work. This simple program is designed to solve the problem of keeping track of your changing IP number.

Using the Code

This program consists of two components:

1. An ASP page (IpLogger.aspx) that logs your current IP into a SQL database. This page will also show your current IP. The page has to be deployed to a publicly accessible server. The code behind IpLogger.aspx.vb page has a function called GetConnectionString() that points to the database where the IP log information is stored. The database can be created using this file: Schema.sql.
2. A Windows script file. The script will access the ASP page periodically. It has to be scheduled with Windows Scheduler on your home PC. The script file uses the following URL to save the IP: http://<myservername>/MyServer/IpLogger.aspx?server=server1&save=1.

To view your current IP address and the history, please open this page in your browser: http://<myservername>/MyServer/IpLogger.aspx?server=server1.

Database Schema

CREATE DATABASE IpLogger
GO

USE IpLogger
GO

CREATE TABLE [dbo].[IpLog] (
    [LogId] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY  CLUSTERED,
    [IpAddress] [varchar] (50) NOT NULL ,
    [ServerName] [varchar] (50) NULL ,
    [AccessTime] [datetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]
GO

Code Behind IpLogger.aspx.vb Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
	Handles Me.Load

    If Request.QueryString("save") <> "" Then
        SaveIP(Request.QueryString("server"))
        Response.Write("OK")
        Response.End()
    ElseIf Request.QueryString("delete") <> "" Then
        DeleteServer(Request.QueryString("server"))
        Response.Write("OK")
        Response.End()
    End If

    Dim sSql As String = "SELECT IpAddress, MIN(AccessTime) AS _
			EffectiveOn FROM IpLog " & _
                         " WHERE ServerName = '" & 
			Request.QueryString("server") & "'" & _
                         " GROUP BY IpAddress ORDER BY IpAddress DESC"
    GridView1.DataSource = GetDataReader(sSql)
    GridView1.DataBind()

End Sub

Private Sub DeleteServer(ByVal sServerName As String)
    Dim sSql As String = "DELETE FROM IpLog WHERE ServerName = '" & sServerName & "'"
    ExecuteCommand(sSql)
End Sub

Private Sub SaveIP(ByVal sServerName As String)
    Dim sId As String = Request.ServerVariables("remote_addr")
    Dim sSql As String = "INSERT INTO IpLog _
	(IpAddress, ServerName) VALUES ('" & sId & "', '" & sServerName & "')"
    ExecuteCommand(sSql)
End Sub

Private Function GetDataReader(ByVal sSql As String) As OleDbDataReader
    Dim sConnection As String = GetConnectionString() 
    Dim cn As New OleDbConnection(sConnection)
    Dim cmd As New OleDbCommand(sSql, cn)
    cn.Open()
    Try
        Return cmd.ExecuteReader(Data.CommandBehavior.CloseConnection)
    Catch ex As Exception
        Throw New Exception(ex.Message & "

SQL: " & Replace(sSql, vbCrLf, "
"))
    End Try
End Function

Private Sub ExecuteCommand(ByVal sSql As String)
    Dim cn As New OleDbConnection(GetConnectionString())
    Dim cmd As New OleDbCommand(sSql, cn)
    cn.Open()

    Try
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        Throw New Exception(ex.Message & "; SQL: " & sSql)
    End Try

    cn.Close()
End Sub

Private Function GetConnectionString() As String
    Return "Provider=SQLOLEDB.1;Password=xxxxx;_
	Persist Security Info=True;User ID=sa;Initial Catalog=IpLogger;Data Source=."
End Function

Windows Script File: LogIP.vbs

'To View IP History in browser http://MyServer/IpLogger/Default.aspx?server=server1

'To Save IP
GetUrlData "http://MyServer/IpLogger/Default.aspx?server=server1&save=1"

Function GetUrlData(sUrl)
    Dim oHttp
    Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")
    oHttp.setTimeouts 0, 0, 0, 0
    
    oHttp.Open "GET", sUrl, False
    oHttp.send
    GetUrlData = oHttp.responseText
    Set oHttp = Nothing
End Function

History

• 15^th February, 2009: Initial post