Report not showing device Serial Numbers

While I was searching for a serial number for a device that needed reporting to the suppliers as faulty the other day I noticed that our report was showing a lot of systems with entries such as "To Be Filled By O.E.M." or "00000000".

This is down to SCCM using v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 in default reports which pulls the serial number from the SerialNumber field in Win32_SystemEnclosure where some (awkward) manufacturers use SerialNumber field in Win32_BIOS.

However, with a little tweaking to the report it now shows all (well, more than before) the serial numbers from across different manufactures that store it in a different location in WMI.

SELECT DISTINCT
v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_R_System_Valid.User_Name0 AS [Last User Logon],
CASE WHEN (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 IS NULL OR
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')
THEN 'Unknown' ELSE v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 END AS [Top Console User],
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, v_GS_COMPUTER_SYSTEM.Model0 AS Model,
[Serial Number] = CASE
when v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 ='00000000'
OR v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 ='Not Available'
OR v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 ='To Be Filled By O.E.M.'
OR v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 ='N/A'
OR v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 ='None'
OR v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 IS NULL
then v_GS_PC_BIOS.SerialNumber0
Else
v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0
End

FROM v_R_System_Valid INNER JOIN
v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID LEFT OUTER JOIN
v_GS_SYSTEM_ENCLOSURE_UNIQUE ON v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID INNER JOIN
v_FullCollectionMembership AS v_FullCollectionMembership_1 ON v_FullCollectionMembership_1.ResourceID = v_R_System_Valid.ResourceID LEFT OUTER JOIN
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID inner join
v_GS_PC_BIOS on (v_GS_PC_BIOS.ResourceID = v_R_System_Valid.ResourceID)
WHERE (v_FullCollectionMembership_1.CollectionID = @CollectionID) AND (NOT (v_GS_COMPUTER_SYSTEM.Manufacturer0 LIKE 'VM%')) AND
(NOT (v_GS_COMPUTER_SYSTEM.Manufacturer0 LIKE 'micro%'))
ORDER BY [Computer Name]

The SQL query above is set to use a prompt for the collection to run against so don't forget to either change this, or add a prompt for @CollectionID when creating the new report in the console.
I've also set the query to filter out virtual devices as I don't report on these using this report.