SCCM 2012 computer information query

I was recently asked to create a query based on the following requirements:

On Windows XP systems only, provide the following information for each system:

1.      Processor speed and type

2.      Memory

3.      Total disk space, free space

4.      IP address

Note: The disk space report is available as an existing report – ‘Asset Intelligence – Hardware 01A report’

However, the free disk space is not captured by default. To enable this attribute, go to client settings for your desktops, hardware inventory, set classes…

clip_image001

Browse to Logical Disk, select Free Space (MB), click OK, OK to exit.

clip_image002

Once clients receive the update, on the next hardware inventory cycle, they should return Disk Free Space. Until then it will be NULL.

This query should give you what you need, the check for Operating System can be modified to provide information on other OS types. Or, the check for OS type can be removed and used to create a report of all clients.


SELECT   DISTINCT  s.Netbios_Name0 AS ComputerName, s.Operating_System_Name_and0 AS OSName, 
       pr.Name0 AS ProcessorTypeSpeed, 
       m.TotalPhysicalMemory0 AS MemoryMB, ip.IPAddress0, 
       ld.deviceid0 AS DriveLetter, ld.Size0 AS TotalDriveSize, ld.freespace0 AS FreeSpaceAvaiable
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 '%NT Workstation 5%'
       AND ip.IPAddress0 IS NOT NULL AND ip.DefaultIPGateway0 IS NOT NULL
       AND ld.DriveType0=3  
       AND ld.deviceid0='C:'
Advertisements
This entry was posted in ConfigMgr, SQL, SSRS. Bookmark the permalink.

4 Responses to SCCM 2012 computer information query

  1. Hi, check this report where full inventory of the computers is returned
    https://gallery.technet.microsoft.com/Configuration-Manager-Most-071cfd38

  2. Pingback: Top 5 blog posts for 2016 | Steve Thompson [MVP]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s