CM Display OS Marketing version in SQL reports
Have you ever noticed that by default, the devices (Windows 10, 11, Servers) don't report up their 'marketing name' in standard inventory? Like 22H2, or 21H2? Ever find that annoying? Sure, you can extend inventory to pull in the regkey of SOFTWARE\Microsoft\Windows NT\CurrentVersion\DisplayVersion, but... you don't have to. That information 'is' buried in the database, it's just not... super easy to find.
Here's a sample report to "Show ComputerName, OSBuild, MarketingName, Make, Model, SupportEndDate for the OS".
The gem, of course, is pulling out the MarketingName.
You could also do fun things like...
-only show records where MainstreamSupportEndDate < GetDate() (aka, stuff out of support)
-only show records where Manufacturer0 = 'Dell, Inc.'
etc etc etc...
s1.netbios_name0 as 'ComputerName'
,s1.Build01 as 'OSBuild'
,Case when CHARINDEX('(',ldg.groupName) >0 then
rtrim(left(ldg.groupName, CHARINDEX('(',ldg.groupName) -1))
else ldg.groupName end as 'MarketingName'
,csys.Manufacturer0 as 'Make'
,csys.Model0 as 'Model'
from v_LifecycleDetectedGroups ldg
join v_LifecycleDetectedResourceIdsByGroupName lr on lr.GroupName=ldg.GroupName
join v_r_system s1 on s1.ResourceID=lr.ResourceID
left join v_GS_COMPUTER_SYSTEM csys on csys.ResourceID=s1.ResourceID
where ldg.Category in ('WinClient','WinServer')
- Created on .