Share via

SCCM hardware inventory including all disks, CPUs, Ports SQL report

Vilas 101 Reputation points
2021-11-23T11:51:37.467+00:00

I am looking for SQL report which gives me result in below example

ServerName C:Size E:Size F:Size G:Size CPUType0 CPUType1 IPAddress SubnetMask Memory
Server1 ----- ----- ----- ----- CPU0 CPU1 10.0.0.1 10.1.0.0 - 10.2.0.0 64
Server2 ----- ----- ----- ----- CPU0 CPU1 10.0.0.2 10.1.0.0 - 10.2.0.0 64
Server3 ----- ----- ----- ----- CPU0 CPU1 10.0.0.3 10.1.0.0 - 10.2.0.0 64
Server4 ----- ----- ----- ----- CPU0 CPU1 10.0.0.3 10.1.0.0 - 10.2.0.0 64

I tried different SQL queries, but it shows 1 record for each disk. If server1 having 4 disks then it shows 4 records for 4 disks and 2 more records for subnet mask range. Total 6 record for server1. Please let me know SQL query to get such report.

Microsoft Configuration Manager
Accepted answer
  1. Vilas 101 Reputation points
    2021-11-24T12:47:25.14+00:00

    @AllenLiu-MSFT

    Thank you for query. But, this query returns drives row wise. So, if any server have more than one drive for example C:, E:, F: and G: then it shows 4 rows for each drive. I want the report where it shows all drive size in one row.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. AllenLiu-MSFT 41,456 Reputation points Microsoft Vendor
    2021-11-24T06:39:58.457+00:00

    Hi, @Vilas
    Thank you for posting in Microsoft Q&A forum.

    What query do you have now? Is below query meet your requirement? 

    SELECT   DISTINCT  s.Netbios_Name0 AS ComputerName, s.Operating_System_Name_and0 AS OSName,   
       pr.Name0 AS ProcessorTypeSpeed,   
       ld.deviceid0 AS DriveLetter, ld.Size0 AS TotalDriveSize, ld.freespace0 AS FreeSpaceAvaiable,  
	   m.TotalPhysicalMemory0 AS MemoryMB, ip.IPAddress0, ip.IPSubnet0  
FROM v_R_System_Valid s   
       INNER JOIN v_GS_PROCESSOR pr ON s.ResourceID = pr.ResourceID  
       INNER JOIN v_GS_COMPUTER_SYSTEM gs ON s.ResourceID = gs.ResourceID   
       INNER JOIN v_GS_NETWORK_ADAPTER ON s.ResourceID = v_GS_NETWORK_ADAPTER.ResourceID   
       INNER JOIN v_GS_X86_PC_MEMORY m ON s.ResourceID = m.ResourceID  
       INNER JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION ip ON s.ResourceID = ip.ResourceID  
       INNER JOIN v_GS_LOGICAL_DISK AS ld ON s.ResourceID = ld.ResourceID   
WHERE s.Operating_System_Name_and0 LIKE '%server%'  
       AND ip.IPAddress0 IS NOT NULL AND ip.DefaultIPGateway0 IS NOT NULL  
       AND ld.DriveType0=3

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Garth Jones 1,351 Reputation points
    2021-11-24T08:48:15.95+00:00

    What exactly do you have for a query now?

    0 comments