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

select
 s.Name0
, scu.TopConsoleUser0
, s.Full_Domain_Name0
, s.Resource_Domain_OR_Workgr0
, 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
where
s.Operating_System_Name_and0 like 'microsoft windows nt workstation%'
and s.Client0 = 1
order by s.Full_Domain_Name0, s.Name0
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s