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

0 comments:

Post a Comment

Note: Only a member of this blog may post a comment.