How to report which user last logged onto a specific set of machines, and when?
The following SQL query will return the computer name, computer domain, last logged on user and last logon time for computers with a name starting with E (but this could be any other type of filter):
-- This is the main query used to select the columns that interest us
select tm.[name] as 'Computer name', evt.Domain, evt.[User], tm.[last logon event time]
from Evt_AeX_Client_LogOn evt
-- This is the sub query that returns the computer guid, computer name and last logon time.
select lg._ResourceGuid, vc.name, max(lg._eventTime) as 'Last logon event time'
from Evt_AeX_Client_LogOn lg
join vComputer vc
on lg._ResourceGuid = vc.Guid
where lg.Event = 'Logon'
and vc.name like 'E%'
group by lg._ResourceGuid, vc.Name
) as tm
on evt._eventTime = tm.[Last logon event time]
and evt._ResourceGuid = tm._ResourceGuid
order by [Last logon event time] desc
We have to use a the group by and max functions to ensure we only have the last logged on user per computer, so to extend the result from this query we joined the result set to the Client log on event table and pick the data that interest us from there.