Visitor tracking with SQL

[chrishirst]

Basic ASP + MySql/Access script for recording site visitors and tracking traffic.

set up to use MySQL, but to use other SQL servers it should simply be a case of adding the correct connection string (or DSN) to the script.

The code below needs to go into an included file, change the constants to suit your server and the sub is called on the page by using <%Track()%>.

table field details included in comments.


<%
' table structure;
' fieldname - type
'==============
' id - Autonumber
' accessdate - date
' accesstime - time
' remoteip - varchar(15)
' refererurl - varchar(254)
' siteurl - varchar(254)
' method - varchar(6)
' useragent - varchar(254)


const Tracking_Table = "tablename"
const str_SQL_User = "username"
const str_SQL_Pass = "password"
const str_SQL_Server = "server"
const str_SQL_DB = "database"

dim track_on
dim str_SQL_ConnString

' str_SQL_ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("database.mdb")

str_SQL_ConnString = "driver={MySQL ODBC 3.51 Driver};server=" & str_SQL_Server & ";uid=" & str_SQL_User & ";pwd=" & str_SQL_Pass & ";database=" & str_SQL_DB & ";option=16387"

track_on = true
' set true or false to turn tracking on or off

Sub Track()
if track_on = true then
dim str_REMOTE_ADDR
dim str_HTTP_REFERER
dim str_URL
dim str_REQUEST_METHOD
dim str_HTTP_USER_AGENT
dim str_Track_SQL

with request
str_REMOTE_ADDR = .servervariables("REMOTE_ADDR")
str_HTTP_REFERER = .servervariables("HTTP_REFERER")
str_URL = .servervariables("URL")
str_REQUEST_METHOD = .servervariables("REQUEST_METHOD")
str_HTTP_USER_AGENT = .servervariables("HTTP_USER_AGENT")
end with

Set conn=Server.CreateObject("ADODB.Connection")
Conn.Open str_SQL_ConnString

str_Track_SQL = "INSERT INTO " & Tracking_Table & " (accessdate,accesstime,remoteip,refererurl,siteurl,method,useragent) VALUES (now()" & ",'" & time() & "','" & str_REMOTE_ADDR & "','" & str_HTTP_REFERER & "','" & str_URL & "','" & str_REQUEST_METHOD & "','" & str_HTTP_USER_AGENT & "');"
conn.execute(str_Track_SQL)
conn.close
set conn = nothing
end if
end sub

%>



Tried to reduce width, it was driving me crazy!~ JH

[JuniorHarris]

Short and sweet, very nice!~ :up:

I was wondering if it needed code to handle null values for any fields [such as referral/agent] that could be blocked by the user's firewall. I guess it really depends if the data structure was defined to allow nulls or not...but something to think about.

Also wanted to ask if with request/end with are basic ASP statements, or specific to vb.net/asp.net?

[chrishirst]

Short and sweet, very nice!~


Cheers JH

For NULL values I just have the columns set to allow nulls, but it's simple enough to add some if IsNull(variable) then ... to the code.

the with reference/end with is basic ASP and can be applied to most collections,
eg;
with response
...
end with

with objRS
...
end with


and the width bugged me when I posted it. :) but I try to avoid using line continuations especially when posting code to a thread.

[JuniorHarris]

I had changed the font size, but then it was too small, and I agree about splitting source lines. I guess I'm used to writing everything long-hand, I've not used the with reference before, but it does allow for some nice shorthand ASP. Thanks again for the contribution.

[Forrest]

I do something similar, and yet completely different. A little more like SQL Profiler. I have a table for web paths and another for pages, so each URL gets its own ID. And a user table, so each user has an ID. When a page starts rendering, I add a record with the user and page IDs, the operation if that's possible, and again when the page is finished rendering. This way I kjnow who is waiting how long for what, which is as important as what's being used how often.

This also lets me run a query for the top x pages a user has been to, so I can give them a favorites list they don't have to maintain.

If possible, this stuff works beautifully on a static worker thread.