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 🙂
You must be logged in to post a comment.