Workstations with multiple records in system console usage – just get the last

, s.Full_Domain_Name0
, scu.TopConsoleUser0
, u.Mail0
from v_R_System s
left join (select ResourceID, max (TimeStamp) maxTimeStamp from v_GS_SYSTEM_CONSOLE_USAGE group by ResourceID) lcur on lcur.ResourceID = s.ResourceID
left join v_GS_SYSTEM_CONSOLE_USAGE scu on scu.ResourceID = lcur.ResourceID and scu.TimeStamp = lcur.maxTimeStamp
left join v_R_User u on u.Unique_User_Name0 = scu.TopConsoleUser0
s.Operating_System_Name_and0 like 'microsoft windows nt workstation%'
and s.Client0 = 1
order by s.Full_Domain_Name0, s.Name0

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s