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”
- Launch the Admin Console with elevated priviliges, Right Click and select “Run As Administrator”
- Right-click on the report called “Hardware 01A – Summary of computers in a specific collection” and select “Edit”
- 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:
- Double-Click on Dataset0
- 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
Hello, I`m trying to run this and I`m getting the following:
Incorrect syntax near ‘,’/
Incorrect syntax near the keyword ‘AS’.
Incorrect syntax near the keyword ‘AS’.
Any thoughts ?
Regards.
On an SCCM 2012 SP1 server, try the following query:
select distinct
v_R_System_Valid_Alias.ResourceID,
v_R_System_Valid_Alias.Netbios_Name0 AS [Computer Name],
v_R_System_Valid_Alias.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],
v_Site_Alias.SiteName as [SMS Site Name],
[Top Console User] = CASE
when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP_Alias.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP_Alias.TopConsoleUser0 = ‘-1’)
then @UnknownLoc
Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP_Alias.TopConsoleUser0
End,
v_GS_OPERATING_SYSTEM_Alias.Caption0 AS [Operating System],
v_GS_OPERATING_SYSTEM_Alias.CSDVersion0 AS [Service Pack Level],
v_GS_PC_BIOS.SerialNumber0 AS [Serial Number],
v_GS_SYSTEM_ENCLOSURE_UNIQUE_Alias.SMBIOSAssetTag0 AS [Asset Tag],
v_GS_COMPUTER_SYSTEM_Alias.Manufacturer0 AS [Manufacturer],
v_GS_COMPUTER_SYSTEM_Alias.Model0 AS [Model],
v_GS_X86_PC_MEMORY_Alias.TotalPhysicalMemory0 AS [Memory (KBytes)],
v_GS_PROCESSOR_Alias.NormSpeed0 AS [Processor (GHz)],
(Select sum(Size0)
from fn_rbac_GS_LOGICAL_DISK(@UserSIDs) v_GS_LOGICAL_DISK_Alias inner join fn_rbac_FullCollectionMembership(@UserSIDs) v_FullCollectionMembership_Alias on (v_FullCollectionMembership_Alias.ResourceID = v_GS_LOGICAL_DISK_Alias.ResourceID )
where v_GS_LOGICAL_DISK_Alias.ResourceID =v_R_System_Valid_Alias.ResourceID and
v_FullCollectionMembership_Alias.CollectionID = @CollectionID) As [Disk Space (MB)],
(Select sum(v_GS_LOGICAL_DISK_Alias.FreeSpace0)
from fn_rbac_GS_LOGICAL_DISK(@UserSIDs) v_GS_LOGICAL_DISK_Alias inner join fn_rbac_FullCollectionMembership(@UserSIDs) v_FullCollectionMembership_Alias on (v_FullCollectionMembership_Alias.ResourceID = v_GS_LOGICAL_DISK_Alias.ResourceID )
where v_GS_LOGICAL_DISK_Alias.ResourceID =v_R_System_Valid_Alias.ResourceID and v_FullCollectionMembership_Alias.CollectionID = @CollectionID) As [Free Disk Space (MB)]
from fn_rbac_R_System_Valid(@UserSIDs) v_R_System_Valid_Alias
inner join fn_rbac_GS_OPERATING_SYSTEM(@UserSIDs) v_GS_OPERATING_SYSTEM_Alias on (v_GS_OPERATING_SYSTEM_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
left join fn_rbac_GS_SYSTEM_ENCLOSURE_UNIQUE(@UserSIDs) v_GS_SYSTEM_ENCLOSURE_UNIQUE_Alias on (v_GS_SYSTEM_ENCLOSURE_UNIQUE_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
LEFT join fn_rbac_GS_COMPUTER_SYSTEM(@UserSIDs) v_GS_COMPUTER_SYSTEM_Alias on (v_GS_COMPUTER_SYSTEM_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
inner join v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid_Alias.ResourceID)
LEFT join fn_rbac_GS_X86_PC_MEMORY(@UserSIDs) v_GS_X86_PC_MEMORY_Alias on (v_GS_X86_PC_MEMORY_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
LEFT join fn_rbac_GS_PROCESSOR(@UserSIDs) v_GS_PROCESSOR_Alias on (v_GS_PROCESSOR_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
inner join fn_rbac_FullCollectionMembership(@UserSIDs) v_FullCollectionMembership_Alias on (v_FullCollectionMembership_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
left join fn_rbac_Site(@UserSIDs) v_Site_Alias on (v_FullCollectionMembership_Alias.SiteCode = v_Site_Alias.SiteCode)
LEFT join fn_rbac_GS_LOGICAL_DISK(@UserSIDs) v_GS_LOGICAL_DISK_Alias on (v_GS_LOGICAL_DISK_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID) and v_GS_LOGICAL_DISK_Alias.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM_Alias.WindowsDirectory0,1,2)
left join fn_rbac_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP(@UserSIDs) v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP_Alias on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP_Alias.ResourceID = v_R_System_Valid_Alias.ResourceID)
Where v_FullCollectionMembership_Alias.CollectionID = @CollectionID
Order by v_R_System_Valid_Alias.Netbios_Name0
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
Excellent, good post.