How can I find the last logged on user using a sql query?
Last Updated January 25, 2011
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.
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 JOINvComputer AS vc ON vc.[Guid] = cl.[_ResourceGuid] JOIN(select [max_id]=max(_id)FROM Evt_AeX_Client_Logon WHERE [Event] ='Logon'GROUPBY _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' JOINCollectionMembership AS cm ON cm.[ResourceGuid] = vc.[guid] JOIN vCollection AS vcol ON vcol.[guid] = cm.[CollectionGuid] WHERE vcol.[name] like'All Windows Servers'
Imported Document ID: HOWTO9705
Subscribing will provide email updates when this Article is updated. Login is required to Subscribe