The joys of SCCM or How difficult is it to get an inventory report

It all started with the simple thought of getting a list of all PCs at my work with some pretty basic info like the computer name, model number, OS….I didn’t think I was being too demanding especially knowing I can put SCCM to work. I was in for a major surprise or disappointment when I found that no such report comes with SCCM and you have to build a custom report.

In the spirit of not reinventing the wheel and with the hope of not being the only person on the planet that ever wanted to do this (not to mention my less than impressive skills when it comes to SQL) I have enlisted the help of the online community. Fortunately I have found enough information to get me started and in the end came up with the following query:

SELECT  distinct
CS.name0 as ‘Computer Name’,
CS.domain0 as ‘Domain’,
v_R_System.Last_Logon_Timestamp0 as ‘Last logon’,
v_R_System.User_Name0 as ’ Username’,
BIOS.SerialNumber0 as ‘Bios serial’,
SE.SerialNumber0 as ‘System Enclosure serial’,
CS.Manufacturer0 as ‘Manufacturer’,
CS.Model0 as ‘model’,
OS.Caption0 as ‘OS’,
RAM.TotalPhysicalMemory0 as ‘Total Memory’,
sum(isnull(LDisk.Size0,’0’)) as ‘Hardrive Size’
from
v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID
right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
right join v_R_System on v_R_System.ResourceID = CS.ResourceID
where
LDisk.DriveType0 =3
group by
CS.Name0,
CS.domain0,
v_R_System.User_Name0,
v_R_System.Last_Logon_Timestamp0,
BIOS.SerialNumber0,
SE.SerialNumber0,
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
RAM.TotalPhysicalMemory0

The resulting report looks something like this:

Capture

Bear in mind that this report doesn’t provide real-time information and based on your SCCM inventory settings can be out by quite some time. For example my Heartbeat discovery only runs once a day so data like last logon can be outdated.

Advertisements

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