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

Question

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.

Answer 1

@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.

Answer 2

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.

---

Answer 3

What exactly do you have for a query now?

Answer 4

Hello, you can try a PIVOT TABLE

SELECT DISTINCT

         CONCAT(RS.Netbios_Name0, '.', COALESCE(RS.Full_Domain_Name0,''))  as 'Server'

        , RS.Full_Domain_Name0 as 'Domain'

        , PIV.COL as 'Criteria'

        , COALESCE(PIV.[B:],'') as 'B'

        , COALESCE(PIV.[C:],'') as 'C'

        , COALESCE(PIV.[D:],'') as 'D'

        , COALESCE(PIV.[E:],'') as 'E'

        , COALESCE(PIV.[F:],'') as 'F'

        , COALESCE(PIV.[G:],'') as 'G'

        , COALESCE(PIV.[H:],'') as 'H'

        , COALESCE(PIV.[I:],'') as 'I'

        , COALESCE(PIV.[J:],'') as 'J'

        , COALESCE(PIV.[K:],'') as 'K'

        , COALESCE(PIV.[L:],'') as 'L'

        , COALESCE(PIV.[M:],'') as 'M'

        , COALESCE(PIV.[N:],'') as 'N'

        , COALESCE(PIV.[O:],'') as 'O'

        , COALESCE(PIV.[P:],'') as 'P'

        , COALESCE(PIV.[S:],'') as 'S'

        , COALESCE(PIV.[T:],'') as 'T'

        , COALESCE(PIV.[V:],'') as 'V'

        , COALESCE(PIV.[W:],'') as 'W'

        , COALESCE(PIV.[X:],'') as 'X'

        , COALESCE(PIV.[Z:],'') as 'Z'

FROM    (SELECT ResourceID, HD, COL, VAL FROM Tabla

        CROSS APPLY (VALUES ('HDSize', HDSize), ('HDFreeSpace', HDFreeSpace) ) as VALOR (COL,VAL) ) as T

        PIVOT (MAX(VAL) FOR HD in([B:],[C:],[D:],[E:],[F:],[G:],[H:],[I:],[J:],[K:],[L:],[M:],[N:],[O:],[P:],[S:],[T:],[V:],[W:],[X:],[Z:])) as PIV

INNER JOIN v_R_System as RS on RS.ResourceID=PIV.ResourceID