How can I find the last logged on user using a sql query?

book

Article ID: 181810

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
 

I need to write a query that shows the last logged on user of a computer, how might this be done?

Answer

Here's an example of a query that lists the most recent username and domainname for all computers.

Please note, this query is provided only as an example. You will need to modify it to suit your own needs. Also remember to always take backups before attempting to run queries like this. 

select distinct
vc.[name] as
'Computer Name'
,LogOn.[Time] as
'Time of Most Recent User Logon'
,LogOn.[User] as
'Username (Most Recent User Logon)'
,case
when LogOn.[domain] = vc.[name] then
'localhost'
else LogOn.[domain]
end as
'Domain (Most Recent User Logon)'
from vComputer vc
join Evt_AeX_Client_LogOn logon on logon.[_ResourceGuid] = vc.[guid]
where logon.[_id] in
(
select max([_id]
)
from Evt_AeX_Client_LogOn
where [Event] =
'Logon'
Group by [_ResourceGuid])

The table Evt_AeX_Client_LogOn (which is an event table) holds data for the past user logons. If you look at the table it will likely have many entries for one computer. The logic of the query is, that the _id column continuously increases for each new event put in the table, so the highest _id number for any given computer would be the most recent one.
 

Another example query might look like this;

SELECT
[_itemguid]=vc.[guid]
,vc.[name]
,[Timestamp]=cl.[_eventTime]
,cl.[Event]
,cl.[User]
,cl.[Domain]
FROM Evt_AeX_Client_LogOn AS cl
JOIN vComputer AS vc ON vc.[Guid] = cl.[_ResourceGuid]
JOIN (select [max_id]=max(_id) FROM Evt_AeX_Client_Logon WHERE [Event] = 'Logon' GROUP BY _ResourceGuid) AS cl_max ON cl_max.[max_id] = cl.[_id]
-- Include the part below this comment to filter on a particular collection, if you know the exact name and it is unique
-- if you know the guid of your collection you can substitute the where clause with
-- WHERE vcol.[Guid] = 'collectionguid'
JOIN CollectionMembership AS cm ON cm.[ResourceGuid] = vc.[guid]
JOIN vCollection AS vcol ON vcol.[guid] = cm.[CollectionGuid]
WHERE vcol.[name] like 'All Windows Servers'