Last updated on July 23rd, 2022 at 01:56 pm
In the TechNet forum, I was asked about how to query for the Number 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.
Please watch out for word wrapping and pay attention to quotes that might be converted into stylized quotes.
Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.