SCCM Report - List of Computers from an Active Directory User Security Group - Additional Details

This is an SCCM report to generate a list of computers belonging to users in an SCCM collection. It lists the user's full name.

This report was created and tested for SCCM 2012 Current Branch

Create these variables

• AD_Group_Name - default value of Domain Users
• 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/
• 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

Then create this query in the Dataset

select distinct 

CS.Name0, 

U.Full_User_Name0,

CS.UserName0,

CS.Model0,

CS.Manufacturer0,

BIOS.SerialNumber0,

OS.Caption0,

OS.CSDVersion0

from  

dbo.v_R_User U 

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

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

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 @collectionID

ORDER BY

U.Full_User_Name0

Create a table with these fields

• User Name
• Display 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.