SQL Version Report in SCCM 2012/R2
The default version reporting for SQL is very limited and in order to improve this we need to extend the SCCM Hardware Inventory.
Prerequesites:
- follow the link for a tutorial on modifying .mof file and create custom classes needed by the report
- the report groups your machines by company and version
SQL
with temp([ComputerName],
[Company],
[SQL TYPE],
[SQL Service Pack],
[SQL Version],
[SQL CU Version],
[Version])
as (
SELECT Distinct
[ComputerName],
[Company],
[SQL TYPE],
[SQL Service Pack],
[SQL Version],
[SQL CU Version],
[Version]
FROM
(
-- SQL 2014
SELECT
VRS.Netbios_name0 [ComputerName], vrs.company0 as 'Company',
MAX(CASE sql2014.PropertyName0 WHEN 'SKUName' THEN
sql2014.PropertySTRValue0 END) AS [SQL TYPE]
,MAX(CASE sql2014.PropertyName0 WHEN 'SPLEVEL' THEN
sql2014.PropertyNUMValue0 END) AS [SQL Service Pack]
,MAX(CASE sql2014.PropertyName0 WHEN 'VERSION' THEN
sql2014.PropertySTRValue0 END) AS [SQL Version]
,MAX(CASE sql2014.PropertyName0 WHEN 'FILEVERSION' THEN
sql2014.PropertySTRValue0 END) AS [SQL CU Version]
,MAX(CASE sql2014.PropertyName0 WHEN 'FILEVERSION'
THEN
case left(sql2014.PropertySTRValue0,4)
when '2014' then '2014'
when '2011' then '2012'
when '2009' then '2008 R2'
when '2007' then '2008'
when '2005' then '2005'
when '2000' then '2000'
else '2014'
end
END) as [Version]
FROM
v_R_System VRS
LEFT JOIN v_GS_CUSTOM_SQL_2014_Property_2_00 sql2014 ON sql2014.ResourceID = VRS.ResourceID
LEFT OUTER JOIN v_ClientCollectionMembers c ON c.ResourceID = vrs.ResourceID
WHERE
sql2014.PropertyName0 IN ('SKUNAME','SPLevel','version','fileversion')
and c.CollectionID= @CollID
AND isnull(sql2014.ServiceName0,0) not like '%EXPRESS%'
GROUP BY
VRS.Netbios_name0,
sql2014.ServiceName0,
vrs.company0
-- SQL 2012
UNION ALL
SELECT
VRS.Netbios_name0 [ComputerName], vrs.company0 as 'Company',
MAX(CASE sql2012.PropertyName0 WHEN 'SKUName' THEN
sql2012.PropertySTRValue0 END) AS [SQL TYPE]
,MAX(CASE sql2012.PropertyName0 WHEN 'SPLEVEL' THEN
sql2012.PropertyNUMValue0 END) AS [SQL Service Pack]
,MAX(CASE sql2012.PropertyName0 WHEN 'VERSION' THEN
sql2012.PropertySTRValue0 END) AS [SQL Version]
,MAX(CASE sql2012.PropertyName0 WHEN 'FILEVERSION' THEN
sql2012.PropertySTRValue0 END) AS [SQL CU Version]
,MAX(CASE sql2012.PropertyName0 WHEN 'FILEVERSION'
THEN
case left(sql2012.PropertySTRValue0,4)
when '2014' then '2014'
when '2011' then '2012'
when '2009' then '2008 R2'
when '2007' then '2008'
when '2005' then '2005'
when '2000' then '2000'
else '2012'
end
END) as [Version]
FROM
V_R_System VRS
LEFT JOIN v_GS_CUSTOM_SQL_2012_Property_2_00 sql2012 ON sql2012.ResourceID = VRS.ResourceID
LEFT OUTER JOIN v_ClientCollectionMembers c ON c.ResourceID = vrs.ResourceID
WHERE
sql2012.PropertyName0 IN ('SKUNAME','SPLevel','version','fileversion')
and c.CollectionID= @CollID
AND isnull(ServiceName0,0) not like '%EXPRESS%'
GROUP BY
VRS.Netbios_name0,
sql2012.ServiceName0,
vrs.company0
-- SQL 2008
UNION ALL
SELECT
VRS.Netbios_name0 [ComputerName], vrs.company0 as 'Company',
MAX(CASE sql2008.PropertyName0 WHEN 'SKUName' THEN
sql2008.PropertySTRValue0 END) AS [SQL TYPE]
,MAX(CASE sql2008.PropertyName0 WHEN 'SPLEVEL' THEN
sql2008.PropertyNUMValue0 END) AS [SQL Service Pack]
,MAX(CASE sql2008.PropertyName0 WHEN 'VERSION' THEN
sql2008.PropertySTRValue0 END) AS [SQL Version]
,MAX(CASE sql2008.PropertyName0 WHEN 'FILEVERSION' THEN
sql2008.PropertySTRValue0 END) AS [SQL CU Version]
,MAX(CASE sql2008.PropertyName0 WHEN 'FILEVERSION'
THEN
case left(sql2008.PropertySTRValue0,4)
when '2014' then '2014'
when '2011' then '2012'
when '2009' then '2008 R2'
when '2007' then '2008'
when '2005' then '2005'
when '2000' then '2000'
else '2008'
end
END) as [Version]
FROM
V_R_System VRS
LEFT JOIN v_GS_CUSTOM_SQL_2008_Property_2_00 sql2008 ON sql2008.ResourceID = VRS.ResourceID
lEFT OUTER JOIN v_ClientCollectionMembers c ON c.ResourceID = vrs.ResourceID
WHERE
sql2008.PropertyName0 IN ('SKUNAME','SPLevel','version','fileversion')
and c.CollectionID= @CollID
AND isnull(sql2008.ServiceName0,0) not like '%EXPRESS%'
AND isnull(sql2008.ServiceName0,0) NOT LIKE 'SQLBrowser'
GROUP BY
VRS.Netbios_name0,
sql2008.ServiceName0,
vrs.company0
-- SQL Legacy
UNION ALL
SELECT
VRS.Netbios_name0 [ComputerName], vrs.company0 as 'Company',
MAX(CASE sqlLgcy.PropertyName0 WHEN 'SKUName' THEN
sqlLgcy.PropertySTRValue0 END) AS [SQL TYPE]
,MAX(CASE sqlLgcy.PropertyName0 WHEN 'SPLEVEL' THEN
sqlLgcy.PropertyNUMValue0 END) AS [SQL Service Pack]
,MAX(CASE sqlLgcy.PropertyName0 WHEN 'VERSION' THEN
sqlLgcy.PropertySTRValue0 END) AS [SQL Version]
,MAX(CASE sqlLgcy.PropertyName0 WHEN 'FILEVERSION' THEN
sqlLgcy.PropertySTRValue0 END) AS [SQL CU Version]
,MAX(CASE sqllgcy.PropertyName0 WHEN 'FILEVERSION'
THEN
case left(sqllgcy.PropertySTRValue0,4)
when '2014' then '2014'
when '2011' then '2012'
when '2009' then '2008 R2'
when '2007' then '2008'
when '2005' then '2005'
when '2000' then '2000'
else '2005'
end
END) as [Version]
FROM
V_R_System VRS
LEFT JOIN v_GS_CUSTOM_SQL_Legacy_Property_2_00 sqlLgcy ON sqlLgcy.ResourceID = VRS.ResourceID
LEFT OUTER JOIN v_ClientCollectionMembers c ON c.ResourceID = vrs.ResourceID
WHERE
sqlLgcy.PropertyName0 IN ('SKUNAME','SPLevel','version','fileversion')
and c.CollectionID= @CollID
AND isnull(sqlLgcy.ServiceName0,0) not like '%EXPRESS%'
AND isnull(sqlLgcy.ServiceName0,0) NOT LIKE 'SQLBrowser'
GROUP BY
VRS.Netbios_Name0,
sqlLgcy.ServiceName0,
vrs.company0
) SQLInv
WHERE
[SQL TYPE] NOT LIKE 'Express%'
AND [SQL TYPE] NOT LIKE 'Windows Internal Database%'
and SUBSTRING([SQL Version], 1, 2) != SUBSTRING([SQL CU Version], 1, 2)
)
SELECT distinct
[Company],
[ComputerName],
[SQL TYPE],
[SQL Service Pack],
[SQL Version],
[SQL CU Version],
[version] as VER,
(CASE
WHEN [sql type] LIKE '%develop%' THEN 'Developer'
WHEN [sql type] LIKE '%standard%' THEN 'Standard'
WHEN [sql type] LIKE '%enterprise%' THEN 'Enterprise'
END) AS 'Version'
FROM temp
order by temp.[Company], ver,version, ComputerName
Report will look like:
https://images-blogger-opensocial.googleusercontent.com/gadgets/proxy?url=http%3A%2F%2F2.bp.blogspot.com%2F-bsRji_iUMWU%2FVNsnGaWQGDI%2FAAAAAAAAPco%2FDtT6RSh049Q%2Fs1600%2FCapturesql1.PNG&container=blogger&gadget=a&rewriteMime=image%2F*
