Who Is Logged In

[iRadiate]

Is there a way to return a list of all users currently logged in to an application?
The context security methods allow calls to see the current logged in user but not all logged in users as far as I can tell.

[lvasic]

You can get a list from SQL Server.

[iRadiate]

I sort of do that already, I have a login activity table which displays all login activity .. Login Failed, Login Succeeded, Logged Out, Password Change Succeeded, Password Change Failed, etc.
It requires parsing through and trying to figure out who is logged in and who is logged out.

Login Activity.jpg

In speaking with Terry at Alpha, in order to accurately track logged in users, it would require a "last request time" and "last access" log that needs to be updated in a SQL table. You would surmise from the last activity whether the user
is logged off if there were no activity within the session lifetime. Given the amount of work to add such a feature to Alpha, I told them not to pursue it further on my behalf.

The logout method is highly dependent on whether the user actually use the logout or not, so it's not particularly accurate.
I was hoping for a simple function to return just a list of users currently logged in. I may try to simplify my login activity table query and use a query based on time and login/logout requests.

[iRadiate]

What would the SQL Query be for returning a list where the login date = today's date?

This doesn't work:
WHERE LogType = 'LogIn' AND LogTimeStamp = GETDATE()

Nor does this:
WHERE LogType = 'LogIn' AND LogTimeStamp = CAST ( GETDATE() AS DATETIME )

[lgrupido]

Getdate() and logtimestamp() are both date and time fields. You need to set them both to date using cast, or try th following:

WHERE LogType = 'LogIn' AND LogTimeStamp between cast(getdate() as date) and GETDATE()

[iRadiate]

Thanks Larry, that works !

[RichCPT]

In regards to what Terry from Alpha suggested, another similar suggestion is to have the main container of the web app (the tabbedUI in my case) periodically do an Ajax callback in the OnTimer event.
In the callback, stamp some field that you keep in your own user table to indicate the date/time of last contact from that user. Something as simple as:

UPDATE dbo.UsrLogin
SET ulLastAliveDtTm = GETDATE()
WHERE ulUsrLogin_GUID = :pUserGuid

I keep a SQL script handy that lets me see a list of users whose workstations contacted the server within a specified time frame:

declare @gNow as datetime = GETDATE();
declare @minutesThr as integer = 10;
select ulUsrID, ulIsSignedOn, ulSignOnDate, ulLastAliveDtTm, DATEDIFF(minute,ulLastAliveDtTm, @gNow) as LastContactMM
from UsrLogin where DATEDIFF(minute,ulLastAliveDtTm, @gNow) < @minutesThr OR DATEDIFF(minute,ulSignOnDate, @gNow) < @minutesThr;