|
Cacti服务器ip：192.168.10.112，mssql服务器ip：192.168.1.4 cacti 使用yum方式安装，默认路径为/usr/share/cacti
第二：本例操作环境 [iyunv@i-tcz0hdhc ~]# yum update -y [iyunv@i-tcz0hdhc ~]# uname -a Linux 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 [iyunv@i-tcz0hdhc ~]# more /etc/redhat-release CentOS release 6.6 (Final) [iyunv@i-tcz0hdhc ~]#
第三：必须组件的安装 1、 说明 由于windows sql server 2005 本身不支持snmp。所以要想使用cacti 监控mssql，我们只能通过php连接mssql 2005，获取sql 2005的性能计数器的值。 php连接mssql最常用的方法是使用freetds工具。cacti 服务器上必须安装php-mssql 驱动。
2、 安装freetds和php-mssql [iyunv@i-tcz0hdhc ~]# yum install freetds php-mssql -y Loaded plugins: fastestmirror Setting up Install Process Loading 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.com Package freetds-0.91-2.el6.x86_64 already installed and latest version Package php-mssql-5.3.3-3.el6.x86_64 already installed and latest version Nothing to do [iyunv@i-tcz0hdhc ~]# freetds安装完成后 不需要配置，重启httpd服务后能在phpinfo页面看到php支持mssql了
3、 测试mssql的连接
注意防火墙的设置
[iyunv@i-tcz0hdhc ~]# vi test_mssql_connect.php
[iyunv@i-tcz0hdhc ~]# more test_mssql_connect.php
<?php
if(mssql_connect('192.168.1.4','sa','test')){
echo "success\n";
}
else {
echo "fail";
}
?>
[iyunv@i-tcz0hdhc ~]# php -q test_mssql_connect.php
success
[iyunv@i-tcz0hdhc ~]#
[iyunv@i-tcz0hdhc ~]#
第四：监控脚本配置
1、 在sql server 上新增监控用户
将解压后的sql scripts\sql_server_2005-2008.sql中的内容导入到sql server2005 中。其中的cactistats和CHANGEME分别是新增用户的用户名和密码。可以自行修改。如果此处修改了，下面几行也要做相应修改。之后的ss_win_mssql.php文件中的相应位置也要修改。
成功执行后在会在sql server 上增加一个名为：cactistats密码为：CHANGEME的用户。
2、 测试监控用户连接 新建测试文件 [iyunv@i-tcz0hdhc ~]# vi test_dm_os_performance_counters.php [iyunv@i-tcz0hdhc ~]# 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); ?> [iyunv@i-tcz0hdhc ~]# php test_dm_os_performance_counters.php connent MSSQL succeedArray ( [computed] => 644 [0] => 644 ) [iyunv@i-tcz0hdhc ~]#
3、 添加监控脚本 将解压后的scripts\ss_win_mssql.php上传到cacti服务器的cacti目录下的scripts/下，并赋予权限 [iyunv@i-tcz0hdhc ~]# [iyunv@i-tcz0hdhc ~]# cd /usr/share/cacti/scripts/ [iyunv@i-tcz0hdhc scripts]# chmod 755 ss_win_mssql.php [iyunv@i-tcz0hdhc scripts]# 官方文档中有如下的一段话：“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-73 You'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。所以这相关内容注释。
[iyunv@i-tcz0hdhc ~]# cd /usr/share/cacti/scripts/ [iyunv@i-tcz0hdhc scripts]# vi ss_win_mssql.php [iyunv@i-tcz0hdhc scripts]# more ss_win_mssql.php <?php /* do NOT run this script through a web browser */ if (!isset($_SERVER["argv"][0]) || 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 [counter_name], [cntr_value] FROM ".$perf_counter_table." ". "WHERE ([instance_name] = '' OR [instance_name] = '_Total') AND (". "([object_name] LIKE ('%Plan Cache%') AND [counter_name] IN ". "('Cache Hit Ratio', 'Cache Hit Ratio Base')) OR ". "([object_name] LIKE ('%Buffer Manager%') AND [counter_name] IN ". "('Buffer Cache Hit Ratio', 'Buffer Cache Hit Ratio Base', 'Page reads/sec', 'Page writes/sec')) OR ". "([object_name] LIKE ('%General Statistics%') AND [counter_name] IN ". "('Active Temp Tables', 'User Connections')) OR ". "([object_name] LIKE ('%Databases%') AND [counter_name] IN ". "('Transactions/sec', 'Log Cache Hit Ratio', 'Log Cache Hit Ratio Base', 'Log Flushes/sec', ". "'Log Bytes Flushed/sec', 'Backup/Restore Throughput/sec')) OR ". "([object_name] LIKE ('%Access Methods%') AND [counter_name] IN ". "('Full Scans/sec', 'Range Scans/sec', 'Probe Scans/sec', 'Index Searches/sec', 'Page Splits/sec')) OR ". "([object_name] LIKE ('%Memory Manager%') AND [counter_name] IN ". "('Target Server Memory (KB)', 'Target Server Memory(KB)', 'Total Server Memory (KB)')) OR". "([object_name] LIKE ('%SQL Statistics%') AND [counter_name] IN ". "('SQL Compilations/sec', 'SQL Re-Compilations/sec'))". ")";
$res = mssql_query($sql, $link);
$search = array(' ', '/sec', '(KB)', '/', '-');
while ($row = mssql_fetch_row($res)){ $vals[strtolower(str_replace($search, '', $row[0]))] = (empty($row[1]) ? '0' : $row[1]); }
$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); } ?> [iyunv@i-tcz0hdhc scripts]#
4、 测试监控脚本 [iyunv@i-tcz0hdhc scripts]# [iyunv@i-tcz0hdhc scripts]# /usr/bin/php -q /usr/share/cacti/scripts/ss_win_mssql.php 192.168.1.4 scans cactistats CHANGEME fullscans:4167 rangescans:611197 probescans:130196372 indexsearches:3420215 [iyunv@i-tcz0hdhc scripts]# /usr/bin/php -q /usr/share/cacti/scripts/ss_win_mssql.php 192.168.1.4 connections userconnections:5 [iyunv@i-tcz0hdhc scripts]#
第四：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 | 
QQ群⑧：
窥视卡
雷达卡
发表于 2014-11-3 08:56:31
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡