Find all devices a particular user (in this example “contoso\sccmadmin”) has logged onto in the past 30 days.

SELECT 
  dbo.v_R_System.Name0 AS ComputerName, 
  dbo.v_GS_SYSTEM_CONSOLE_USER.ResourceID,
  dbo.v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0 AS LastLogon,
  dbo.v_GS_SYSTEM_CONSOLE_USER.NumberOfConsoleLogons0 AS NumberLogons,
  dbo.v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 AS UserID,
  dbo.v_GS_SYSTEM_CONSOLE_USER.TotalUserConsoleMinutes0 AS LogonTotalTime
FROM dbo.v_GS_SYSTEM_CONSOLE_USER INNER JOIN
  dbo.v_R_System ON 
    dbo.v_GS_SYSTEM_CONSOLE_USER.ResourceID =
  dbo.v_R_System.ResourceID
WHERE 
  (dbo.v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0 = 'contoso\sccmadmin')
  AND
  (DATEDIFF(dd, dbo.v_GS_SYSTEM_CONSOLE_USER.LastConsoleUse0, GETDATE()) < 30)

Output

The result set includes the Computer Name, the Computer ResourceID, the LastLogon timestamp,  the cumulative number of logins (per device), the UserID, and the cumulative login time (in minutes) per device.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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