Monitor SQL Server 2005 problem

[yorklung]

Hlep!I'm new on cacti,I SUSElinux+cacti to monitor sql server2005,but there is no data in the graphic and "nan",check the cacti log show "CMDPHP: Poller[0] Host[21] DS[151] WARNING: Result from SERVER not valid. Partial Result:U"
How can I fix it?

I have followed this site"http://docs.cacti.net/usertemplate:host ... :sqlserver" to bulid sql server host template

[lints]

Hi Yorklung,

Does anything return if you run the script manually?

[Towneh]

Hi Lints, I was wondering if you could assist me? I am using Cacti 0.8.7g and PHP 5.1.6 on CentOS 5.7, I am currently seeing lines of:

12/06/2011 03:34:14 PM - CMDPHP: Poller[0] Host[85] DS[1234] WARNING: Result from SERVER not valid. Partial Result: U
12/06/2011 03:34:14 PM - CMDPHP: Poller[0] Host[85] DS[1235] WARNING: Result from SERVER not valid. Partial Result: U
12/06/2011 03:34:14 PM - CMDPHP: Poller[0] Host[85] DS[1236] WARNING: Result from SERVER not valid. Partial Result: U
12/06/2011 03:34:14 PM - CMDPHP: Poller[0] Host[85] DS[1237] WARNING: Result from SERVER not valid. Partial Result: U

After enabling the errorlog. These lines link back to the host that is using the SQL Server host template.

When running the script manually:

[root@host ~]# /usr/bin/php -q /var/www/html/cacti/scripts/ss_win_mssql.php <ip address>:1433 connections <username> <password>
userconnections:109

I have tried removing the template and the associated queries and re-importing it to no success.

My poller cache appear as:

HOSTNAME - SQL Server - Backup/Restore I/O
Script Server: /var/www/html/cacti/scripts/ss_win_mssql.php ss_win_mssql <ip address>: bckrsttroughput
RRD: /var/www/html/cacti/rra/whieuibdb01_mssql_bckrst_bytes_1246.rrd

HOSTNAME - SQL Server - Buffer Cache
Script Server: /var/www/html/cacti/scripts/ss_win_mssql.php ss_win_mssql <ip address>: buffercache
RRD: /var/www/html/cacti/rra/whieuibdb01_mssql_buffer_cache_1247.rrd

HOSTNAME - SQL Server - Connections
Script Server: /var/www/html/cacti/scripts/ss_win_mssql.php ss_win_mssql <ip address>: connections
RRD: /var/www/html/cacti/rra/whieuibdb01_mssql_conns_1248.rrd

and so on...

I have tried leaving it as it is with the default username and password in the scripts file, and also by adding them manually to every data source, which updates the poller cache correctly.

Is there anything else I can do?

(also sent as a pm)

[lints]

Hi Towneh,

Have you tried running the scripts from a manually started script server?

What about specifying a port number in the data source?

Also, is this a clustered SQL instance?

[Towneh]

1.

Code: Select all

[root@hostname ~]# /usr/bin/php -q /var/www/html/cacti/script_server.php
PHP Script Server has Started - Parent is cmd
/var/www/html/cacti/scripts/ss_win_mssql.php ss_win_mssql <ip address>: connections <myusername> <mypassword>
userconnections:110

[root@hostname ~]# /usr/bin/php -q /var/www/html/cacti/script_server.php
PHP Script Server has Started - Parent is cmd
/var/www/html/cacti/scripts/ss_win_mssql.php ss_win_mssql <ip address>: connections
PHP Warning:  mssql_connect(): message: Login failed for user '<myusername>'. (severity 14) in /var/www/html/cacti/scripts/ss_win_mssql.php on line 31
PHP Warning:  mssql_connect(): Unable to connect to server:  <ip address>:1433 in /var/www/html/cacti/scripts/ss_win_mssql.php on line 31

In the ss_win_mssql.php file I have the following lines:

Code: Select all

$username = ($username == NULL ? '<myusername>' : $username);
$password = ($password == NULL ? '<mypassword>' : $password);

2. Sorry I forgot to mention I also specified the port 1433 when manually adding the username and password for each data source.

3. It is a single instance of SQL Server 2005 running on a Windows 2008 R2 server.

[lints]

So, it seems to work fine when specifying the username/password through the SS, but not when you let the defaults some through in the script. I've checked the two lines you pasted and they're identical to what I have in mine.

Without trying to be obvious or patronizing, have you checked the username and password in the script to make they're right? Are there any special characters in either of them that might cause issues (spaces, slashes, dashes, single or double quotes)?

Have you tried adding some debugging code to the script to see what's being used as username/password on the connection line (31 in your script)? I might give you some information as to what it's doing.

I was just curious about the cluster aspect because if it was, you'd have to point it at the service/application instead of either of the nodes.

[Towneh]

I have just found out what the issue is!

When copying the password from the shell line that works when manually connecting with the script in to the ss_win_mssql.php file, due to the PuTTY translation set defaulting to ISO-8859-1:1998 (Latin-1 West Europe), this was causing issues with one of the characters in the password (£) string being displayed correctly. I had changed the translation set to UTF-8 and although it displayed the character correctly did not fix the issue. In the end we removed the £ character from the password in SQL and vowed in future only to use alphanumeric passwords.

Thank you for the time helping assist with this

[lints]

My pleasure, happy to hear that it's working!