ConfigMgr queries grouped by operating system family
If you’ve done ConfigMgr queries for awhile you’ve probably done some reports based on operating system. That has meant using the Caption0 column from the v_GS_Operating_System view, but you probably found you had a lot of variations on the various operating system families. For example, Vista Professional, Vista Enterprise, etc. But what if you would rather just categorize at a higher level (by ‘family’) such as XP vs. Vista vs. Win7?
The following query shows how you can do that level of categorization. The trick is to use CASE clauses with LIKE clauses, like so:
declare @grand_total integer
select @grand_total = COUNT(distinct sys.Name0)
from v_R_System sys join v_CH_EvalResults eval on sys.ResourceID=eval.MachineID join v_GS_OPERATING_SYSTEM os on sys.ResourceID=os.ResourceID
where Client0=1 and Obsolete0=0
select @grand_total 'total clients with health results'
select
case when Caption0 like '%Windows 7%' then 'Win7' when Caption0 like '%XP%' then 'XP' when Caption0 like '%Server 2008 R2%' then 'Server 2008 R2' when Caption0 like '%Server% 2008%' then 'Server 2008' when Caption0 like '%Server% 2003%' then 'Server 2003' when Caption0 like '%Vista%' then 'Vista' when Caption0 like '%Hyper-V%' then 'Server 2008 R2' else 'other' end 'OS'
,COUNT(distinct sys.Name0) 'clients with health results'
,COUNT(distinct sys.Name0)*100 / @grand_total '% of all clients'
,SUM(Active0) 'active clients'
,SUM(Active0) * 100 / COUNT(distinct sys.Name0) '% active'
,SUM(case Result when 6 then 0 when 7 then 0 else 1 end) 'unhealthy clients'
,SUM(case Result when 6 then 0 when 7 then 0 else 1 end) * 100.0 / SUM(Active0) '% unhealthy/active'
from v_R_System sys join v_CH_EvalResults eval on sys.ResourceID=eval.MachineID join v_GS_OPERATING_SYSTEM os on sys.ResourceID=os.ResourceID
where Client0=1 and Obsolete0=0
group by
case when Caption0 like '%Windows 7%' then 'Win7' when Caption0 like '%XP%' then 'XP' when Caption0 like '%Server 2008 R2%' then 'Server 2008 R2' when Caption0 like '%Server% 2008%' then 'Server 2008' when Caption0 like '%Server% 2003%' then 'Server 2003' when Caption0 like '%Vista%' then 'Vista' when Caption0 like '%Hyper-V%' then 'Server 2008 R2' else 'other' end
order by COUNT(*) desc
This is actually for ConfigMgr v.Next client health, but the concept is applicable to any such query for SMS or ConfigMgr (SCCM).
p.s. Credit goes to a co-worker, Benjamin Reynolds, for pointing out this option. I’ve done plenty of CASE statements over the years but didn’t realize that LIKE and similar clauses were an option for the sub-clauses.