1wearandtear

Find computers where a particular user has logged on at least once.  Find them by user account name or by SID.  I know this violates a cardinal rule about avoiding LIKE with double-ended ‘%’ wildcards, but hey.  YOLO!

Find by User Profile + UserName

SELECT DISTINCT 
  dbo.v_R_System.Name0 AS ComputerName, 
  dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, 
  dbo.v_R_System.AD_Site_Name0 AS ADSiteName, 
  dbo.v_GS_COMPUTER_SYSTEM.Domain0 AS Domain 
FROM 
  dbo.v_R_System INNER JOIN
  dbo.v_GS_COMPUTER_SYSTEM 
  ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
  INNER JOIN
  dbo.v_GS_USER_PROFILE 
  ON dbo.v_R_System.ResourceID = dbo.v_GS_USER_PROFILE.ResourceID
WHERE 
  (dbo.v_GS_USER_PROFILE.LocalPath0 LIKE '%johndoe%')

Find Computers by User Profile SID

SELECT DISTINCT 
  dbo.v_R_System.Name0 AS ComputerName, 
  dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, 
  dbo.v_R_System.AD_Site_Name0 AS ADSiteName, 
  dbo.v_GS_COMPUTER_SYSTEM.Domain0 AS Domain 
FROM 
  dbo.v_R_System INNER JOIN
  dbo.v_GS_COMPUTER_SYSTEM 
  ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID 
  INNER JOIN
  dbo.v_GS_USER_PROFILE 
  ON dbo.v_R_System.ResourceID = dbo.v_GS_USER_PROFILE.ResourceID
WHERE 
  (dbo.v_GS_USER_PROFILE.SID0 = 'S-1-5-21-123456789-1234567890-1234567890-1234')

Bonus – List UserNames by Most Computer Logins / Profiles

Find those promiscuous little users that seem to get their busy little fingers (or Remote Desktop connections) smeared on the most computers.  This one dumps a table of UserName, UserSID and count of Computers on which they have a local profile folder.

SELECT
  dbo.v_R_User.User_Name0 AS UserName, 
  dbo.v_R_User.SID0 AS UserSID, 
  COUNT(dbo.v_GS_USER_PROFILE.ResourceID) AS Computers
FROM 
  dbo.v_R_User INNER JOIN
  dbo.v_GS_USER_PROFILE ON dbo.v_R_User.SID0 = dbo.v_GS_USER_PROFILE.SID0
GROUP BY 
  User_Name0, dbo.v_R_User.SID0
ORDER BY 
  Computers DESC, UserName
Advertisements

2 thoughts on “SCCM SQL Queries: Find Computers by User Logins and Login Counts

  1. Hi, I am curious. I am a bit new using queries, so I decided to cut and paste one of the queries that you provided ” Find Computers by User Profile SID” but when I ran the query I received an “Invalid” error. Anything I need to change?

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