Last 10 users

[Anthony]

Or so,
Any code samples?

I am Looking to pull from the server request variables, (Authenticating via
windows authentication on my intranet site) and show only the last 10 Unique
users (Along with their login ID/Displayname) to visit a page, and the
date/time they've done so... (Keeping the most recent date/time only for dup
entries.)

I can write to a sql database table no problem, just looking for a good
approach before I reinvent the wheel from scratch..

Thank you in advance..

[Aaron Bertrand [MVP]]

Just create a table in SQL Server,

CREATE TABLE UserLogins
(
UserName VARCHAR(64) PRIMARY KEY,
dt SMALLDATETIME DEFAULT GETDATE()
)
GO

Then in session_onstart, call a stored procedure:

CREATE PROCEDURE dbo.user_trackLogin
@username VARCHAR(64)
AS
BEGIN
SET NOCOUNT ON
UPDATE UserLogins
SET dt = GETDATE() WHERE
UserName = @username
IF @@ROWCOUNT = 0
INSERT UserLogins(UserName)
VALUES(@username)
END
GO

Using something like this in global.asa:

sub session_onstart()
set conn = CreateObject("ADODB.Connection")
conn.open "connection string"
un = Request.ServerVariables("LOGON_USER")
un = replace(un, "'", "''")
conn.execute("EXEC user_trackLogin '" & un & "'")
conn.close: set conn = nothing
end sub

Then to get the most recent 10:

CREATE PROCEDURE dbo.user_listTop10
AS
BEGIN
SET NOCOUNT ON
SELECT TOP 10 UserName, dt
FROM UserLogins
ORDER BY dt DESC
END
GO

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"Anthony" <antgoodlife@nospam.comcast.net> wrote in message
news:#jwF5O83DHA.360@TK2MSFTNGP12.phx.gbl...

> Or so,
> Any code samples?
>
> I am Looking to pull from the server request variables, (Authenticating

via

> windows authentication on my intranet site) and show only the last 10

Unique

> users (Along with their login ID/Displayname) to visit a page, and the
> date/time they've done so... (Keeping the most recent date/time only for

dup

> entries.)
>
> I can write to a sql database table no problem, just looking for a good
> approach before I reinvent the wheel from scratch..
>
> Thank you in advance..
>
>

[Anthony]

Thank you Aaron, I've already obtained the username from the Logon_user.. so
I won't need that piece.. but it's good to post it.. (What exactly is the
difference between auth_user and logon_user anyway? regardless, I'll look
towards using this in the asp. And the stored procedures will be a first run
in my repertoire..

Thank you very much for the quick reply

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/iissdk/iis/ref_ssi_echo.asp[/url]

tells me the difference so there is no need to answer.. but figured i'd
share..
--
Anthony B
------------------------
Expansion Slots: The extra holes in your belt buckle

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:eO$rVY83DHA.1704@tk2msftngp13.phx.gbl...

> Just create a table in SQL Server,
>
> CREATE TABLE UserLogins
> (
> UserName VARCHAR(64) PRIMARY KEY,
> dt SMALLDATETIME DEFAULT GETDATE()
> )
> GO
>
> Then in session_onstart, call a stored procedure:
>
> CREATE PROCEDURE dbo.user_trackLogin
> @username VARCHAR(64)
> AS
> BEGIN
> SET NOCOUNT ON
> UPDATE UserLogins
> SET dt = GETDATE() WHERE
> UserName = @username
> IF @@ROWCOUNT = 0
> INSERT UserLogins(UserName)
> VALUES(@username)
> END
> GO
>
> Using something like this in global.asa:
>
> sub session_onstart()
> set conn = CreateObject("ADODB.Connection")
> conn.open "connection string"
> un = Request.ServerVariables("LOGON_USER")
> un = replace(un, "'", "''")
> conn.execute("EXEC user_trackLogin '" & un & "'")
> conn.close: set conn = nothing
> end sub
>
> Then to get the most recent 10:
>
> CREATE PROCEDURE dbo.user_listTop10
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT TOP 10 UserName, dt
> FROM UserLogins
> ORDER BY dt DESC
> END
> GO
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>
>
>
> "Anthony" <antgoodlife@nospam.comcast.net> wrote in message
> news:#jwF5O83DHA.360@TK2MSFTNGP12.phx.gbl...

> > Or so,
> > Any code samples?
> >
> > I am Looking to pull from the server request variables, (Authenticating

> via

> > windows authentication on my intranet site) and show only the last 10

> Unique

> > users (Along with their login ID/Displayname) to visit a page, and the
> > date/time they've done so... (Keeping the most recent date/time only for

> dup

> > entries.)
> >
> > I can write to a sql database table no problem, just looking for a good
> > approach before I reinvent the wheel from scratch..
> >
> > Thank you in advance..
> >
> >

>
>