Modify the built-in report in Configuration Manager 2012 to show Lenovo serial

I wrote post a long time ago on how to modify the builtin reports in Configuration Manager 2007 to display serial numbers for Lenovo computers as well as they are not stored in the same WMI class, https://ccmexec.com/2010/04/lenovo-computers-and-serial-numbers-in-reports/

Per request here is how to do the same in Configuration Manager 2012 for the report “Hardware 01A – Summary of computers in a specific collection”

Hardware 1a_1

  1. Launch the Admin Console with elevated priviliges, Right Click and select “Run As Administrator”
  2. Right-click on the report called “Hardware 01A – Summary of computers in a specific collection” and select “Edit”
  3. Report Builder will then be launched, depending on which SQL Version you use it will be either 2.0 or 3.0. If you are using SQL Server 2008 R2 you should edit the registry before so that the console can launch the correct version of the Report Builder, more information can be found here:
  4. Double-Click on Dataset0
  5. In the Query add /change the following lines displayed in red below:

select  distinct

v_R_System_Valid.ResourceID,

v_R_System_Valid.Netbios_Name0 AS [Computer Name],

v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],

v_Site.SiteName as [SMS Site Name],

[Top Console User] = CASE

when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = ‘-1’)

then @UnknownLoc

Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0

End,

v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],

v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],

v_GS_PC_BIOS.SerialNumber0 AS [Serial Number],

v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag],

v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer],

v_GS_COMPUTER_SYSTEM.Model0 AS [Model],

v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],

v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)],

(Select sum(Size0)

from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )

where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and

v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)],

(Select sum(v_GS_LOGICAL_DISK.FreeSpace0)

from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID )

where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)]

from v_R_System_Valid

inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)

left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)

LEFT join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)

inner join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid.ResourceID)

LEFT join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)

LEFT join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)

inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)

left  join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)

LEFT join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)

left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)

Where v_FullCollectionMembership.CollectionID = @CollectionID

Order by v_R_System_Valid.Netbios_Name0

6. Click OK and then save the report, you could save it as a new report as well if you like to have the original query left

7. The report will now show the serial number for all computers including Lenovo

Hardware 1a_2

3 Comments

Add a Comment

Your email address will not be published. Required fields are marked *