I need to write a query that shows the last logged on user of a computer, how might this be done?
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.
vc.[name] as 'Computer Name'
,LogOn.[Time] as 'Time of Most Recent User Logon'
,LogOn.[User] as 'Username (Most Recent User Logon)'
when LogOn.[domain] = vc.[name] then 'localhost'
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 (
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
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'