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.
    Blogger Comment

0 comments:

Post a Comment