Written by Sherry Kissinger.

Installed SQL05, SQL08, SQL12, SQL14 version information via ConfigMgr Hardware Inventory

This routine is an update to this previous post , which was for SQL 2005, SQL 2008, and SQL 2012. This update includes SQL 2014. With John Nelson’s help, here’s a mof edit and a sample report to answer the question of "what version of sql, and what service pack, and/or Cumulative Updates have been applied"

You will likely need to consult with an outside source, like http://www.sqlsecurity.com/faqs-1/sql-server-versions or http://sqlserverbuilds.blogspot.com/ , to answer the question of exactly what cumulative update or sql hotfix is applied, but this mof edit should get you most of the way there.

// This section goes in Configuration.mof, at the bottom

//--------------------------------------------- // SQL 2014 Properties //---------------------------------------------

[Union, ViewSources{"select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,ServiceName,SqlServiceType from sqlServiceAdvancedProperty"},ViewSpaces{"\\\\.\\root\\microsoft\\sqlserver\\computermanagement12"}, dynamic,Provider("MS_VIEW_INSTANCE_PROVIDER")]

class cm_sql14

{
    [PropertySources{"IsReadOnly"}        ] boolean IsReadOnly;
    [PropertySources{"PropertyIndex"},key ] uint32 PropertyIndex;
    [PropertySources{"PropertyName"},key ] string PropertyName;
    [PropertySources{"PropertyNumValue"} ] uint32 PropertyNumValue;
    [PropertySources{"PropertyStrValue"} ] string PropertyStrValue;
    [PropertySources{"PropertyValueType"} ] uint32 PropertyValueType;
    [PropertySources{"ServiceName"},key   ] string ServiceName;
    [PropertySources{"SqlServiceType"},key] uint32 SqlServiceType;
};

//——————————————— // SQL 2012 Properties //———————————————

[Union, ViewSources{"select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,ServiceName,SqlServiceType from sqlServiceAdvancedProperty"},ViewSpaces{"\\\\.\\root\\microsoft\\sqlserver\\computermanagement11"}, dynamic,Provider("MS_VIEW_INSTANCE_PROVIDER")]

class cm_sql12

//——————————————— // SQL 2008 Properties //———————————————

[Union, ViewSources{"select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,ServiceName,SqlServiceType from sqlServiceAdvancedProperty"},ViewSpaces{"\\\\.\\root\\microsoft\\sqlserver\\computermanagement10"}, dynamic,Provider("MS_VIEW_INSTANCE_PROVIDER")]

class cm_sql08

//——————————————— // SQL 2000/2005 Properties //———————————————

class cm_sql2kand05

{
   [PropertySources{"IsReadOnly"}        ] boolean IsReadOnly;
   [PropertySources{"PropertyIndex"},key ] uint32 PropertyIndex;
   [PropertySources{"PropertyName"},key ] string PropertyName;
   [PropertySources{"PropertyNumValue"} ] uint32 PropertyNumValue;
   [PropertySources{"PropertyStrValue"} ] string PropertyStrValue;
   [PropertySources{"PropertyValueType"} ] uint32 PropertyValueType;
   [PropertySources{"ServiceName"},key   ] string ServiceName;
   [PropertySources{"SqlServiceType"},key] uint32 SqlServiceType;
};

// This section goes at the bottom of sms_def.mof if ConfigurationManager 2007

// If CM12, save this section to a txt file ending in a .mof extention, and import into Default client Agent Settings, Hardware Inventory.

[ dynamic, provider("MS_VIEW_INSTANCE_PROVIDER"),
SMS_Report (TRUE),
SMS_Group_Name ("SQL14 Property"),
SMS_Class_ID ("CUSTOM|SQL14_Property|1.0")]
class cm_sql14 : SMS_Class_Template
{
    [ SMS_Report (TRUE) ]       Boolean    IsReadOnly;
    [ SMS_Report (TRUE), key ]    UInt32     PropertyIndex;
    [ SMS_Report (TRUE), key ]    String     PropertyName;
    [ SMS_Report (TRUE) ]       UInt32     PropertyNumValue;
    [ SMS_Report (TRUE) ]       String     PropertyStrValue;
    [ SMS_Report (TRUE) ]       UInt32     PropertyValueType;
    [ SMS_Report (TRUE), key ]    String     ServiceName;
    [ SMS_Report (TRUE), key ]    UInt32     SqlServiceType;
};

//=================SQL 2012 Information

[dynamic, provider("MS_VIEW_INSTANCE_PROVIDER"),
SMS_Report(TRUE),
SMS_Group_Name("SQL12 Property"),
SMS_Class_ID("CUSTOM|SQL12_Property|1.0")]
class cm_sql12 : SMS_Class_Template

{     [SMS_Report(TRUE)    ] boolean IsReadOnly;
    [SMS_Report(TRUE),key] uint32 PropertyIndex;
    [SMS_Report(TRUE),key] string PropertyName;
    [SMS_Report(TRUE)    ] uint32 PropertyNumValue;
    [SMS_Report(TRUE)    ] string PropertyStrValue;
    [SMS_Report(TRUE)    ] uint32 PropertyValueType;
    [SMS_Report(TRUE),key] string ServiceName;
    [SMS_Report(TRUE),key] uint32 SqlServiceType;
};

//=================SQL 2008 Information

[dynamic, provider("MS_VIEW_INSTANCE_PROVIDER"),
SMS_Report(TRUE),
SMS_Group_Name("SQL Property"),
SMS_Class_ID("CUSTOM|SQL_Property|2.0")]
class cm_sql08 : SMS_Class_Template

//==================SQL Information 2000 and 2005

[dynamic, provider("MS_VIEW_INSTANCE_PROVIDER"),
SMS_Report(TRUE),
SMS_Group_Name("SQL Property Legacy"),
SMS_Class_ID("SQL_Property_Legacy|2.0")]

class cm_sql2kand05 : SMS_Class_Template

The below is a sample report.

select sys1.Netbios_name0,
max(Case sql4.PropertyName0 when ‘SKUName’ then
   sql4.PropertySTRValue0 end) as [SQL14 Type] ,
max(Case sql4.PropertyName0 when ‘SPLEVEL’ then
   sql4.PropertyNUMValue0 end) as [SQL14 Service Pack]   ,
max(Case sql4.PropertyName0 when ‘VERSION’ then
   sql4.PropertySTRValue0 end) as [SQL14 Version] ,
max(Case sql4.PropertyName0 when ‘FILEVERSION’ then
   sql4.PropertySTRValue0 end) as [SQL14 CU Version],
max(Case sql3.PropertyName0 when ‘SKUName’ then
   sql3.PropertySTRValue0 end) as [SQL12 Type],
max(Case sql3.PropertyName0 when ‘SPLEVEL’ then
   sql3.PropertyNUMValue0 end) as [SQL12 Service Pack]   ,
max(Case sql3.PropertyName0 when ‘VERSION’ then
   sql3.PropertySTRValue0 end) as [SQL12 Version] ,
max(Case sql3.PropertyName0 when ‘FILEVERSION’ then
   sql3.PropertySTRValue0 end) as [SQL12 CU Version] ,
max(Case sql.PropertyName0 when ‘SKUName’ then
   sql.PropertySTRValue0 end) as [SQL08 Type] ,
max(Case sql.PropertyName0 when ‘SPLEVEL’ then
   sql.PropertyNUMValue0 end) as [SQL08 Service Pack]   ,
max(Case sql.PropertyName0 when ‘VERSION’ then
   sql.PropertySTRValue0 end) as [SQL08 Version] ,
max(Case sql.PropertyName0 when ‘FILEVERSION’ then
   sql.PropertySTRValue0 end) as [SQL08 CU Version] ,
max(Case sql2.PropertyName0 when ‘SKUName’ then
   sql2.PropertySTRValue0 end) as [SQL05 Type] ,
max(Case sql2.PropertyName0 when ‘SPLEVEL’ then
   sql2.PropertyNUMValue0 end) as [SQL05 Service Pack] ,
max(Case sql2.PropertyName0 when ‘VERSION’ then
   sql2.PropertySTRValue0 end) as [SQL05 Version] ,
max(Case sql2.PropertyName0 when ‘FILEVERSION’ then
   sql2.PropertySTRValue0 end) as [SQL05 CU Version]
from v_r_system sys1
left join v_gs_sql_property0 sql on sys1.resourceid=sql.ResourceID
left join v_gs_sql_property_legacy0 sql2 on sys1.ResourceID=sql2.ResourceID
left join v_GS_SQL12_Property0 sql3 on sys1.ResourceID=sql3.ResourceID
left join v_gs_SQL14_Property0 sql4 on sys1.ResourceID=sql4.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’)
or sql4.PropertyName0 in (‘SKUNAME’,'SPLevel’,'version’,'fileversion’)
group by sys1.Netbios_name0

Which would look something similar to this:

sqlreportpicture

Created on 08 May 2014.