Many companies have accounting and asset databases with information about SCCM clients that is not always up to date. Being able to reconcile this data with SCCM is a common task for admins. I recently needed to take a set of serial numbers and see if SCCM had data on the systems. Here is the SQL query I came up with:
select
CS.Name0 as [System Name],
SE.SerialNumber0 as [Serial Number]
from
v_GS_SYSTEM_ENCLOSURE SE
Inner Join v_GS_COMPUTER_SYSTEM CS
On CS.ResourceID = SE.ResourceID
where
SE.SerialNumber0 in (
'Serial1',
'Serial2',
'Serial3',
'Serial4')
I then made one modification to turn it into a report in Report Builder.
select
CS.Name0 as [System Name],
SE.SerialNumber0 as [Serial Number]
from
v_GS_SYSTEM_ENCLOSURE SE
Inner Join v_GS_COMPUTER_SYSTEM CS
On CS.ResourceID = SE.ResourceID
where
SE.SerialNumber0 in ( @SerialNumber )
Now, anyone can do a quick check to see if a system with the corresponding serial number is in SCCM.