SCCM Report - List of Computers from an Active Directory User Security Group - No Names

This is an SCCM report to generate a list of computers belonging to users in an active directory security group. It does not list their names, just their log in name. This is done because in some cases looking up the full user name will exclude computers because the computer does not have a user name attached to it.

This report was created and tested for SCCM 2012 Current Branch

Create these parameters

• domain - absolute values of the domain you are searching. I did this because I had more than one domain. the domain value should be in this format "domain\\" without the quotations/
• AD_Group_Name - default value of Domain Users
• OSVersion - I used values of
• %
• Microsoft Windows XP Professional
• Microsoft Windows 7 Enterprise
• Microsoft Windows 7 Professional
• collectionID - SCCM computer collection IDs.  Use available values. Also put % as the default value
• model - model of computer. no default or available values. can leave blank.
• wildcard - hidden value used in query. make default value %
• computer - computer name. no default or available values.
• user - User's full name. no default or available values
• company - Company name from AD. no default or available value

Then create this query in Datasets

select distinct 

CS.Name0 as [Computer Name], 

CS.UserName0 as [User Name],

CS.Model0 as [Model],

CS.Manufacturer0 as [Manufacturer],

BIOS.SerialNumber0 as [Serial Number],

OS.Caption0 as [Operating System],

OS.CSDVersion0 as [Service Pack]

from  

dbo.v_R_User U 

join dbo.v_RA_User_UserGroupName UGN on U.ResourceID = UGN.ResourceID 

left join dbo.v_GS_COMPUTER_SYSTEM CS on CS.UserName0 = U.Unique_User_Name0 

INNER JOIN v_GS_OPERATING_SYSTEM OS ON CS.resourceid = OS.resourceid

LEFT JOIN v_RA_System_IPSubnets as AIP on OS.ResourceID=AIP.ResourceID

INNER JOIN v_FullCollectionMembership FCM ON CS.resourceid = FCM.resourceID

left JOIN dbo.v_GS_PC_BIOS BIOS on CS.resourceid = BIOS.resourceid

Where 

UGN.User_Group_Name0 = (@domain) + (@AD_Group_Name) AND

OS.Caption0 LIKE @OSVersion AND

CollectionID LIKE (@wildcard) + (@collectionID) + (@wildcard) AND

CS.Model0 Like (@wildcard) + (@model) + (@wildcard) AND

CS.Name0 Like (@wildcard) + (@computer) + (@wildcard) AND

U.Full_User_Name0 Like (@wildcard) + (@user) + (@wildcard)

ORDER BY

CS.UserName0

Create a table with these fields

• User Name
• Computer Name
• Manufacturer
• Model
• Serial Number
• Operating System
• Service Pack

Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.