SCCM Hardware inventory custom queries 1

 The following SQL query retrieves key details that most customers commonly request:

  • Computer Name and Organizational Unit (OU)
  • Client Status (Active/Inactive)
  • Processor and Memory Details
  • Last Logged-On User
  • Last Hardware Scan Date
  • Operating System Version and Build
  • Logical Disk Details (Total and Free Space)

 


SELECT DISTINCT

    s.Name0 AS ComputerName,

    MAX(v_RA_System_SystemOUName.System_OU_Name0) AS 'Computer OU',

    CASE

        WHEN cs.ClientActiveStatus = 1 THEN 'Active'

        ELSE 'Inactive'

    END AS 'ActiveClient',

    cp.name0 AS [Processor Name], 

    v_GS_COMPUTER_SYSTEM.UserName0 AS 'Last Logged-On User',

    v_GS_WORKSTATION_STATUS.LastHWScan,

    os.Caption0 AS 'OS Version',

    os.BuildNumber0 AS 'OS Build',

    STUFF(

        (SELECT ', ' + 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 = s.ResourceID

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

        1, 2, ''

    ) AS DriveInfo,

    m.TotalPhysicalMemory0 / 1000 AS MemoryMB

FROM v_R_System s

INNER JOIN v_GS_X86_PC_MEMORY m ON s.ResourceID = m.ResourceID

INNER JOIN v_GS_PROCESSOR cp ON s.ResourceID = cp.ResourceID

INNER JOIN v_GS_OPERATING_SYSTEM os ON s.ResourceID = os.ResourceID

LEFT JOIN v_RA_System_SystemOUName ON s.ResourceID = v_RA_System_SystemOUName.ResourceID

LEFT JOIN v_GS_WORKSTATION_STATUS ON s.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID

LEFT JOIN v_GS_COMPUTER_SYSTEM ON s.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID

LEFT JOIN v_CH_ClientSummary CS ON s.ResourceID = cs.ResourceID

LEFT JOIN v_FullCollectionMembership fcm ON fcm.ResourceID = s.ResourceID

GROUP BY

    s.Name0, cp.name0, s.ResourceID, m.TotalPhysicalMemory0,

    v_GS_WORKSTATION_STATUS.LastHWScan, v_GS_COMPUTER_SYSTEM.UserName0, cs.ClientActiveStatus,

    os.Caption0, os.BuildNumber0;

 

 

This will give a result like the one below

 

A screenshot of a computer AI-generated content may be incorrect.

 

 

 

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

Remove CCMCache, Windows.old, and Temp folders using Powershell script

Image
This PowerShell script automates the cleanup of: CCM Cache Files : Removes files older than 14 days from C:\Windows\ccmcache. Temporary Files : Cleans up files older than 14 days from the user’s temp folder ($env:Temp) and the system-wide temp folder (C:\Windows\Temp). Windows.old Folder : Deletes the Windows.old folder if it’s older than 14 days. This folder is typically created during a Windows upgrade and can take up significant disk space. The script also logs all actions to a log file (C:\TempCleanupLog.txt), making it easy to track what was deleted and troubleshoot any issues. # Define paths $ccmCachePath = "C:\Windows\ccmcache" $tempPath = "$env:Temp" $systemTempPath = "C:\Windows\Temp" $windowsOldPath = "C:\Windows.old" $logFile = "C:\TempCleanupLog.txt" # Change log file location if needed # Function to clean up files and directories older than a specific number of days function Clean-Ol...
Read more