Home Page

Live Real Time User Monitoring in ASP and SQL!

This is great way to monitor user and bot activity in real time... I have tried many different methods and this is hands down the best way to go... my coding isn't always the cleanest but it works... you could actually move the connstring to one include but for example purposes I'm showing the connection string in all places its need.

Create the The Databases.

Call this one "sessions"

Use the following to build the table automatically (copy and paste in Query Analyzer):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sessions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Sessions] GO CREATE TABLE [dbo].[Sessions] ( [SessionID] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LastActivity] [smalldatetime] NOT NULL , [Active] [bit] NOT NULL , [PATH_INFO] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [REMOTE_ADDR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [HTTP_USER_AGENT] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [HTTP_REFERER] [varchar] (999) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [URL] [varchar] (999) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [QUERY_STRING] [varchar] (999) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO

Table will be like this:

Column Name	Data Type	Length	Allow Nulls
SessionID *** make this the active key as well	varchar	24	no
LastActivity **** default vale = getdate()	smalldatetime	4	no
Active **** default value = 1	bit	1	no
PATH_INFO	varchar	500	yes
REMOTE_ADDR	varchar	20	yes
HTTP_USER_AGENT	varchar	500	yes
HTTP_REFERER	varchar	999	yes
URL	varchar	999	yes
QUERY_STRING	varchar	999	yes

You will have to manually edit the table to add the default values ******

Call this one "properties"

Use the following to build the table automatically (copy and paste in Query Analyzer):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Properties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Properties] GO CREATE TABLE [dbo].[Properties] ( [PName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [IntP] [int] NULL , [DateTimeP] [datetime] NULL , [VarcharP] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO INSERT INTO Properties (Pname, IntP) VALUES ('SessionTimeOut',5)

Table will be like this:

Column Name	Data Type	Length	Allow Nulls
PName	varchar	32	no
IntP	int	4	yes
DateTimeP	datetime	8	yes
VarcharP	varchar	255	yes

Edit this table now... the only required is the first value for timeout period... I used 5

Stored Procedures needed (copy and paste into Query analyzer to quickly build these).

CREATE PROCEDURE dbo.proc_getActiveSessionCount AS BEGIN EXEC dbo.proc_removeInactiveSessions SELECT COUNT(Active) FROM Sessions WHERE Active = 1 END GO CREATE PROCEDURE dbo.proc_getIntProperty @pName VARCHAR(32) AS BEGIN SET NOCOUNT ON SELECT IntP FROM Properties WHERE PName = @pName END GO CREATE PROCEDURE dbo.proc_removeInactiveSessions AS BEGIN SET NOCOUNT ON DECLARE @age INT, @boundary SMALLDATETIME SELECT @age = IntP FROM Properties WHERE PName = 'SessionTimeout' SET @boundary = DATEADD(MINUTE, -@age, GETDATE()) DELETE Sessions WHERE Active = 1 AND LastActivity < @boundary END GO CREATE PROCEDURE dbo.proc_terminateSession @sessionID VARCHAR(24) AS BEGIN SET NOCOUNT ON DELETE Sessions WHERE SessionID = @SessionID END GO CREATE PROCEDURE dbo.proc_updateSession @sessionID VARCHAR(24) , @remote_address VARCHAR(20), @path_info VARCHAR(500), @url VARCHAR(999), @query_string VARCHAR(999), @http_user_agent VARCHAR(500), @http_referer VARCHAR(999) AS BEGIN SET NOCOUNT ON UPDATE Sessions SET LastActivity = GETDATE() , URL = @URL, QUERY_STRING = @query_string WHERE SessionID = @sessionID IF @@ROWCOUNT = 0 INSERT Sessions (SessionID, Remote_Addr, URL, Query_String, http_user_agent, http_referer) VALUES(@sessionID, @remote_address, @url, @query_string, @http_user_agent, @http_referer) END GO

The Global.asa settings:

<SCRIPT LANGUAGE="VBScript" RUNAT="Server"> Sub Session_OnStart ConnString = "Provider=SQLOLEDB;" & _ "Password=your_password;" & _ "User ID=your_username;" & _ "Initial Catalog=your_database_name;" & _ "Data Source=your_db_server_ip;" set conn = CreateObject("ADODB.Connection") conn.open ConnString sql = "EXEC proc_getIntProperty 'SessionTimeout'" set rs = conn.execute(sql) if not rs.eof then session.timeout = rs(0) end if rs.close set rs = nothing conn.close set conn = nothing Session("SessID") = session.SessionID & replace(date(), "/", "") & clng(timer) End Sub sub session_onEnd() ConnString = "Provider=SQLOLEDB;" & _ "Password=your_password;" & _ "User ID=your_username;" & _ "Initial Catalog=your_database_name;" & _ "Data Source=your_db_server_ip;" set conn = CreateObject("ADODB.Connection") conn.open ConnString sql = "EXEC proc_terminateSession '" & Session("SessID") & "'" conn.execute sql, , 129 conn.close set conn = nothing end sub </SCRIPT>

Include on every page (session.asp):

--or --

<% ConnString = "Provider=SQLOLEDB;" & _ "Password=your_password;" & _ "User ID=your_username;" & _ "Initial Catalog=your_database_name;" & _ "Data Source=your_db_server_ip;" set conn = CreateObject("ADODB.Connection") conn.open ConnString sql = "EXEC proc_updateSession " & _ "'" & session("sessID") & "'," & _ "'" &Request.ServerVariables("REMOTE_ADDR")&"'," & _ "'" &Request.ServerVariables("PATH_INFO")&"'," & _ "'" &Request.ServerVariables("URL")&"'," & _ "'" &Request.ServerVariables("QUERY_STRING")&"'," & _ "'" &Request.ServerVariables("HTTP_USER_AGENT")&"'," & _ "'" &Request.ServerVariables("HTTP_REFERER")&"'" conn.execute sql, , 129 conn.close set conn = nothing %>

The monitering page:

<% ConnString = "Provider=SQLOLEDB;" & _ "Password=your_password;" & _ "User ID=your_username;" & _ "Initial Catalog=your_database_name;" & _ "Data Source=your_db_server_ip;" set conn = CreateObject("ADODB.Connection") sql = "exec proc_getActiveSessionCount" set conn = CreateObject("ADODB.Connection") conn.open connstring set rs = conn.execute(sql) ActiveUsers = rs(0) rs.close set rs = nothing conn.close set conn = nothing %> <p align="center"><b><font size="4">There are <%=ActiveUsers%> active users. </font></b></p> <table border="1" width="700"> <tr> <td>IP Address</td> <td>Page</td> <td>User Agent</td> <td>Referred From</td> </tr> <% Set conn = server.CreateObject("adodb.Connection") conn.open(connstring) set rs = Server.CreateObject("ADODB.Recordset") SqlStr = "SELECT * FROM Sessions ORDER BY REMOTE_ADDR" Set rs = conn.Execute(SqlStr) Do While Not rs.eof remote_addr = "<a target=""_new"" href=""http://www.dnsstuff.com/tools/whois.ch?ip="&rs("remote_addr")&""">"&rs("remote_addr")&"</a>" URL = rs("url") if trim(rs("query_string")) <> "" then URL = URL &"?"& rs("query_string") end if URL = "<a target=""_new"" href=""http://YOUR-URL"&url&""">"&URL&"</a>" http_user_agent = rs("http_user_agent") if rs("http_referer") <> "" then http_referer = "<a target=""_new"" href="""&rs("http_referer")&""">"&rs("http_referer")&"</a>" else http_referer = " " end if %> <tr> <td><%=remote_addr%></td> <td><%=url%></td> <td><%=http_user_agent%></td> <td><%=http_referer%></td> </tr> <% rs.movenext loop conn.Close Set conn = Nothing Set rs = Nothing function fUserAgent(ua) strSystem = "" on error resume next if instr(ua, "Opera") then if instr(ua, "Opera 3.") then strBrowser = "Opera 3.x" elseif instr(ua, "Opera 4.") then strBrowser = "Opera 4.x" elseif instr(ua, "Opera 5.") then strBrowser = "Opera 5.x" elseif instr(ua, "Opera 6.") then strBrowser = "Opera 6.x" else strBrowser = "Opera" end if elseif instr(ua, "MSIE") then if instr(ua, "MSIE 6.") then strBrowser = "Microsoft IE 6.x" elseif instr(ua, "MSIE 5.") then strBrowser = "Microsoft IE 5.x" elseif instr(ua, "MSIE 4.") then strBrowser = "Microsoft IE 4.x" elseif instr(ua, "MSIE 3.") then strBrowser = "Microsoft IE 3.x" if instr(ua, "MSIE 3.02") then strBrowser = "Microsoft IE 3.02" end if else strBrowser = "Microsoft IE" end if elseif instr(ua, "Mozilla") and instr(ua, "compatible") = 0 then if instr(ua, "Netscape7") then strBrowser = "Netscape 7.x" elseif instr(ua, "Netscape6") then strBrowser = "Netscape 6.x" elseif instr(ua, "Mozilla/4") then strBrowser = "Netscape Navigator 4.x" elseif instr(ua, "Mozilla/3") then strBrowser = "Netscape Navigator 3.x" elseif instr(ua, "Netscape") then strBrowser = "Netscape" elseif instr(ua, "inktomi") then strBrowser = "INKTOMI" strSystem = "SEARCH ENGINE SPIDER" else if instr(ua, "Mozilla/5") then strBrowser = Mid(ua, instr(ua, "rv:")+3) strBrowser = Mid(strBrowser, 1, instr(strBrowser, ")")) strBrowser = Mid(strBrowser, 1, Len(strBrowser)-1) strBrowser = "Mozilla " & strBrowser else strBrowser = "Mozilla" end if end if else StrBrowser="" UaArray = Split(AgentCsvStr,"**") tmpFlag=False For intTmpCtr = 0 to ubound(UaArray) tmpItemArray=Split(UaArray(intTmpCtr),"||") If instr(ua,tmpItemArray(0)) then tmpFlag = True strBrowser = tmpItemArray(1) strSystem = tmpItemArray(2) End If If TmpFlag then EXIT For Next if len(ua) < 3 then strBrowser = "SPOOFED" strSystem = "No User Agent" end if If StrBrowser="" Then strBrowser = cStr(ua) 'fLang(strLangMOD_Ls3kAU_01150) 'used to be an email to the author here end if end if end function %> </table>