ss_win_mssql NaN

[davidefilippi]

Hi all
I've some trouble with this script. I've modified it because I can't use mssql_ functions so I've replaced:
mssql_connect() with sqlsrv_connect()
mssql_fetch_row() with sqlsrv_fetch_array().

If I execute manually the script, this return me some value
# php ss_win_mssql.php '192.168.0.201':'1433' memory 'cactistats' 'cactipass'
memoryhitratio:99,996471405029 totalservermemory:8388312 targetservermemory:8388608

When I execute
# php /var/www/html/cacti/scripts/ss_win_mssql.php ss_win_mssql '192.168.0.201':'1433' memory 'cactistats' 'cactipass'

[no output]

In cacti.log I see
SPINE: Poller[1] WARNING: Invalid Response, Device[13] TH[1] DS[334] SCRIPT: /var/www/html/cacti/scripts/ss_win_mssql.php ss_win_mssql '192.168.0.201':'1433' memory 'cactistats' 'cactipass', output:


Other commands give me
# php ss_win_mssql.php '192.168.0.201':'1433' pageio 'cactistats' 'cactipass'
pagereads:346265262 pagewrites:4124129

# php /var/www/html/cacti/scripts/ss_win_mssql.php ss_win_mssql '192.168.0.201':'1433' pageio 'cactistats' 'cactipass'

[no output]

In cacti.log I see
SPINE: Poller[1] Device[13] TH[1] DS[335] SS[0] SERVER: /var/www/html/cacti/scripts/ss_win_mssql.php ss_win_mssql '192.168.0.201':'1433' pageio 'cactistats' 'cactipass', output: U


Can you help me please?

[davidefilippi]

No answer?

[netniV]

Are you sure that port is opened through the Windows Firewall? Have you tried using SQL Management Studio from another machine to verify connectiivty?

[talkingtoes]

Did you ever get the ss_win_mssql.php script to work with the newer sqlsrv commands? What OS / PHP versions are you running.
If so, can you post a copy of your .php file? I'm fighting it, and losing.
TalkingToes

[netniV]

I will try this myself later to see what I can see.

[netniV]

The template that I found for this is listed as Cacti 0.8.7, where I'm only running 1.2+. Did you follow all the instructions at:
https://docs.cacti.net/usertemplate:hos ... :sqlserver

[talkingtoes]

Yes. The import of the .XML file works still on Cacti 1.2.x . The ss_win_mssql.php script just needs updating for PHP 7.x, and I've given up for a few days.

Ubuntu 19.03 has PHP 7.2 (and the Cacti 1.2.2 package) which does not have a mssql_* command set anymore: https://www.php.net/manual/en/function. ... nnect.php (the Red box states "This function was removed in PHP 7.0.0 "

You have to install the new Microsoft SQL support files, which provide the sqlsrv_* commands. This is a fair write up https://medium.com/@nahomt/using-sql-se ... 5671249c45 but use the 19.04 instead (if I recall what I did last week).

I was able to edit it to get the sqlsrv_connect to work, but editing lines past it elude me. Hence my question if he could post his .php solution file (or if you are able to adapt the few commands.)

Thank you!

[netniV]

I'll take that information and see what I could do.

[netniV]

OK so I got an SQL server instance setup tonight and then used the info above to get SQL connectiivty working from the Linux box to the server. Try the following code to ensure that your connectivirty is working as expected (replacing the <<>> fields as appropriate).

Code: Select all

<?php
$serverName = "<<your serveR>>";
$connectionOptions = [
    "Database" => "master",
    "Uid" => "sa",
    "PWD" => "<<your sa password>>"
]; 
//Establishes the connection 
$conn = sqlsrv_connect($serverName, $connectionOptions);
//Executes a query 
$stmt= sqlsrv_query($conn, "SELECT @@Version as SQL_VERSION");
if ($stmt === false) {
        die('failed to query');
}

if  (sqlsrv_fetch($stmt) === false) {
        die('failed to fetch');
}

$name = sqlsrv_get_field( $stmt, 0);
echo "$name: ";

/*Get the second field of the row as a stream.
Because the default return type for a nvarchar field is a
string, the return type must be specified as a stream. */
$stream = sqlsrv_get_field( $stmt, 1,
                            SQLSRV_PHPTYPE_STREAM( SQLSRV_ENC_CHAR));
if ( $stream !== false ) {
        while( !feof( $stream ))
        {
                $str = fread( $stream, 10000 );
                if ( $str === false) {
                        die ('failed to fread');
                }
                echo $str;
        }
}
/* Free the statement and connection resources. */
sqlsrv_free_stmt( $stmt );
sqlsrv_close( $conn );

Once you save that to a file, then run it:

php -q test_sql.php

You should have something like:

# php -q /root/test_sql.php | more
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)
Aug 22 2017 17:04:49
Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
:

In truth, the later field reads are not needed since we only want the version we can just echo $name and be done but I included them for completeness should you wish to try other queries. Also, whilst testing with the SA password, an production stuff should use a user with the lowest possible privileges. If all that is working for you, then I guess the next step will be to modify the templates.

[talkingtoes]

Thank you.
This confirmed my sqlsrv_* is working fine (The original .ZIP file has a SQL script to create limited cactiuser/password on the MS SQL server, and using it, your script still works .)
Now to change the .PHP file to use the new commands.