Having some fun pulling user login information by way of the profile path inventory data collected from clients.  In this example, I’m excluding server logins, and focusing only on the profiles under C:\Users.  You can modify these however you want, but I hope they’re of some use to you.

1wearandtear

Filter by AD Site Name

SELECT
  SUBSTRING(dbo.v_GS_USER_PROFILE.LocalPath0,10,50) AS ProfilePath, 
  dbo.v_R_System.Name0,
  dbo.v_GS_USER_PROFILE.TimeStamp, 
  dbo.v_GS_USER_PROFILE.ResourceID, 
  dbo.v_R_System.AD_Site_Name0
FROM
  dbo.v_GS_USER_PROFILE INNER JOIN
  dbo.v_R_System ON dbo.v_GS_USER_PROFILE.ResourceID = dbo.v_R_System.ResourceID
WHERE
  (dbo.v_GS_USER_PROFILE.LocalPath0 LIKE 'C:\Users\%') 
  AND 
  (dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%Server%')
  AND 
  (dbo.v_R_System.AD_Site_Name0 = 'Default-First-Site-Name')

Filter by Time Window (within last 30 days)

SELECT
  SUBSTRING(dbo.v_GS_USER_PROFILE.LocalPath0,10,50) AS ProfilePath, 
  dbo.v_R_System.Name0,
  dbo.v_GS_USER_PROFILE.TimeStamp, 
  dbo.v_GS_USER_PROFILE.ResourceID, 
  dbo.v_R_System.AD_Site_Name0
FROM
  dbo.v_GS_USER_PROFILE INNER JOIN
  dbo.v_R_System ON dbo.v_GS_USER_PROFILE.ResourceID = dbo.v_R_System.ResourceID
WHERE
  (dbo.v_GS_USER_PROFILE.LocalPath0 LIKE 'C:\Users\%') 
  AND 
  (dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%Server%')
  AND 
  (DATEDIFF(dd,dbo.v_GS_USER_PROFILE.TimeStamp,GETDATE()) < 30)

Filter by Device Model

SELECT
  SUBSTRING(dbo.v_GS_USER_PROFILE.LocalPath0,10,50) AS ProfilePath, 
  dbo.v_R_System.Name0,
  dbo.v_GS_USER_PROFILE.TimeStamp, 
  dbo.v_GS_USER_PROFILE.ResourceID, 
  dbo.v_R_System.AD_Site_Name0,
  dbo.v_GS_COMPUTER_SYSTEM.Model0
FROM
  dbo.v_GS_USER_PROFILE INNER JOIN
  dbo.v_R_System ON dbo.v_GS_USER_PROFILE.ResourceID = dbo.v_R_System.ResourceID
  INNER JOIN
  dbo.v_GS_COMPUTER_SYSTEM ON
  dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE
  (dbo.v_GS_USER_PROFILE.LocalPath0 LIKE 'C:\Users\%') 
  AND 
  (dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%Server%')
  AND
  (dbo.v_GS_COMPUTER_SYSTEM.Model0 = 'Elitebook Folio 9470m')
Advertisements

One thought on “SCCM / SQL – Find User Logins

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