Cacti 0.8.8b 成功监控mssql 2005
Cacti服务器ip：192.168.10.112，mssql服务器ip：192.168.1.4cacti 使用yum方式安装，默认路径为/usr/share/cacti第二：本例操作环境 # yum update -y# uname -aLinux i-tcz0hdhc 2.6.32-431.29.2.el6.x86_64 #1 SMP Tue Sep 9 21:36:05 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux# more /etc/redhat-release CentOS release 6.6 (Final)#
第三：必须组件的安装1、 说明由于windows sql server 2005 本身不支持snmp。所以要想使用cacti 监控mssql，我们只能通过php连接mssql 2005，获取sql 2005的性能计数器的值。php连接mssql最常用的方法是使用freetds工具。cacti 服务器上必须安装php-mssql 驱动。
2、 安装freetds和php-mssql# yum install freetdsphp-mssql -yLoaded plugins: fastestmirrorSetting up Install ProcessLoading mirror speeds from cached hostfile * base: mirrors.yun-idc.com * epel: mirrors.yun-idc.com * extras: mirrors.yun-idc.com * rpmfusion-free-updates: mirror.bjtu.edu.cn * rpmfusion-nonfree-updates: mirror.bjtu.edu.cn * updates: mirrors.yun-idc.comPackage freetds-0.91-2.el6.x86_64 already installed and latest versionPackage php-mssql-5.3.3-3.el6.x86_64 already installed and latest versionNothing to do# freetds安装完成后 不需要配置，重启httpd服务后能在phpinfo页面看到php支持mssql了
3、 测试mssql的连接
注意防火墙的设置
# vi test_mssql_connect.php
# more test_mssql_connect.php
<?php
if(mssql_connect('192.168.1.4','sa','test')){
echo "success\n";
}
else {
echo "fail";
}
?>
# php -q test_mssql_connect.php
success
#
#
第四：监控脚本配置
Cacti官方模板下载地址：http://docs.cacti.net/templates
Sql server 模板官方安装文档：http://docs.cacti.net/usertemplate:host:microsoft:sqlserver
将http://docs.cacti.net/_media/usertemplate:host:microsoft:cacti087e-mssql-host_template.tgz 下载到本地。解压后得到4个文件夹。
1、 在sql server 上新增监控用户
将解压后的sql scripts\sql_server_2005-2008.sql中的内容导入到sql server2005 中。其中的cactistats和CHANGEME分别是新增用户的用户名和密码。可以自行修改。如果此处修改了，下面几行也要做相应修改。之后的ss_win_mssql.php文件中的相应位置也要修改。
成功执行后在会在sql server 上增加一个名为：cactistats密码为：CHANGEME的用户。
2、 测试监控用户连接 新建测试文件# vi test_dm_os_performance_counters.php # more test_dm_os_performance_counters.php <?php try { $hostname='192.168.1.4'; \\ 数据库ip地址$port=1433; \\ 数据库端口（默认）$dbname="master"; \\ 数据库名（默认）$username="cactistats"; \\ 数据库用户名（必须和前面导入的sql文件中的一致）$pw="CHANGEME"; \\ 数据库用户密码（必须和前面导入的sql文件中的一致）$dbh= new PDO("dblib:host=$hostname:$port;dbname=$dbname","$username","$pw"); } catch (PDOException $e) { echo"Failed to get DB handle: ".$e->getMessage() ."n"; exit; } echo 'connent MSSQL succeed';
#$stmt=$dbh->prepare("SELECT * FROM sys.dm_os_performance_counters"); $stmt=$dbh->prepare("SELECT COUNT (*) FROM sys.dm_os_performance_counters"); $stmt->execute(); while ($row=$stmt->fetch()) { print_r($row); } unset($dbh); unset($stmt); ?># php test_dm_os_performance_counters.php connent MSSQL succeedArray( => 644 => 644)#
3、 添加监控脚本 将解压后的scripts\ss_win_mssql.php上传到cacti服务器的cacti目录下的scripts/下，并赋予权限# # cd /usr/share/cacti/scripts/# chmod 755 ss_win_mssql.php # 官方文档中有如下的一段话：“I use MemCached to speed up the polling process so the code is setup to use it. If you choose not to, comment out (or delete) lines 24-29 and 72-73You'll need to install the MemCached service as well as the PHP libraries which should be available through PECL”。大致意思就是说，ss_win_mssql.php中默认使用的是memcached来取mssql的数据，如果没有安装或是不使用memcached的话。需要将memcached有关的行注释掉。否则无法取到mssql的数据。我这里没有安装memcached。所以这相关内容注释。
# cd /usr/share/cacti/scripts/# vi ss_win_mssql.php # more ss_win_mssql.php <?php/* do NOT run this script through a web browser */if (!isset($_SERVER["argv"]) || isset($_SERVER['REQUEST_METHOD'])|| isset($_SERVER['REMOTE_ADDR'])) { die("<br><strong>This script is only meant to run at the command line.</strong>");}
/* display No errors */error_reporting(0);
if (!isset($called_by_script_server)) { array_shift($_SERVER["argv"]);
print call_user_func_array("ss_win_mssql", $_SERVER["argv"])."\n";}
function ss_win_mssql ($hostname, $cmd, $username = NULL, $password = NULL) { list($host, $port) = explode(':', $hostname); $port = ($port == '' ? '1433' : $port); $username = ($username == NULL ? 'cactistats' : $username); $password = ($password == NULL ? 'CHANGEME' : $password);
$ret = '';
// $MCache_Host = 'localhost'; \\ 注释掉memcached相关内容// $MCache_Port = '11211'; \\ 注释掉memcached相关内容// $cachekey = 'ss_win_mssql:'.$host.'-'.$port; \\ 注释掉memcached相关内容// $MemCache = new Memcache; \\ 注释掉memcached相关内容// $MemCache->connect($MCache_Host, $MCache_Port); \\ 注释掉memcached相关内容// if (! $vals = $MemCache->get($cachekey)){ \\ 注释掉memcached相关内容
if (! $link = mssql_connect($host.':'.$port, $username, $password) ) return;
list($server_version) = mssql_fetch_row(mssql_query("SELECT SERVERPROPERTY('productversion')"));
$perf_counter_table = (substr($server_version, 0, 1) == "8" ? 'sysperfinfo' : 'sys.dm_os_performance_counters');
$sql = "SELECT , FROM ".$perf_counter_table." ". "WHERE ( = '' OR = '_Total') AND (". "( LIKE ('%Plan Cache%') AND IN ". "('Cache Hit Ratio', 'Cache Hit Ratio Base')) OR ". "( LIKE ('%Buffer Manager%') AND IN ". "('Buffer Cache Hit Ratio', 'Buffer Cache Hit Ratio Base', 'Page reads/sec', 'Page writes/sec')) OR ". "( LIKE ('%General Statistics%') AND IN ". "('Active Temp Tables', 'User Connections')) OR ". "( LIKE ('%Databases%') AND IN ". "('Transactions/sec', 'Log Cache Hit Ratio', 'Log Cache Hit Ratio Base', 'Log Flushes/sec', ". "'Log Bytes Flushed/sec', 'Backup/Restore Throughput/sec')) OR ". "( LIKE ('%Access Methods%') AND IN ". "('Full Scans/sec', 'Range Scans/sec', 'Probe Scans/sec', 'Index Searches/sec', 'Page Splits/sec')) OR ". "( LIKE ('%Memory Manager%') AND IN ". "('Target Server Memory (KB)', 'Target Server Memory(KB)', 'Total Server Memory (KB)')) OR". "( LIKE ('%SQL Statistics%') AND IN ". "('SQL Compilations/sec', 'SQL Re-Compilations/sec'))". ")";
$res = mssql_query($sql, $link);
$search = array(' ', '/sec', '(KB)', '/', '-');
while ($row = mssql_fetch_row($res)){ $vals))] = (empty($row) ? '0' : $row); }
$vals['buffercachehitratio'] = $vals['buffercachehitratio'] / $vals['buffercachehitratiobase'] * 100; $vals['logcachehitratio'] = $vals['logcachehitratio'] / $vals['logcachehitratiobase'] * 100; $vals['proccachehitratio'] = $vals['cachehitratio'] / $vals['cachehitratiobase'] * 100; $vals['memoryhitratio'] = $vals['totalservermemory'] / $vals['targetservermemory'] * 100;
unset($vals['buffercachehitratiobase'], $vals['logcachehitratiobase'], $vals['cachehitratiobase'], $vals['cachehitratio']);
// $MemCache->set($cachekey, $vals, FALSE, 15); \\ 注释掉memcached相关内容// } \\ 注释掉memcached相关内容
switch ($cmd){ case "bckrsttroughput": $ret .= 'bckrsttroughput:'.$vals['backuprestorethroughput'].' '; break; case "buffercache": $ret .= 'buffercachehitratio:'.$vals['buffercachehitratio'].' '; break; case "compilations": $ret .= 'compliations:'.$vals['sqlcompilations'].' '; $ret .= 'recompliations:'.$vals['sqlrecompilations'].' '; break; case "connections": $ret .= 'userconnections:'.$vals['userconnections'].' '; break; case "logcache": $ret .= 'logcachehitratio:'.$vals['logcachehitratio'].' '; break; case "logflushes": $ret .= 'logflushes:'.$vals['logflushes'].' '; break; case "logflushtraffic": $ret .= 'bytesflushed:'.$vals['logbytesflushed'].' '; break; case "memory": $ret .= 'memoryhitratio:'.$vals['memoryhitratio'].' '; $ret .= 'totalservermemory:'.$vals['totalservermemory'].' '; $ret .= 'targetservermemory:'.$vals['targetservermemory'].' '; break; case "pageio": $ret .= 'pagereads:'.$vals['pagereads'].' '; $ret .= 'pagewrites:'.$vals['pagewrites'].' '; break; case "pagesplits": $ret .= 'pagesplits:'.$vals['pagesplits'].' '; break; case "proccache": $ret .= 'proccachehitratio:'.$vals['proccachehitratio'].' '; break; case "scans": $ret .= 'fullscans:'.$vals['fullscans'].' '; $ret .= 'rangescans:'.$vals['rangescans'].' '; $ret .= 'probescans:'.$vals['probescans'].' '; $ret .= 'indexsearches:'.$vals['indexsearches'].' '; break; case "temptables": $ret .= 'activetemptables:'.$vals['activetemptables'].' '; break; case "transactions": $ret .= 'transactions:'.$vals['transactions'].' '; break;# case "":# $ret .= ':'.$vals[''].' ';# break; }
return trim($ret);}?>#
4、 测试监控脚本 ## /usr/bin/php -q /usr/share/cacti/scripts/ss_win_mssql.php 192.168.1.4 scans cactistats CHANGEMEfullscans:4167 rangescans:611197 probescans:130196372 indexsearches:3420215# /usr/bin/php -q /usr/share/cacti/scripts/ss_win_mssql.php 192.168.1.4 connections userconnections:5#
第四：cacti配置1、 导入模板将解压后的template\cacti_host_template_windows_-_sql_server.xml导入到cacti中 Console → Import/Export → Import Templates→ 选择文件 → Import 导入后的Host Templates名为 Windows - SQL Server导入后graph templates包含： Windows - SQL Server - Backup/Restore I/O Windows - SQL Server - Buffer Cache Windows - SQL Server - Connections Windows - SQL Server - Log Cache Windows - SQL Server - Log Flush I/O Windows - SQL Server - Log Flushes Windows - SQL Server - Memory Windows - SQL Server - Page I/O Windows - SQL Server - Page Splits Windows - SQL Server - Procedure Cache Windows - SQL Server - Processes Blocked Windows - SQL Server - SQL Compilations Windows - SQL Server - Table Scans Windows - SQL Server - Temp Tables Windows - SQL Server - Transactions
2、 新建一个Devices“Console → Management → Devices → Add”中新建一个Devices。注意“Host Template” 中选择“Windows - SQL Server” 点“Save”后在页面上方点 “Create Graphs for this Host”。 在“Graph Templates”中选择要监控的项，点“Create”后出现“Create Graphs from Data Query ”中要求填写sql server的端口号、用户名、密码。此处如果不填写，将直接使用前面在ss_win_mssql.php中填写的端口号、用户名、密码。
3、 将新建的Devices加入到Graph Trees中（过程略）5分钟后，我们能在graphs中看到mssql的监控图。
这里要注意一下，虽然mssql 2005 不支持snmp，监控也是使用的php脚本，但是rrdtool画图是必须是要snmp的哦，所以在被监控的mssql 2005上的snmp必须要允许cacti服务器，否则就会有数据不出图哦
至此 cacti成功监控mssql 2005
页:
[1]