none
SCCM如何可以监控到SQL server的安装版本 RRS feed

  • 问题

  • SCCM可以监控到Office、Windows等软件的相关版本,但是只有SQL server安装了哪个版本是检测不出来的,请问SCCM是否可以实现SQL server安装版本?
    2017年2月28日 5:25

答案

全部回复

  • 您好,

    可以实现,需要手动修改mof文件。

    请参考下面的链接,通过硬件收集来获取SQL 的安装版本等信息。

    Installed SQL 05, SQL08, and SQL12 version information via ConfigMgr Hardware Inventory

    http://myitforum.com/myitforumwp/2012/02/21/installed-sql-05-sql08-and-sql12-version-information-via-configmgr-hardware-inventory/

     


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • 已标记为答案 张蕊蕊 2017年3月2日 2:34
    2017年3月1日 2:37
    版主
  • 手动修改mof文件有具体步骤吗?链接也打不开?
    2017年3月2日 2:34
  • 可能是网站在墙外,我帮你把里面的内容复制出来:

    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_name0

    The above will result is something similar to this:

    sqlverreport


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    2017年3月2日 6:41
    版主