Config mgr report count x86 and x64 operating systems

In this post you will find the code for two reports. The first is a simple report that will count the different system types (x86 etc). The second report can be used as a standard hardware inventory report listing the most basic information like operating system, server name etc. Once you have created the two reports they can be linked together using System type as a prompt/variable.

Count System types

SELECT     dbo.v_GS_COMPUTER_SYSTEM.SystemType0, COUNT(dbo.v_GS_COMPUTER_SYSTEM.SystemType0) AS Total
FROM         dbo.v_R_System INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
GROUP BY dbo.v_GS_COMPUTER_SYSTEM.SystemType0

Basic hardware inventory report

SELECT DISTINCT
                      TOP (100) PERCENT dbo.v_R_System_Valid.ResourceID, dbo.v_R_System_Valid.Netbios_Name0 AS [Computer Name],
                      dbo.v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], dbo.v_Site.SiteName AS [SMS Site Name],
                      dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
                      dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],
                      dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model,
                      dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], dbo.v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],
                          (SELECT     SUM(dbo.v_GS_LOGICAL_DISK.Size0) AS Expr1
                            FROM          dbo.v_GS_LOGICAL_DISK INNER JOIN
                                                   dbo.v_FullCollectionMembership ON dbo.v_FullCollectionMembership.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID
                            WHERE      (dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System_Valid.ResourceID)) AS [Disk Space (MB)],
                          (SELECT     SUM(v_GS_LOGICAL_DISK_2.FreeSpace0) AS Expr1
                            FROM          dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_2 INNER JOIN
                                                   dbo.v_FullCollectionMembership AS v_FullCollectionMembership_2 ON
                                                   v_FullCollectionMembership_2.ResourceID = v_GS_LOGICAL_DISK_2.ResourceID
                            WHERE      (v_GS_LOGICAL_DISK_2.ResourceID = dbo.v_R_System_Valid.ResourceID)) AS [Free Disk Space (MB)],
                      dbo.v_GS_COMPUTER_SYSTEM.SystemType0
FROM         dbo.v_R_System_Valid INNER JOIN
                      dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System_Valid.ResourceID LEFT OUTER JOIN
                      dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE ON dbo.v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_GS_X86_PC_MEMORY ON dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_GS_PROCESSOR ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System_Valid.ResourceID INNER JOIN
                      dbo.v_GS_LOGICAL_DISK AS v_GS_LOGICAL_DISK_1 ON v_GS_LOGICAL_DISK_1.ResourceID = dbo.v_R_System_Valid.ResourceID AND
                      v_GS_LOGICAL_DISK_1.DeviceID0 = SUBSTRING(dbo.v_GS_OPERATING_SYSTEM.WindowsDirectory0, 1, 2) CROSS JOIN
                      dbo.v_Site
WHERE     (dbo.v_GS_COMPUTER_SYSTEM.SystemType0 = @SystemType)
ORDER BY [Computer Name]

The prompt for the basic inventory report

Select distinct SystemType0 From v_GS_COMPUTER_SYSTEM

To create a prompted report follow the instructions in this post https://blog.ctglobalservices.com/confmgr07/config-mgr-inventory-and-reporting/creating-configuration-manager-2007-reports-part-iv-using-prompt/

Linking the two reports

Once the two reports are created you can link them together using the Config Mgr. console.

	navigate to the Report and open the properties for the Count SystemTypes report.
	Select the Links tab. In link type select: Link to another report Select the basic hardware inventory report. It will automatically detect the prompt. Just make sure that column 1 is selected.

The result

The count of system types. Once you select the right arrow it will open the basic inventory report for the select system type.