Custom Report with SQL Server versions and editions installed

[bendover]

I'd need to make custom report for SCCM 2012 R2 which includes the following:

 

Computer Name, User Name, SQL Version (2005, 2008, 2012, 2014 etc), Edition (Express, Standard etc) and Version Number.

 

Have tried many different solutions but none of them seems to work. Many solutions includes modifying configuration.mof and sms_def.mof files. That's not a problem but every SQL query gives some error message.

[Peter van der Woude]

You might want to provide more details about your configuration, query and error, if you want some serious help.

[bendover]

https://gallery.technet.microsoft.com/scriptcenter/SQL-Version-Report-in-SCCM-61d46820-> Many errors about invalid column names.

 

http://www.myitforum.com/forums/SCCM-Report-to-identify-SQL-Servers-with-version-and-SP-level-m228367.aspx-> Returning 0 rows.

 

http://www.hasmug.com/2014/06/29/improved-reporting-on-sql-instances-in-sccm/-> "Invalid object name 'v_GS_CUSTOM_SQL2014'

 

Just few examples

[GarthMJ]

I hate to say it but you are barely giving anyone anything to go on.

 

Have you imported/edit the MOFs yet?

Have you confirmed that SQL is being inventoried?

Which query did you try in each post?

[bendover]

Yes, I have edited configuration.mof and added custom attributes to Default Client Settings using .mof file. Yes, I have run hardware inventory for all clients. I already found couple of errors; custom attributes added to client settings uses different names than classes in query. There are also syntax errors; if you just copy and paste the sql query it uses wrong quotation marks. Nevermind...this will work soon.

[GarthMJ]

It is very common to have issue with quote marks within blog post, But other than that all SQL query should work.

 

Again without any details as to what errors or issues you are having, there is very little anyone can do to help you.

[bendover]

It's almost working but for some reason can't query SQL Express editions.

 

Query:

 

SELECT sys1.Netbios_Name0 AS Computer, usr1.Full_User_Name0 AS [user], MAX(CASE sql.PropertyName0 WHEN 'SKUName' THEN sql.PropertyStrValue0 END) AS Edition,

MAX(CASE sql.PropertyName0 WHEN 'VERSION' THEN sql.PropertyStrValue0 END) AS Version, MAX(CASE sql.PropertyName0 WHEN 'SPLEVEL' THEN sql.PropertyNumValue0 END) AS [service Pack],

MAX(CASE sql.PropertyName0 WHEN 'FILEVERSION' THEN sql.PropertyStrValue0 END) AS [CU Version]

FROM dbo.v_R_System AS sys1 LEFT JOIN

dbo.v_R_User AS usr1 ON sys1.User_Name0 = usr1.User_Name0 LEFT JOIN

dbo.v_GS_CUSTOM_SQL12_Property0 AS sql ON sys1.ResourceID = sql.ResourceID LEFT JOIN

dbo.v_GS_CUSTOM_SQL14_Property0 AS sql2 ON sys1.ResourceID = sql2.ResourceID LEFT JOIN

dbo.v_GS_CUSTOM_SQL_Property_2_00 AS sql3 ON sys1.ResourceID = sql3.ResourceID

WHERE (sql.PropertyName0 IN ('SKUNAME', 'SPLevel', 'version', 'fileversion')) OR

(sql2.PropertyName0 IN ('SKUNAME', 'SPLevel', 'version', 'fileversion')) OR

(sql3.PropertyName0 IN ('SKUNAME', 'SPLevel', 'version', 'fileversion'))

GROUP BY sys1.Netbios_Name0, usr1.Full_User_Name0

Class extensions -> http://mnscug.org/blogs/sherry-kissinger/328-installed-sql05-sql08-sql12-sql14-version-information-via-configmgr-hardware-inventory

EDIT: Done, also SQL Express Editions are working

[GarthMJ]

Have you confirmed that SQL Express is being inventoried?

[bendover]

SQL query is working and reporting all the needed data including Express. Above example is just the part of the query.

[GarthMJ]

You didn't answer my question. Have you confirmed that SQL Express is being inventoried? aka can you see with within resource explorer?

 

I know for a fact that you can collect SQL express data. I have similar but custom method to collect this data.