— SQL 2017
select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql6.PropertyName0 when ‘VERSION’ then
(case
when sql6.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql6.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql6.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql6.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql6.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql6.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql6.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql6.PropertyName0 when ‘SKUName’ then
sql6.PropertySTRValue0 end) as [SQL Type]
,max(Case sql6.PropertyName0 when ‘SPLEVEL’ then
sql6.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql6.PropertyName0 when ‘VERSION’ then
sql6.PropertySTRValue0 end) as [SQL Version]
,max(Case sql6.PropertyName0 when ‘FILEVERSION’ then
sql6.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_SQL17_Property0 sql6 on sys1.ResourceID=sql6.ResourceID
where
sql6.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
union
— SQL 2016
select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql5.PropertyName0 when ‘VERSION’ then
(case
when sql5.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql5.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql5.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql5.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql5.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql5.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql5.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql5.PropertyName0 when ‘SKUName’ then
sql5.PropertySTRValue0 end) as [SQL Type]
,max(Case sql5.PropertyName0 when ‘SPLEVEL’ then
sql5.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql5.PropertyName0 when ‘VERSION’ then
sql5.PropertySTRValue0 end) as [SQL Version]
,max(Case sql5.PropertyName0 when ‘FILEVERSION’ then
sql5.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_SQL16_Property0 sql5 on sys1.ResourceID=sql5.ResourceID
where
sql5.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
union
— SQL 2014
select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql4.PropertyName0 when ‘VERSION’ then
(case
when sql4.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql4.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql4.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql4.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql4.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql4.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql4.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql4.PropertyName0 when ‘SKUName’ then
sql4.PropertySTRValue0 end) as [SQL Type]
,max(Case sql4.PropertyName0 when ‘SPLEVEL’ then
sql4.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql4.PropertyName0 when ‘VERSION’ then
sql4.PropertySTRValue0 end) as [SQL Version]
,max(Case sql4.PropertyName0 when ‘FILEVERSION’ then
sql4.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_SQL14_Property0 sql4 on sys1.ResourceID=sql4.ResourceID
where
sql4.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
union
— SQL 2012
select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql3.PropertyName0 when ‘VERSION’ then
(case
when sql3.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql3.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql3.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql3.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql3.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql3.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql3.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql3.PropertyName0 when ‘SKUName’ then
sql3.PropertySTRValue0 end) as [SQL Type]
,max(Case sql3.PropertyName0 when ‘SPLEVEL’ then
sql3.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql3.PropertyName0 when ‘VERSION’ then
sql3.PropertySTRValue0 end) as [SQL Version]
,max(Case sql3.PropertyName0 when ‘FILEVERSION’ then
sql3.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_SQL12_Property0 sql3 on sys1.ResourceID=sql3.ResourceID
where
sql3.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
union
— SQL 2008
Select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql2.PropertyName0 when ‘VERSION’ then
(case
when sql2.PropertySTRValue0 like ‘9.0%’ then ‘SQL 2005’
when sql2.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql2.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql2.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql2.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql2.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql2.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql2.PropertyName0 when ‘SKUName’ then
sql2.PropertySTRValue0 end) as [SQL Type]
,max(Case sql2.PropertyName0 when ‘SPLEVEL’ then
sql2.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql2.PropertyName0 when ‘VERSION’ then
sql2.PropertySTRValue0 end) as [SQL Version]
,max(Case sql2.PropertyName0 when ‘FILEVERSION’ then
sql2.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_sql_property_2_00 sql2 on sys1.resourceid=sql2.ResourceID
where
sql2.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
union
— SQL 2005
Select
sys1.Netbios_name0 as [Computer Name]
,max(Case sql1.PropertyName0 when ‘VERSION’ then
(case
when sql1.PropertySTRValue0 like ‘9.%’ then ‘SQL 2005’
when sql1.PropertySTRValue0 like ‘10.5%’ then ‘SQL 2008 R2’
when sql1.PropertySTRValue0 like ’10.%’ then ‘SQL 2008’
when sql1.PropertySTRValue0 like ’11.%’ then ‘SQL 2012’
when sql1.PropertySTRValue0 like ’12.%’ then ‘SQL 2014’
when sql1.PropertySTRValue0 like ’14.%’ then ‘SQL 2016’
when sql1.PropertySTRValue0 like ’16.%’ then ‘SQL 2017’
else ‘SQL Other’ End)
end) as [SQL]
,max(Case sql1.PropertyName0 when ‘SKUName’ then
sql1.PropertySTRValue0 end) as [SQL Type]
,max(Case sql1.PropertyName0 when ‘SPLEVEL’ then
sql1.PropertyNUMValue0 end) as [SQL Service Pack]
,max(Case sql1.PropertyName0 when ‘VERSION’ then
sql1.PropertySTRValue0 end) as [SQL Version]
,max(Case sql1.PropertyName0 when ‘FILEVERSION’ then
sql1.PropertySTRValue0 end) as [SQL CU Version]
from v_r_system sys1
left join v_gs_custom_sql_property_legacy_2_00 sql1 on sys1.ResourceID=sql1.ResourceID
where sql1.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0
