Hello. I am creating a login/logoff audit program to track / restrict users on our network.
I have a table containing log on/off records so : loginid, userid, logindatetime, state, computername (state is either: 1 logon, -1 logoff or 0 attempted logon)
its probably quite simple but i can't quite get my head around it, i need a query to tell me who is currently logged on, ie the most recent logon (state=1) which has no logoff (state=-1) to match. the trouble is that this must cater for users being able
to log on to multiple computers so somewhere the computer names must match too when eliminating records.
i currently have the following query for selecting the most recent logon, but can't think how to eliminate the ones which have a matching logoff
with cte as (
select *, ROW_NUMBER() over (partition by UserID order by a.LoginDateTime desc) as seq from LoginAuditTable a where a.[State] = 1
View Complete Post