Thursday, August 7, 2014

Find Duplicate Serial Numbers SCCM 2007

We are using SCCM to feed information into an asset management system (CMDB).  We keep trying to stress how SCCM is not a very good asset management tool, especially SCCM 2007.  I have heard the CM12 makes some improvements.

select
sys.ResourceID,
sys.Name0,
sys.Active0,
sys.Obsolete0,
bios.SerialNumber0
from v_R_System sys
left join v_GS_PC_BIOS bios on bios.ResourceID = sys.ResourceID
where bios.SerialNumber0 in (select SerialNumber0 from v_GS_PC_BIOS group by SerialNumber0 having COUNT(*) > 1)
and sys.Obsolete0 = 0
order by SerialNumber0