MSSQL - User Connections

[mlabbepg]

Hi,

Is there any documentation on the different modes of check_mssql_server.php?

Specifically, we're trying to understand the logic for "User connections" returning a value in KB while we would expect the check to return the number of user connexions to the SQL server.

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.php -H 192.168.100.50 --checktype 'server' -U 'username' -P 'secret' --perftype default -p port --mode usercons --warning '20' --critical '50'
CRITICAL: User Connections is 1229.0KB|user_connections=1229.0KB;20;50;;

[gsmith]

Hi,

That's a bug.

If you run /usr/ocal/nagios/libexec/check_mssql_server.php -h you will get a list of options.

I used your command against my mssql server and threw in high verbosity using -vvv and I can see
the raw response from the mssql server:

Code: Select all

DEBUG: [MSSQLQuery::run_on_connection] result [array (
  0 =>
  array (
    'value' => '4',
    'utctimestamp' => '1623773896',
  ),
)] (2156)

So I have four users logged in, but I get an answer of 4.0 KB

Full trace:

Code: Select all

[root@gs-cent7-23-94 tmp]# ./check_mssql_server.php -vvv -H 192.168.223.12 --checktype 'server' -U 'gsmith' -P 'xxxxx' -p 1433 --mode usercons --warning '20' --critical '50'
DEBUG: [parse_specs] Adding verbosity... Original Log Level [300], New Log Level [100] (1175)
NOTICE: [parse_specs] Adding verbosity... Original Log Level [WARNING], New Log Level [DEBUG] (1176)
DEBUG: [parse_specs] Options
array (
  'checktype' => 'server',
  'hostname' => '192.168.223.12',
  'username' => 'gsmith',
  'password' => 'xxxxx',
  'port' => '1433',
  'warning' => '20',
  'critical' => '50',
  'verbose' =>
  array (
    0 => false,
    1 => false,
    2 => false,
  ),
  'mode' => 'usercons',
) (1222)
DEBUG: [Check::__construct]  (1483)
DEBUG: [Check::run_check]  (1491)
DEBUG: [Check::run_check] MODES[usercons]array (
  'help' => 'User Connections',
  'stdout' => 'User Connections is @resultKB',
  'label' => 'user_connections',
  'query' => 'SELECT cntr_value as value, DATEDIFF(SECOND, \'1970-01-01\', GETUTCDATE()) as utctimestamp FROM @table WHERE counter_name=\'@counter_name\' AND instance_name=\'@instance_name\';',
  'counter_name' => 'User Connections',
  'instance_name' => '',
  'type' => 'standard',
  'unit' => 'KB',
) (1494)
DEBUG: [Check::run_check] instancename [] (1497)
INFO: [Check::run_check] Connecting to dblib db_dsn [dblib:host=192.168.223.12:1433;dbname=master;charset=UTF8] (1531)
INFO: [Check::run_check] Successful connecting to 192.168.223.12 [0.006844] (1550)
DEBUG: [Check::run_check] *** Run Only One Test (1575)
DEBUG: [Check::execute_query]  (1587)
INFO: [Check::execute_query] MODES name [usercons] (1599)
DEBUG: [Check::execute_query] MODES mode array (
  'help' => 'User Connections',
  'stdout' => 'User Connections is @resultKB',
  'label' => 'user_connections',
  'query' => 'SELECT cntr_value as value, DATEDIFF(SECOND, \'1970-01-01\', GETUTCDATE()) as utctimestamp FROM @table WHERE counter_name=\'@counter_name\' AND instance_name=\'@instance_name\';',
  'counter_name' => 'User Connections',
  'instance_name' => '',
  'type' => 'standard',
  'unit' => 'KB',
) (1600)
DEBUG: [Check::execute_query] mode [usercons] queryType [standard] counterName [User Connections] instance_name [] --instancename [] (1619)
DEBUG: [Check::execute_query] queryType [standard] (1625)
INFO: [Check::execute_query] standard: sqlQuery [SELECT cntr_value as value, DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) as utctimestamp FROM sys.dm_os_performance_counters WHERE counter_name='User Connections' AND instance_name='';] (1648)
DEBUG: [BaseQuery::__construct]  (1952)
DEBUG: [BaseQuery::__construct] query [SELECT cntr_value as value, DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()) as utctimestamp FROM sys.dm_os_performance_counters WHERE counter_name='User Connections' AND instance_name='';] options (Array) stdout [User Connections is @resultKB] label [user_connections] unit [KB] modifier [] queryType [standard] (1961)
DEBUG: [MSSQLQuery::__construct]  (2121)
DEBUG: [MSSQLQuery::test]  (2229)
DEBUG: [MSSQLQuery::run_on_connection]  (2127)
DEBUG: [MSSQLQuery::run_on_connection] returnCode [1] (2135)
DEBUG: [MSSQLQuery::run_on_connection] result [array (
  0 =>
  array (
    'value' => '4',
    'utctimestamp' => '1623773896',
  ),
)] (2156)
INFO: [MSSQLQuery::test] queryDuration [0.001369] (2240)
DEBUG: [MSSQLQuery::test] calculatedResult [4] (2241)
DEBUG: [MSSQLQuery::test] returnCode [1] (2242)
DEBUG: [MSSQLQuery::calculate_result]  (2209)
INFO: [MSSQLQuery::calculate_result] calculatedResult [4] modifier [1] (2211)
DEBUG: [MSSQLQuery::calculate_result] calculate [4] (2216)
DEBUG: [MSSQLQuery::calculate_result] calculatedResult [4.0] (2220)
DEBUG: [MSSQLQuery::sigFig]  (2277)
DEBUG: [sigFig] decimalPart [.0] decimalCount [1] integerPart [4] integerCount [1]
INFO: [MSSQLQuery::test] outputMsg [standard duration=0.001369 seconds.] (2263)
DEBUG: [MSSQLQuery::test] calculatedResult [4.0] finalResult [4.0] (2264)
DEBUG: [BaseQuery::process_results]  (1977)
DEBUG: [BaseQuery::process_results] input queryDuration [0.001369] options (Array) state [0] outputMsg [standard duration=0.001369 seconds.] (1978)
DEBUG: [BaseQuery::check_nagios_threshold]  (2056)
DEBUG: [BaseQuery::check_nagios_threshold] threshold [20], value [4.0] (2058)
DEBUG: [BaseQuery::check_nagios_threshold] start [0] > end [20] (2081)
DEBUG: [BaseQuery::check_nagios_threshold] inside (2090)
DEBUG: [BaseQuery::process_results] Check Warning Threshold: exitCode [0 ] state [OK] outputMsg [self::standard duration=0.001369 seconds.] (2009)
DEBUG: [BaseQuery::check_nagios_threshold]  (2056)
DEBUG: [BaseQuery::check_nagios_threshold] threshold [50], value [4.0] (2058)
DEBUG: [BaseQuery::check_nagios_threshold] start [0] > end [50] (2081)
DEBUG: [BaseQuery::check_nagios_threshold] inside (2090)
DEBUG: [BaseQuery::process_results] Check Critical Threshold: exitCode [0 ] state [OK] outputMsg [self::standard duration=0.001369 seconds.] (2023)
DEBUG: [BaseQuery::process_results] mappedResult: [User Connections is 4.0KB] stdout [User Connections is @resultKB] queryResult [4.0] (2034)
INFO: [BaseQuery::process_results] outputMsg: [OK: User Connections is 4.0KB|user_connections=4.0KB;20;50;;
] (2047)
DEBUG: [BaseQuery::process_results] @state [OK] @stdout [User Connections is @resultKB] @label [user_connections] @result [User Connections is 4.0KB] @unit [KB] @warning [20] @critical [50] (2048)
DEBUG: [nagios_exit]  (3011)
OK: User Connections is 4.0KB|user_connections=4.0KB;20;50;;

I'll file a bug report to get that fixed.

Let me know if I can close this out please.

Thanks

[mlabbepg]

Thanks for the explanations. I didn't think we had that many connections on this sql server, and didn't take time (yet) to check the count directly in SSMS to compare with the result nagios returns in KB.

It makes sense now

Is there a way to get the new check_mssql_server.php alone when the bug gets fixed, or do I need to wait for a future nagos-xi upgrade?

[gsmith]

You can usually install the plugins separately, since that is required if you are running Nagios Core vs. Nagios XI.
That is, for plugins supplied with the system, which I believe this one is. Worse case is you could do an install
on a machine and not license it, and you'd be able to pick up the plugin that way.

The devs confirmed that this is a pretty low priority fix.

You could always try your hand at fixing the plugin yourself. If you go that route remember to keep a copy
off to the side so you can get it back if you have to do a re-install or upgrade.

Let me know if you need anything else of if I can close this out.

Thanks

[mlabbepg]

Thanks. You can lock the thread.