How to report which user last logged onto a specific set of machines, and when?

Products

• IT Management Suite

Issue/Introduction

 You would like a query that you can run to report on the last logged user to logon to a set of machines and when.

Environment

ITMS 8.x

Cause

N/A

Resolution

Question
How to report which user last logged onto a specific set of machines, and when?

Answer

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
  join (
-- 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.