< Blog

Query to Find the Number of Operating Systems by PC Type

By Garth Jones

In the TechNet forum, I was asked about how to query for a count of Operating Systems (OS) by virtual machine and by computer type (desktop, laptop, or server).

I thought this information might be useful for everyone, so I’m sharing my query.

The results will be organized into four columns. The first is OS, the second is Virtual/Physical, the third column is PC Type (Chassis) and the last column gives the total amount of devices running the specified OS.

select

       OS.caption0 as ‘OS’,

       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,

       2,3

After running this query, your results table should look similar to the one below.

Query to Find the Number of Operating Systems by PC Type

Please watch out for word wrapping and pay attention to quotes that might be converted into stylized quotes.

If you have any questions, please feel free to contact me @Garthmj or @enhansoft.