SCCM Report - Hardware - General > All Computers and Users in a Device Collection

This is an SCCM report to generate a list of computers in a device collection
This report was created and tested for SCCM 2012 Current Branch

Create these parameters

• ID - Collection ID. Get values from query
• Dataset: ColelctionNames
• Value field: CollectionID
• Label field: Name

Then create these queries in the Dataset

Dataset named CollectionNames

select CollectionID, Name

from dbo.v_Collection C

ORDER BY Name

Dataset named DataSet1

select 

FCM.Name as [Computer Name],

FCM.Domain as [Domain],

C.Name as [Collection Name],

CS.UserName0 as [User ID],

U.Full_User_Name0 as [User Name],

OS.Caption0 as [Operating System],

OS.CSDVersion0 as [Service Pack],

        CS.Model0 as [Model Type],

Year(BIOS.ReleaseDate0) as Age,

        CS.Manufacturer0 as [Manufacturer], 

BIOS.SerialNumber0 as [Serial Number]

from 

dbo.v_Collection C

join dbo.v_FullCollectionMembership FCM on C.CollectionID = FCM.CollectionID

join dbo.v_GS_COMPUTER_SYSTEM CS on CS.Name0 = FCM.Name

join dbo.v_R_User U on U.Unique_User_Name0 = CS.UserName0

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

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

Where

FCM.CollectionID = @ID

Order By

U. Full_User_Name0

Create a table with these fields

• Computer Name
• Domain
• User ID
• User Name
• Operating System
• Service Pack
• Model Type
• Manufacturer
• Age
• Serial Number

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.