One of the first things people want to start seeing once they have SCCM in place is richer detail on all of the devices that are being managed. I’ve written this report to provide an overview of all devices.
One key thing to call out is the way I structure reports like this is based around creating a temp table and then joining multiple queries together through the ‘UPDATE’ statement, this allows you to on the fly manage multiple values on sub queries to ensure you’re getting the desired data.
DECLARE @TempTable TABLE(
Hostname varchar (100),
CCMClient varchar (10),
OperatingSystem varchar (100),
Manufacturer varchar (100),
Model varchar (100),
Chassis varchar (100),
Serial varchar (100),
IsVirtual varchar (100),
CPU ntext,
CPUCores int,
CPULogical int,
RAM int,
VolumeSize_C int,
VolumeFree_C int
)
INSERT INTO @TempTable (Hostname, OperatingSystem,CCMClient, IsVirtual)
SELECT
dbo.v_R_System.Name0,
dbo.v_R_System.operatingSystem0,
CASE WHEN dbo.v_R_System.Client0=1 THEN 'Yes' ELSE 'No' END,
CASE WHEN dbo.v_R_System.Is_Virtual_Machine0=1 THEN 'Yes' ELSE 'No' END
FROM
dbo.v_R_System
UPDATE @TempTable
SET Manufacturer = (
SELECT DISTINCT
dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0
FROM
dbo.v_GS_COMPUTER_SYSTEM
INNER JOIN dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 IS NOT NULL)
)
UPDATE @TempTable
SET Model = (
SELECT DISTINCT
dbo.v_GS_COMPUTER_SYSTEM.Model0
FROM
dbo.v_GS_COMPUTER_SYSTEM
INNER JOIN dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_COMPUTER_SYSTEM.Model0 IS NOT NULL)
)
UPDATE @TempTable
SET Chassis = (
SELECT DISTINCT
CASE dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0
WHEN '1' THEN 'Other'
WHEN '2' THEN 'Unknown'
WHEN '3' THEN 'Desktop'
WHEN '4' THEN 'Low Profile Desktop'
WHEN '5' THEN 'Pizza Box'
WHEN '6' THEN 'Mini Tower'
WHEN '7' THEN 'Tower'
WHEN '8' THEN 'Portable'
WHEN '9' THEN 'Laptop'
WHEN '10' THEN 'Notebook'
WHEN '11' THEN 'Hand Held'
WHEN '12' THEN 'Docking Station'
WHEN '13' THEN 'All in One'
WHEN '14' THEN 'Sub Notebook'
WHEN '15' THEN 'Space-Saving'
WHEN '16' THEN 'Lunch Box'
WHEN '17' THEN 'Main System Chassis'
WHEN '18' THEN 'Expansion Chassis'
WHEN '19' THEN 'SubChassis'
WHEN '20' THEN 'Bus Expansion Chassis'
WHEN '21' THEN 'Peripheral Chassis'
WHEN '22' THEN 'Storage Chassis'
WHEN '23' THEN 'Rack Mount Chassis'
WHEN '24' THEN 'Sealed-Case PC'
ELSE 'Undefinded' END AS 'Chassis'
FROM
dbo.v_R_System
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0!='12')
)
UPDATE @TempTable
SET Serial = (
SELECT DISTINCT
dbo.v_GS_PC_BIOS.SerialNumber0
FROM
dbo.v_GS_PC_BIOS
INNER JOIN dbo.v_R_System ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PC_BIOS.SerialNumber0 IS NOT NULL)
)
UPDATE @TempTable
SET CPU = (
SELECT DISTINCT
dbo.v_GS_PROCESSOR.Name0
FROM
dbo.v_GS_PROCESSOR
INNER JOIN dbo.v_R_System ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PROCESSOR.Name0 IS NOT NULL)
)
UPDATE @TempTable
SET CPUCores = (
SELECT DISTINCT
dbo.v_GS_PROCESSOR.NumberOfCores0
FROM
dbo.v_GS_PROCESSOR
INNER JOIN dbo.v_R_System ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PROCESSOR.NumberOfCores0 IS NOT NULL)
)
UPDATE @TempTable
SET CPULogical = (
SELECT DISTINCT
dbo.v_GS_PROCESSOR.NumberOfLogicalProcessors0
FROM
dbo.v_GS_PROCESSOR
INNER JOIN dbo.v_R_System ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName) AND
(dbo.v_GS_PROCESSOR.NumberOfLogicalProcessors0 IS NOT NULL)
)
Update @TempTable
SET VolumeSize_C = (
SELECT TOP (1)
dbo.v_GS_LOGICAL_DISK.Size0
FROM
dbo.v_GS_LOGICAL_DISK
INNER JOIN dbo.v_R_System ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_GS_LOGICAL_DISK.Size0 IS NOT NULL) AND
(dbo.v_GS_LOGICAL_DISK.DeviceID0 = N'C:') AND
(dbo.v_R_System.Name0 = HostName)
)
Update @TempTable
SET VolumeFree_C = (
SELECT TOP (1)
dbo.v_GS_LOGICAL_DISK.FreeSpace0
FROM
dbo.v_GS_LOGICAL_DISK
INNER JOIN dbo.v_R_System ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_GS_LOGICAL_DISK.DeviceID0 = N'C:') AND
(dbo.v_R_System.Name0 = HostName)
)
Update @TempTable
SET RAM = (
SELECT
SUM(dbo.v_GS_PHYSICAL_MEMORY.Capacity0)/1024
FROM
dbo.v_GS_PHYSICAL_MEMORY
INNER JOIN dbo.v_R_System ON dbo.v_GS_PHYSICAL_MEMORY.ResourceID = dbo.v_R_System.ResourceID
WHERE
(dbo.v_R_System.Name0 = HostName)
)
SELECT *
FROM
@TempTable
ORDER BY
Hostname
Or you can download the .rdo here and install. One note using this report you must have the OperatingSystem attribute being discovered by AD System Discovery.
If you have questions or want to see extra detail in these reports feel free to comment below and I can assist in extending.
Riding the Cloud 