Improved Reporting on SQL Instances in SCCM

UPDATED 3/20/2015: Now includes SQL 2014 and also there is a post by Jason Sandys that seems to be a more intuitive way for you to report on SQL instances using SCCM: http://blog.configmgrftw.com/sql-server-inventory-using-configmgr/

This came up during our 2014 Q3 user group meeting. Matthew Hudson (ECM MVP) informed us that with SCEP SQL exclusions you’ll need to ensure you exclude the SQL DB/LOG file locations that are not set to the default locations. Methods to determine where they are include via the registry, WMI, or by extending the SCCM hardware inventory.

To extend the SCCM hardware inventory follow these steps and use a revised query in the next paragraph to report on the instances: http://mnscug.org/blogs/sherry-kissinger/328-installed-sql05-sql08-sql12-sql14-version-information-via-configmgr-hardware-inventory

The above solution is great for collecting the SQL instance inventory. Although, when it comes time to reporting on it we can format it in a much more intuitive way (although the query might not be that intuitive :D). Assuming your WMI SQL classes are prefixed with “custom” the following SQL query will pull all instances consolidated in a handful of columns and will exclude instances named SQLEXPRESS or “Windows Internal Database”. If there is an even better way to write this please contact us and we’ll update this post!

 

SELECT 
 [ComputerName],
 [SQL TYPE],
 [SQL Service Pack],
 [SQL Version],
 [SQL CU Version],
 [Instance ID]
FROM 
(
-- SQL 2014
 SELECT
  VRS.Netbios_name0 [ComputerName],
   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 'INSTANCEID' THEN
  sql2014.PropertySTRValue0 END) AS [Instance ID]
 FROM 
  v_r_system_valid VRS
  LEFT JOIN v_GS_CUSTOM_SQL2014 sql2014 ON sql2014.ResourceID = VRS.ResourceID
 WHERE
  sql2014.PropertyName0 IN ('SKUNAME','SPLevel','version','fileversion','INSTANCEID')
 --AND PropertySTRValue0 NOT LIKE 'Express%'
 --AND PropertySTRValue0 NOT LIKE 'Windows Internal Database%'
 GROUP BY 
  VRS.Netbios_name0,
  sql2014.ServiceName0
UNION ALL
 -- SQL 2012
 SELECT
  VRS.Netbios_name0 [ComputerName],
   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 'INSTANCEID' THEN
  sql2012.PropertySTRValue0 END) AS [Instance ID]
 FROM 
  v_r_system_valid VRS
  LEFT JOIN v_GS_CUSTOM_SQL2012 sql2012 ON sql2012.ResourceID = VRS.ResourceID
 WHERE
  sql2012.PropertyName0 IN ('SKUNAME','SPLevel','version','fileversion','INSTANCEID')
 --AND PropertySTRValue0 NOT LIKE 'Express%'
 --AND PropertySTRValue0 NOT LIKE 'Windows Internal Database%'
 GROUP BY 
  VRS.Netbios_name0,
  sql2012.ServiceName0
 -- SQL 2008
 UNION ALL
 SELECT 
  VRS.Netbios_name0 [ComputerName],
  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 'INSTANCEID' THEN
  sql2008.PropertySTRValue0 END) AS [Instance ID]
 FROM
  v_r_system_valid VRS
  LEFT JOIN v_GS_CUSTOM_SQL2008 sql2008 ON sql2008.ResourceID = VRS.ResourceID
 WHERE
  sql2008.PropertyName0 IN ('SKUNAME','SPLevel','version','fileversion','INSTANCEID')
 --AND PropertySTRValue0 NOT LIKE 'Express%'
 --AND PropertySTRValue0 NOT LIKE 'Windows Internal Database%'
 --AND sql2008.ServiceName0 <> 'SQLBrowser'
 GROUP BY 
  VRS.Netbios_name0,
  sql2008.ServiceName0
 -- SQL Legacy
 UNION ALL
 SELECT
  VRS.Netbios_name0 [ComputerName],
  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 'INSTANCEID' THEN
  sqlLgcy.PropertySTRValue0 END) AS [Instance ID]
 FROM
  v_R_System VRS
  LEFT JOIN v_GS_CUSTOM_SQL2000AND2005 sqlLgcy ON sqlLgcy.ResourceID = VRS.ResourceID
 WHERE
  sqlLgcy.PropertyName0 IN ('SKUNAME','SPLevel','version','fileversion','INSTANCEID')
 --AND PropertySTRValue0 NOT LIKE 'Express%'
 --AND PropertySTRValue0 NOT LIKE 'Windows Internal Database%'
 --AND sqlLgcy.ServiceName0 <> 'SQLBrowser'
 GROUP BY
  VRS.Netbios_Name0,
  sqlLgcy.ServiceName0
) SQLInv
WHERE
 [SQL TYPE] NOT LIKE 'Express%'
AND [SQL TYPE] NOT LIKE 'Windows Internal Database%'
ORDER BY
 ComputerName

The result:

Although it’s just returning the SQL instance version, you may want to include SQL branding on your report. Such as “SQL Server 2012”, etc. We’ll save that for a later post and meeting 🙂

Posted in Community Content Tagged with: , , , , , , ,