SCCM如何可以监控到SQL server的安装版本
问题
全部回复
-
可能是网站在墙外，我帮你把里面的内容复制出来：
This routine is an update to this previous post, which was for SQL 2005, and SQL 2008. This update includes SQL 2012. 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 , 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 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;
};//———————————————
// 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
{
[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;
};// 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;
};//=================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{
[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 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;
};The below is a sample report.
select sys1.Netbios_name0,
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
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_name0The above will result is something similar to this:
Please remember to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
