SCCM Sql Query for Installed SQL Server version 2000,2005, 2008 and 2012 information via ConfigMgr Hardware Inventory

 

Original I took from this site— Thanks Sherry its very nice

http://myitforum.com/cs2/blogs/skissinger/archive/2010/12/20/installed-sql-05-and-08-version-information-via-configmgr-hardware-inventory.aspx

 

Edit the below mentioned HINV Files x:\Conf Manager\inboxes\clifiles.src\hinv\

1.SMS_def.mof

(i).open with notepad of the following file SMS_def.mof
(ii) Add below mentioned property to bottom of the file

// 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.

//=================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;
};

//=================================
//Add these two sections to the bottom of sms_def.mof
//=================SQL Information 2008 and possibly newer

[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

{
[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 Information 2000 (possibly) and 2005

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

class cm_sql2kand05 : 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;
};

2.configuration.mof

(i).open with notepad of the following file configuration.mof
(ii) Add below mentioned property to bottom of the file

//———————————————
// 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

{
[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;
};

//=================================
//Add the below two sections to the bottom of Configuration.mof
//=================================

//———————————————
// SQL 2008 (and possibly higher) 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

{
[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 2000/2005 Properties
//———————————————

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

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;
};

Verify using following query

1.

select sys1.Netbios_name0,
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
where sql.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
or
sql2.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by sys1.Netbios_name0

2.

select sys1.Netbios_name0,
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_property_legacy0 sql2 on sys1.ResourceID=sql2.ResourceID
where
sql2.PropertyName0 in (‘SKUNAME’,’SPLevel’,’version’,’fileversion’)
group by
sys1.Netbios_name0

NOTE

After changes you have to wait as per your Hardware inventory scan cycle or you can change the hardware inventory scan time to your custom time.

Advertisements

4 thoughts on “SCCM Sql Query for Installed SQL Server version 2000,2005, 2008 and 2012 information via ConfigMgr Hardware Inventory

  1. Hi, thanks for the post. I had used similar customization on CM07 where it worked. On CM12 it does not work. Any idea how to fix it?
    I have followed your steps, but v_gs_sql_property0 is not created in database. First part was added imported to Client settings custom classes and second added to configuration.mof.
    Thanks
    David

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s