SCCM Report - Hardware - General > All Computers in a Device Collection with Age

This is an SCCM report to generate a list of computers with users and the computer age with additional details

This report was created and tested for SCCM 2012 Current Branch

Create these parameters

  • ID - Get Values from a query
    • Dataset - CollectionNames
    • Value Field - CollectionID
    • Label Field - Name

Then create these queries in Datasets

CollectionNames

select CollectionID, Name
from dbo.v_Collection C
ORDER BY Name

DataSet1

select 
FCM.Name as [Computer Name],
CS.UserName0 as [User ID],
U.Full_User_Name0 as [User Name],
        CS.Model0 as [Model Type],
        CS.Manufacturer0 as [Manufacturer], 
DateDiff(yyyy,BIOS.ReleaseDate0,GetDate()) as [Age],
BIOS.SerialNumber0 as [Serial Number]

from 
dbo.v_GS_COMPUTER_SYSTEM CS
join dbo.v_FullCollectionMembership FCM 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

Where
FCM.CollectionID = @ID
Order By
U. Full_User_Name0

Create a table with these fields

  • Computer Name
  • Colelction Name
  • User ID
  • User Name
  • 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