Count anything with unique Serial Number


Here is how I do it for OS and Service Packs


select Caption0, CSDVersion0, COUNT(1)


from  (


select distinct SerialNumber0,


(select top 1 Caption0 from dbo.v_GS_OPERATING_SYSTEM where


v_GS_OPERATING_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID order by ResourceID desc) as Caption0,


(select top 1 CSDVersion0 from dbo.v_GS_OPERATING_SYSTEM where


v_GS_OPERATING_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID order by ResourceID desc) as CSDVersion0


from v_GS_PC_BIOS


) A


group by Caption0, CSDVersion0


order by COUNT(1) desc


 


 


Compare output to out of the box SCCM report


 


Comments (0)