Am looking to produce a report that would show stats like below: 

Windows Servers 163
Windows 7 Desktops 278
Windows Laptops 48
Virtual Desktops 79
Windows 8 Devices 8

Am having some issues trying to do that. 

I've been trying to modify the following SQL: 

select sys.name0,
cs.manufacturer0,
sys.caption0
from v_R_System sys
INNER JOIN v_GS_Computer_System cs on sys.resourceID = cs.resourceID
INNER JOIN v_GS_Operating_System os on sys.resrouceID = os.resourceID

But I think that query is a 2007 one and my SCCM implementation is 2012!

Any help apprec

Hi,

You can use this query in a report in Configuration Manager 2012 if you just want the count:

SELECT Caption0 as 'Operating System', Count(Caption0) AS 'Number' FROM v_GS_OPERATING_SYSTEM Group by Caption0
Order by Caption0

If you want to include service pack you can use this:

SELECT Caption0 as 'Operating System',CSDVersion0 as 'Service Pack', Count(Caption0) AS 'Number' FROM v_GS_OPERATING_SYSTEM Group by CSDVersion0,Caption0
Order by CSDVersion0,Caption0

Thanks, that gives me the OS count but I'm trying to combine that and see we have x amount of Windows 7 laptop, x amount of virtual Windows 7 etc. 

I think I need to combine the operating system query and manufacturer query to do that but can't quite figure the syntax. 

I have a query that works within SCCM that does it, but need to convert that so it does it for all OS types etc and does a report... 

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Manufacturer in ("VMware, Inc.") and SMS_R_System.OperatingSystemNameandVersion like '%Workstation 6.1%'

I've been trying to modify the following SQL: 

select sys.name0,
cs.manufacturer0,
sys.caption0
from v_R_System sys
INNER JOIN v_GS_Computer_System cs on sys.resourceID = cs.resourceID
INNER JOIN v_GS_Operating_System os on sys.resrouceID = os.resourceID

But I think that query is a 2007 one and my SCCM implementation is 2012!

If the query gives you what you want in CM07. Then it will give you the same data in CM12.

How exact do you determine a computer is a VM, vs. Desktop vs. Laptop?

At the moment I use a SCCM to distinguish between a VM and a desktop/laptop by using manufacturer.  Anything Dell with Windows 7/8 is a desktop/laptop.  Anything with Windows 7/8 and vmware is a VM. 

At the moment I use a SCCM to distinguish between a VM and a desktop/laptop by using manufacturer.  Anything Dell with Windows 7/8 is a desktop/laptop.  Anything with Windows 7/8 and vmware is a

try this

select 
	OS.caption0,
	Case
		when RV.Is_Virtual_Machine0 = 1 then 'Virtual'
		when CS.Manufacturer0 like '%VMWare%' then 'Virtual'
		else 'Physical'
	end as 'Type',
	Case SE.ChassisTypes0 
		when '1' then 'Other' 
		when '2' then 'Unknown' 
		when '3' then 'Desktop' 
		when '4' then 'Low Profile Desktop' 
		when '5' then 'Pizza Box' 
		when '6' then 'Mini Tower' 
		when '7' then 'Tower' 
		when '8' then 'Portable' 
		when '9' then 'Laptop' 
		when '10' then 'Notebook' 
		when '11' then 'Hand Held' 
		when '12' then 'Docking Station' 
		when '13' then 'All in One' 
		when '14' then 'Sub Notebook' 
		when '15' then 'Space-Saving' 
		when '16' then 'Lunch Box' 
		when '17' then 'Main System Chassis' 
		when '18' then 'Expansion Chassis' 
		when '19' then 'SubChassis' 
		when '20' then 'Bus Expansion Chassis' 
		when '21' then 'Peripheral Chassis' 
		when '22' then 'Storage Chassis' 
		when '23' then 'Rack Mount Chassis' 
		when '24' then 'Sealed-Case PC' 
		else 'Undefinded' 
		end as 'PC Type',
	Count(*) as 'Total'
 from 
	dbo.v_R_System RV
	join dbo.v_GS_COMPUTER_SYSTEM CS on RV.ResourceID = CS.resourceID
	JOIN dbo.v_GS_Operating_System OS on RV.ResourceID = OS.resourceID
	Join dbo.v_GS_SYSTEM_ENCLOSURE SE on RV.ResourceID = SE.ResourceID
Group by 
	OS.caption0,
	CS.Manufacturer0,
	RV.Is_Virtual_Machine0,
	SE.ChassisTypes0 
Order by 
	OS.caption0

Great query thanks Garth!