Install - Host template for MSSQL Server 2000 --> No data

[kangyut]

Dear Sir

i'm try to install Host template for Microsoft SQL Server 2000/2005/2008.
from this link http://docs.cacti.net/usertemplate:host ... :sqlserver

setup it is ok, but no data in graph.

i'm check in "Cacti log"
11/26/2010 04:46:11 PM - CMDPHP: Poller[0] Host[9] DS[19] WARNING: Result from SERVER not valid. Partial Result: U
11/26/2010 04:46:11 PM - CMDPHP: Poller[0] Host[9] DS[20] WARNING: Result from SERVER not valid. Partial Result: U

----------------------------
Please help me, how to fix it?

[lints]

Have you tried running the script manually from the command-line? Does it give you valid output?

[heinzelrumpel]

Have you tried running the script manually from the command-line? Does it give you valid output?

Hi,

I am experiencing exact the same problem. How would I run the script manually?

Thanks

Heinzelrumpel

o.k. just ran the script on the cmd. This is the output

Code: Select all

vm-nagios:/usr/share/cacti/site/scripts# ./ss_win_mssql.php
./ss_win_mssql.php: line 1: ?php: Datei oder Verzeichnis nicht gefunden
./ss_win_mssql.php: line 2: /bin: is a directory
./ss_win_mssql.php: line 3: syntax error near unexpected token `$_SERVER["argv"][0]'
./ss_win_mssql.php: line 3: `if (!isset($_SERVER["argv"][0]) || isset($_SERVER['REQUEST_METHOD'])  || isset($_SERVER['REMOTE_ADDR'])) {'
vm-nagios:/usr/share/cacti/site/scripts#

Weird. It is the original script from cacti

This is the whole script

Code: Select all

<?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 ? 'xxxxxxx' : $username);
        $password = ($password == NULL ? 'xxxxxxx' : $password);

        $ret = '';

/*        $MCache_Host = 'localhost'; */
/*        $MCache_Port = '11211'; */
/*        $cachekey = 'ss_win_mssql:'.$host.'-'.$port; */
/*        $MemCache = new Memcache; */
/*        $MemCache->connect($MCache_Host, $MCache_Port); */
/*        if (! $vals = $MemCache->get($cachekey)){ */

                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); */
/*        } */

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

[lints]

You have to specify an interpreter to use when running the script manually.

Try the code below and post the results:

Code: Select all

php -q ./ss_win_mssql.php <IP_OF_SQL_SERVER>:<TCP_PORT_OF_SQL_SERVER> connections

[heinzelrumpel]

O.k. doing so nothing happens

Code: Select all

vm-nagios:/usr/share/cacti/site/scripts# php -q ./ss_win_mssql.php 192.168.1.84:1433 connections
vm-nagios:/usr/share/cacti/site/scripts#

[lints]

Retry after commenting out this line:

Code: Select all

error_reporting(0);

[heinzelrumpel]

O.K. This time this is the output

Code: Select all

vm-nagios:/usr/share/cacti/site/scripts# php -q ./ss_win_mssql.php 192.168.1.84:1433 connections
PHP Fatal error:  Call to undefined function mssql_connect() in /usr/share/cacti/site/scripts/ss_win_mssql.php on line 31

[heinzelrumpel]

I saw, that php5-mssql was not installed. Now I get an error like this

Code: Select all

vm-nagios:/usr/share/cacti/site/scripts# php -q ./ss_win_mssql.php 192.168.1.84:1433 connections
PHP Warning:  mssql_connect(): message: Fehler bei der Anmeldung für den Benutzer 'administrator'. (severity 14) in /usr/share/cacti/site/scripts/ss_win_mssql.php on line 31
PHP Warning:  mssql_connect(): General SQL Server error: Check messages from the SQL Server (severity 14) in /usr/share/cacti/site/scripts/ss_win_mssql.php on line 31
PHP Warning:  mssql_connect(): Unable to connect to server: 192.168.1.84:1433 in /usr/share/cacti/site/scripts/ss_win_mssql.php on line 31

Seems to be a authentification problem. Am I right?

p.s

So , now I recreated he user on mssql server. The output from running the script again ist this now

Code: Select all

vm-nagios:/usr/share/cacti/site/scripts# php -q ./ss_win_mssql.php 192.168.1.84:1433 connections
PHP Notice:  Undefined index: cachehitratio in /usr/share/cacti/site/scripts/ss_win_mssql.php on line 67
PHP Notice:  Undefined index: cachehitratiobase in /usr/share/cacti/site/scripts/ss_win_mssql.php on line 67
PHP Warning:  Division by zero in /usr/share/cacti/site/scripts/ss_win_mssql.php on line 67
userconnections:95

Well, seems that there are not fatal errors anymore. I will watch the graph. Hopefully it will show some data

[heinzelrumpel]

So, data is growing, execpt "Temp Tables, "Procedure Cache" and "Log Flushes".

[pnukeid]

hello, i've beent try using this template but graph can't showing data..

try on command line..

eka@debian-rssa:/usr/share/cacti/site/scripts$ php -q ./ss_win_mssql.php 192.168.2.201:1433 memory
memoryhitratio:100 totalservermemory:12431152 targetservermemory:12431152

it's ok alright ?

but on graph value still NAN

cacti.JPG (39.12 KiB) Viewed 12298 times

and still have error on cacti log.

cacti1.JPG (16.72 KiB) Viewed 12298 times

anyone know how fix problem ??

[lints]

So, data is growing, execpt "Temp Tables, "Procedure Cache" and "Log Flushes".

So I just noticed this post. I think I'm not getting emails to say that somebody posted on a watched thread..

In any event, I think some of these metrics aren't available on SQL Server 2000. If you open the script copy the query and it it manually through SQL Management Studio you'll see which fields are returned and which ones aren't.

[lints]

hello, i've beent try using this template but graph can't showing data..

try on command line..

eka@debian-rssa:/usr/share/cacti/site/scripts$ php -q ./ss_win_mssql.php 192.168.2.201:1433 memory
memoryhitratio:100 totalservermemory:12431152 targetservermemory:12431152

it's ok alright ?

but on graph value still NAN

cacti.JPG

and still have error on cacti log.

cacti1.JPG

anyone know how fix problem ??

Sadly, I can't say I do.
Have you tried running the script with the Script Server and seeing what it returns that way?

[seanli]

Hi pnukeid,
I got same problem and fixed by changing "Data Input Methods" in Cacti's configuration as below:

1. go "Data Input Methods" and choice one of the Data Input Methods , like "Windows - SQL Server - Memory"
2. change "Input Type" to "Script/Command"
3. change "Input String" form "<path_cacti>/scripts/ss_win_mssql.php ss_win_mssql <hostname>:<port> memory <user> <password>"
to "<path_php_binary> -q <path_cacti>/scripts/ss_win_mssql.php <hostname>:<port> memory <user> <password>"

Repeat those steps for all your SQL Server Data Input Methods...
Hope this could solve your problem, good luck!

Regards,
Sean

BTW, my environment is :
Cacti Version 0.8.7g
Cacti OS unix
SNMP Version NET-SNMP version: 5.5
RRDTool Version RRDTool 1.3.x
PHP Version 5.2.17

[wincakti]

I had some same issues but resolved it by removing te SQL port in the script ss_win_mssql.php

# if (! $link = mssql_connect($host.':'.$port, $username, $password) )
if (! $link = mssql_connect($host, $username, $password) )

Also installed the memcache as I am on windows it was a new service.

Now all works fine except one graph:

Page I/O

The error is as follows:


Warning: preg_match() [function.preg-match]: Delimiter must not be alphanumeric or backslash in D:\wamp\www\cacti\lib\rrd.php on line 1082

Warning: preg_match() [function.preg-match]: Delimiter must not be alphanumeric or backslash in D:\wamp\www\cacti\lib\rrd.php on line 1085

Warning: preg_match() [function.preg-match]: Delimiter must not be alphanumeric or backslash in D:\wamp\www\cacti\lib\rrd.php on line 1088

Warning: preg_match() [function.preg-match]: Delimiter must not be alphanumeric or backslash in D:\wamp\www\cacti\lib\rrd.php on line 1091

Warning: preg_match() [function.preg-match]: Delimiter must not be alphanumeric or backslash in D:\wamp\www\cacti\lib\rrd.php on line 1094

Warning: preg_match() [function.preg-match]: Delimiter must not be alphanumeric or backslash in D:\wamp\www\cacti\lib\rrd.php on line 1097

Warning: preg_match() [function.preg-match]: Delimiter must not be alphanumeric or backslash in D:\wamp\www\cacti\lib\rrd.php on line 1100

Warning: preg_match() [function.preg-match]: Delimiter must not be alphanumeric or backslash in D:\wamp\www\cacti\lib\rrd.php on line 1103

d:/rrdtool/rrdtool.exe graph - \
--imgformat=PNG \
--start=1323254028 \
--end=1323340428 \
--title="WebSQL - SQL Server - Page I/O" \
--base=1000 \
--height=150 \
--width=600 \
--alt-autoscale-max \
--lower-limit=0 \
COMMENT:"From 2011-12-07 11\:33\:48 To 2011-12-08 11\:33\:48\c" \
COMMENT:" \n" \
--vertical-label="IO/sec" \
--slope-mode \
--font TITLE:12:D:\rrdtool\fonts\DejaVuSansMono-Roman.ttf \
--font AXIS:8:D:\rrdtool\fonts\DejaVuSansMono-Roman.ttf \
--font LEGEND:10:D:\rrdtool\fonts\DejaVuSansMono-Roman.ttf \
--font UNIT:8:D:\rrdtool\fonts\DejaVuSansMono-Roman.ttf \
DEF:a="D\:/wamp/www/cacti/rra/websql_mssql_page_reads_58.rrd":mssql_page_reads:AVERAGE \
DEF:b="D\:/wamp/www/cacti/rra/websql_mssql_page_reads_58.rrd":mssql_page_writes:AVERAGE \
CDEF:cdefi=ALL_DATA_SOURCES_NODUPS \
AREA:a#686240FF:"Reads" \
GPRINTLAST:" Current\:%8.2lf %s" \
GPRINTAVERAGE:"Average\:%8.2lf %s" \
GPRINTMAX:"Maximum\:%8.2lf %s\n" \
AREA:b#55AD84FF:"Writes":STACK \
GPRINTLAST:"Current\:%8.2lf %s" \
GPRINTAVERAGE:"Average\:%8.2lf %s" \
GPRINTMAX:"Maximum\:%8.2lf %s\n" \
LINE1:cdefi#FFD660FF:"Total" \
GPRINT:cdefi:LAST:" Current\:%8.2lf %s" \
GPRINT:cdefi:AVERAGE:"Average\:%8.2lf %s" \
GPRINT:cdefi:MAX:"Maximum\:%8.2lf %s\n"

Hopefully someone can guide me in the right direction.

System:
ESX host running w2008
PHP 5.2.11 (wamp)
memcache
rrdtool 1.2.15
cacti 0.8.7.e

[lints]

Well, I don't think the problem is related to the script since you're talking about a graphing issue as opposed to a poller issue.

I suspect however that it might have to do with your old-ish version of RRDTool. The latest version is 1.4.5, but you're running 1.2.15. Something in that graph might require a more recent version. Transparency (or alpha blending) comes to mind, but there might be something else.