SCCM Hardware inventory custom queries 2 ( Specified RAM, Hard Drive details)

 The SQL query provided in this post is designed to extract a wealth of information from your SCCM database. Here’s a breakdown of what it does:

  1. Hostname and Model Information:
    • Retrieves the NetBIOS name (hostname) and the model of each PC.
  2. Operating System Details:
    • Fetches the OS version and build number, which is essential for ensuring compliance and identifying systems that need updates.
  3. Processor Information:
    • Gathers details about the processor, including its name, number of cores, and logical processors.
  4. Memory Configuration:
    • Concatenates information about all RAM slots, including capacity, type, and bus speed, providing a complete picture of the system’s memory configuration.
  5. Storage Details:
    • Retrieves information about the type and model of each drive, as well as the total and free space on each logical disk.
  6. Total Memory and Disk Size:
    • Calculates the total physical memory and the sum of all logical disk sizes, giving you an overview of the system’s storage capacity.





 

DECLARE @CollectionID NVARCHAR(8);

SET @CollectionID = 'SMS00001'; -- All Systems, change this to your target collection if needed

 

SELECT DISTINCT

    sys.Netbios_Name0 AS 'Hostname',

    comp.Model0 AS 'PC Model Name',

   

    -- OS Information

    os.Caption0 AS 'OS Version',

    os.BuildNumber0 AS 'OS Build',

   

    -- Processor Details

    processor.Name0 AS 'Processor Name',

    processor.NumberOfCores0 AS 'Processor Cores',

    processor.NumberOfLogicalProcessors0 AS 'Logical Processors',

   

    -- Memory Information - Using STUFF/FOR XML to concatenate all RAM slots for a system

    STUFF(

        (SELECT ', ' + 'Slot: ' + mem.BankLabel0 +

                ', Capacity: ' + CAST(mem.Capacity0/1024 AS VARCHAR(10)) + ' GB' +

                ', Type: ' + CASE

                    WHEN mem.FormFactor0 = 8 THEN 'DIMM'

                    WHEN mem.FormFactor0 = 12 THEN 'SODIMM'

                    ELSE 'Other'

                END

         FROM v_GS_PHYSICAL_MEMORY mem

         WHERE mem.ResourceID = sys.ResourceID

         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),

        1, 2, ''

    ) AS 'RAM Configuration',

   

    -- RAM Bus Speed

    STUFF(

        (SELECT DISTINCT ', ' + CAST(mem.Speed0 AS NVARCHAR(10)) + ' MHz'

         FROM v_GS_PHYSICAL_MEMORY mem

         WHERE mem.ResourceID = sys.ResourceID

         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),

        1, 2, ''

    ) AS 'RAM Bus Speed',

   

    -- Hard Drive Type/Model Information with unique drive letters

    STUFF(

        (SELECT DISTINCT ', ' +

                ISNULL(pd.Model0, 'Unknown Model') + ' (' +

                CASE pd.MediaType0

                    WHEN 0 THEN 'Unspecified'

                    WHEN 3 THEN 'HDD'

                    WHEN 4 THEN 'SSD'

                    WHEN 5 THEN 'SCM'

                    ELSE 'Unknown'

                END + ') ' +

                STUFF(

                    (SELECT DISTINCT ',' + ld_sub.DeviceID0

                     FROM v_GS_LOGICAL_DISK ld_sub

                     LEFT JOIN v_GS_PHYSICAL_DISK pd_sub ON ld_sub.ResourceID = pd_sub.ResourceID

                     WHERE ld_sub.ResourceID = sys.ResourceID

                     AND (pd_sub.Model0 = pd.Model0 OR (pd_sub.Model0 IS NULL AND pd.Model0 IS NULL))

                     AND (pd_sub.MediaType0 = pd.MediaType0 OR (pd_sub.MediaType0 IS NULL AND pd.MediaType0 IS NULL))

                     GROUP BY ld_sub.DeviceID0

                     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),

                    1, 1, ''

                )

         FROM v_GS_LOGICAL_DISK ld

         LEFT JOIN v_GS_PHYSICAL_DISK pd ON ld.ResourceID = pd.ResourceID

         WHERE ld.ResourceID = sys.ResourceID

         GROUP BY pd.Model0, pd.MediaType0

         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),

        1, 2, ''

    ) AS 'Drive Types',

   

    -- Hard Drive Space Information with unique drive letters

    STUFF(

        (SELECT DISTINCT ', ' + ld_inner.deviceid0 + ' (' +

                CAST(ld_inner.Size0/1024 AS VARCHAR(20)) + ' GB, Free: ' +

                CAST(ld_inner.FreeSpace0/1024 AS VARCHAR(20)) + ' GB)'

         FROM v_GS_LOGICAL_DISK AS ld_inner

         WHERE ld_inner.ResourceID = sys.ResourceID

         GROUP BY ld_inner.deviceid0, ld_inner.Size0, ld_inner.FreeSpace0

         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),

        1, 2, ''

    ) AS 'Drive Space',

   

    -- Total Physical Memory (as in your reference query)

    m.TotalPhysicalMemory0 / 1000 AS 'Total Memory (MB)',

   

    -- Full Hard Disk Size (Summing all logical disk sizes)

    (SELECT SUM(ld_inner.Size0) / 1024

     FROM v_GS_LOGICAL_DISK ld_inner

     WHERE ld_inner.ResourceID = sys.ResourceID) AS 'Total Hard Disk Size (GB)'

 

FROM v_R_System sys

INNER JOIN v_FullCollectionMembership fcm ON sys.ResourceID = fcm.ResourceID

LEFT JOIN v_GS_COMPUTER_SYSTEM comp ON sys.ResourceID = comp.ResourceID

LEFT JOIN v_GS_PROCESSOR processor ON sys.ResourceID = processor.ResourceID

LEFT JOIN v_GS_OPERATING_SYSTEM os ON sys.ResourceID = os.ResourceID

LEFT JOIN v_GS_X86_PC_MEMORY m ON sys.ResourceID = m.ResourceID

 

WHERE fcm.CollectionID = @CollectionID

AND fcm.ResourceType = 5 -- Devices Only

 

ORDER BY sys.Netbios_Name0;

----------------------------------------------------------------------------------------------------------------------

Popular Posts

Windows 11 24H2 Upgrade using Intune Feature Updates Policy

Image
How to Deploy Feature Updates Using Intune: A Step-by-Step Guide Microsoft Intune provides a streamlined method for managing Windows feature updates across your organization. In this guide, I'll walk through the essential prerequisites, policy creation steps, deployment, and monitoring process to ensure a smooth rollout of feature updates using Intune. 1. Verify Prerequisites Before you create a feature update policy, ensure the following prerequisites are met on the target devices: Device Enrollment : Devices must be enrolled in Intune, either as Microsoft Entra hybrid joined or Microsoft Entra joined . Supported OS : Devices must be running a supported version of Windows 10 or Windows 11 . Telemetry Settings : Devices must have the telemetry level set to Required . You can configure this via Devices > Windows> Configuration > Create Policy > Templates>  Device Restrictions > Reporting and Telemetry > Share Usage Data >Set as Required...
Read more

Deploying a Script through Intune to a Linux PC

Image
Can Intune Deploy Shell Scripts to Linux Devices? Yes! Just like deploying PowerShell scripts to Windows, Intune can also deploy shell scripts to Linux devices. In this blog, I'll walk you through the process of deploying shell scripts to Linux using Intune, making it easier to automate tasks and manage Linux endpoints efficiently. Prerequisites Before deploying a shell script via Intune, ensure the following requirements are met: 1. Intune and Microsoft Entra ID Your environment must have Microsoft Intune configured for device management. This setup enables secure enrollment and policy enforcement on Linux devices. 2. Linux Device Enrollment The Linux PC must be properly enrolled in Intune to receive policies and scripts. If the device is not enrolled, follow Microsoft's documentation on Linux enrollment in Intune. Deploying the Shell Script Once the prerequisites are met, follow these steps to deploy your shell script through Intune: Access Microsoft Intune Sign in to the ht...
Read more

Deleting Registry.pol and Forcing Group Policy Update Using a Batch File - Deploy through SCCM

  Deleting Registry.pol and Forcing Group Policy Update Using a Batch File - Software update cycle scan failures  In some troubleshooting scenarios ( specially software update cycle scanning errors)  , it is necessary to remove the Registry.pol file to reset Group Policy settings and force an immediate update. This guide outlines how to accomplish this using a batch script and deploy it via SCCM.  Step 1: Create a Batch File Script to Delete Registry.pol and Update Group Policy @echo off setlocal :: Define paths set "RegPolPath=C:\Windows\System32\GroupPolicy\Machine\Registry.pol" :: Check if the file exists and delete it if exist "%RegPolPath%" del /f /q "%RegPolPath%" :: Force Group Policy update gpupdate /force :: Notify user echo Group Policy has been updated successfully. pause Open Notepad. Copy and paste the script above. Save the file with a .bat extension (e.g., ResetGPO.bat ). Step 2: Deploy the Batch Script Using SCCM 1. Create an SCCM Packag...
Read more

Removing Obsolete Computer Records from Active Directory Using PowerShell (Only Windows Client Versions)

  Removing Obsolete Computer Records from Active Directory Using PowerShell Active Directory (AD) environments can accumulate obsolete computer objects over time. These stale records not only clutter the directory but can also pose security risks. This article provides a PowerShell-based approach to identifying and removing outdated computer records from Active Directory. Identifying Obsolete Computer Accounts The first step in cleaning up AD is to identify inactive computers based on their last logon timestamp and password last set date. Below is a PowerShell script to find obsolete devices that have not logged in for more than 60 days: Import-Module ActiveDirectory $DaysInactive = 60 $time = (Get-Date).AddDays(-$DaysInactive) # Identify obsolete devices $obsoleteDevices = Get-ADComputer -Filter { (LastLogonTimeStamp -lt $time -and PasswordLastSet -lt $time) -and (OperatingSystem -like '*Windows 10*' -or OperatingSystem -like '*Windows 11*' -or OperatingSystem...
Read more