SQL Server 2005 Templates

[timmeh]

Hi Lints,

If I'm receiving a value when running via the command line but receiving a U result when running via CMDPHP.

What do you think may be the cause?

Many Thanks,
Timmeh!

[slamck]

This is great! Thanks for sharing.

[lints]

Try manually running it via the script server with the error reporting line commented out. If something fails, it should tell you.

[aingeckn]

Hello,

I have applied the templates on my sql server but not working.
It seems to be encrypted sql instance.

what can i do?


sorry for poor english.

[tosage]

Hello aingeckn,

What did you do ?
You have execute the SQL request on your SQL Server 2005 for give permission right at cactistats ?
Give us more information about your problem please

Thnaks !

[leexiaofei05]

Examples of the graphs the templates create.

I try many ways and changed username && password.Please help me.
USE [master]
GO
CREATE LOGIN [cactistats] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC sys.sp_addsrvrolemember @loginame = N'cactistats', @rolename = N'processadmin'
GO
CREATE USER [cactistats] FOR LOGIN [cactistats] WITH DEFAULT_SCHEMA=[dbo]
GO
GRANT SELECT ON [sys].[dm_os_performance_counters] TO [cactistats]
GO


.php also changed password '123'

24-29 and 72-73 #




[root@CactiEZ scripts]# php ss_win_mssql.php ip connections cactistats password
The answer is null.

[Foromias]

Hi Gents:

Unfortunately in my job be cannot use Linux so I forced to install Cacti under Windows. I installed PHP 5 Cacti Version 0.8.8a and works fin but... SQL Server Driver for PHP 5 is not deployed for windows. If you try to use PHP5 with php_mssql.dll it dosen´t work.

I spend a lot of time searching a solution and at last I found it.
I installed http://www.microsoft.com/en-us/download ... x?id=20098 add the extension dll in PHP.ini

extension=php_sqlsrv_54_ts.dll
extension=php_pdo_sqlsrv_54_ts.dll

Well it looks like it must work but, microsoft dosent follow the standards and they decided that MSSQL and SQLSRV Function will be not the same.

Following this info http://blogs.msdn.com/b/brian_swan/arch ... art-2.aspx I "rebuild" ss_win_mssql.php in order to work with SQLSRV Function. attached you can find the result.

Now it is working fine with my SQL servers but maybe somebody can take a look and improve it, specially the template author lints.

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

$connectionInfo = array( "UID"=>$username,
"PWD"=>$password);
$ret = '';

$conn = sqlsrv_connect($host, $connectionInfo);
if( $conn === false )
{
echo "Unable to connect.</br>";
die( print_r( sqlsrv_errors(), true));
}else
{
$perf_counter_table = 'sysperfinfo';

$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 = sqlsrv_query($conn, $sql);

$search = array(' ', '/sec', '(KB)', '/', '-');

while ($row = sqlsrv_fetch_array($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']);


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

}



?>

[mskchn]

Hi- I have did all the steps successfully, But unfortunatly i am not sure in which server's script folder should i put ss_win_mssql.php.

I am using CACTI in Ubuntu and want to monitor SQL 2008.


Thanks
Kumar

[tosage]

If your installation come from Ubuntu Repository, this PHP script must be in your /usr/share/cacti/site/scripts

[monitoringguy]

Hi,

I`m running Cacti on a Windows installation, i have performed the following:

Run the query on the SQL server being monitored
Imported the templates,
Copied the script to the scripts folder
Added to device, created graphs
Installed php extensions in c:\php\ext\
modified php.ini with two lines as per Foromias post
replaced script as per Foromias post

Issue i have is when the extensions are listed in the php.ini apache2 will fall over? Any ideas chaps?

Cheers,

[JJX]

I have a problem monitoring MSSQL 2008 R2. Cacti: 0.8.7g & Debian OS
I created the user according to sql_server_2005-2008.sql.
Then, if i run the script from command line works:

swordfish:/var/www/cacti/scripts# su www-data
\h:\w$ pwd
/var/www/cacti/scripts
\h:\w$ /usr/bin/php /var/www/cacti/scripts/ss_win_mssql.php 192.168.0.15:53262 bckrsttroughput cactistats 12345678901234567
bckrsttroughput:117619348876

I have imported the xml file and created the necessary graphs. From logs:

02/26/2013 04:55:02 PM - CMDPHP: Poller[0] Host[307] DS[4779] WARNING: Result from SERVER not valid. Partial Result: U
02/26/2013 04:55:02 PM - CMDPHP: Poller[0] Host[307] DS[4778] SERVER: /usr/bin/php /var/www/cacti/scripts/ss_win_mssql.php 192.168.0.15:53262 temptables cactistats 12345678901234567, output: U

or

02/26/2013 04:40:02 PM - CMDPHP: Poller[0] Host[307] DS[4770] SERVER: /usr/bin/php /var/www/cacti/scripts/ss_win_mssql.php 192.168.0.15:53262 logflushtraffic cactistats 12345678901234567, output: U
02/26/2013 04:40:02 PM - CMDPHP: Poller[0] Host[307] DS[4770] WARNING: Result from SERVER not valid. Partial Result: U

Any idea why the script fails for all checks ?

[tosage]

Hello JJX, i past my cli :

02/26/2013 05:25:05 PM - SPINE: Poller[0] Host[19] DS[552] SS[2] SERVER: /usr/share/cacti/site/scripts/ss_win_mssql.php ss_win_mssql 10.x.x.x:1433 scans xxxxxxx xxxxxxx, output: fullscans:10992747628 rangescans:14715751137 probescans:68321054497 indexsearches:214385414305

I think the port of the server is not good in your cli or you don't use the 1433 for open an connection with SQL Server ?

David

[JJX]

- I am not using 1433 default port. The port is correct.

- The script connects to MSSQL, the query runs successfully and I see the logs from the server.
As you can see if I run the script manually it works.

- The difference is that after script name I dont use the parameter "ss_win_mssql"

02/26/2013 05:25:05 PM - SPINE: Poller[0] Host[19] DS[552] SS[2] SERVER: /usr/share/cacti/site/scripts/ss_win_mssql.php ss_win_mssql 10.x.x.x:1433 scans xxxxxxx xxxxxxx, output: fullscans:10992747628 rangescans:14715751137 probescans:68321054497 indexsearches:214385414305

If I add this through command line the script fails to run. For example:

swordfish:/var/www/cacti/scripts# php /var/www/cacti/scripts/ss_win_mssql.php 192.168.0.15:53262 transactions cactistats 12345678901234567
transactions:1611526
swordfish:/var/www/cacti/scripts# php /var/www/cacti/scripts/ss_win_mssql.php ss_win_mssql 192.168.0.55:53262 transactions cactistats 12345678901234567

swordfish:/var/www/cacti/scripts#

Am I using a wrong script ? The latest is: cacti087e-mssql-host_template.tgz ?

[phalek]

Does your data-input method "Windows - SQL Server - Transactions" look like this one ?

[JJX]

I attach a screenshot from the same Data Input Method.

Snap1.png (1.48 MiB) Viewed 4853 times

I have removed the parameter "ss_win_mssql" as the script is not running with that parameter.

Can you please tell me which version of php are u using?
Thank you