SQL Server Express 2014 check_mssql_health with on LibreNMS

[premtimibrahimi]

Hello,

I just installed check_mssql_health on LibreNMS running with Ubuntu 16.04 and I have a little problem. With the commande line the plugin works fine, i get the correct result. Exemple testing connceted users. I get this result :

root@srv-loki:/usr/lib/nagios/plugins# ./check_mssql_health --server MSSQLSERVER --username 'premtim\root' --password 'Super2010' --mode connection-time --commit OK - 0.01 seconds to connect as premtim\root | 'connection_time'=0.01;1;5;;

I tried different --mode like list all databases or current users and have the same result.

But when I try to use the check_mssql_health on LibreNMS I get the instructions info. Check the image :

Thank you

[lausser]

I have no idea how LibreNMS works nor was check_mssql_health developed with other tools than Nagios in mind. It's probably the backslash in the username you have to quote in a different way. Gerhard

[premtimibrahimi]

Finally made it.

[lausser]

Was it the backslash or how did you solve it? And would it be possible to attach a screenshot here? I'd like to see how MS SQL checks look like in a LibreNMS system.

[premtimibrahimi]

The first thing to do was creating a service check file "check_mssql_health.inc.php" in /opt/librenms/includes/services

Second thing was using these parameters : -username="domain\user" --password="password"

Instead of : --username='domain\user' --password='password'

I'm just getting strange resultat with the database size:

[premtimibrahimi]

[premtimibrahimi]

[lausser]

"strange" means: different Output than with the command line? Or did you expect other numbers? Compared to other tools, check_mssql_health takes the free space on the disk drives into account, so one might see a significant lower percentage of used space.

[premtimibrahimi]

I use this to get the result : --mode database-size --name test --commit I thoght it would check the size of the database not the size of the disk. Is there a way of filtering the output of 3.0000MB like 30000 MB with the parameters ?

[lausser]

Can you run it on the command line and add "-vvvvvvvvvvvv" to it? Then you'll get a lot of Debugging Output. Please copy&paste it here.

[premtimibrahimi]

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::Sybase::DBI::check_messages

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::Sybase::DBI::check_messages

Fri Apr 14 19:39:10 2017: SQL: SELECT @@VERSION ARGS: $VAR1 = [];

Fri Apr 14 19:39:10 2017: RESULT: $VAR1 = [ 'Microsoft SQL Server 2014 - 12.0.2269.0 (X64) Jun 10 2015 03:35:45 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) ' ];

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::DBI::override_opt

Fri Apr 14 19:39:10 2017: AUTOLOAD Monitoring::GLPlugin::Commandline::override_opt

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::DBI::check_messages

Fri Apr 14 19:39:10 2017: SQL: SELECT SYSTEM_USER ARGS: $VAR1 = [];

Fri Apr 14 19:39:10 2017: RESULT: $VAR1 = [ 'premtim\root' ];

Fri Apr 14 19:39:10 2017: SQL: SELECT @@SERVICENAME ARGS: $VAR1 = [];

Fri Apr 14 19:39:10 2017: RESULT: $VAR1 = [ 'MSSQLSERVER' ];

Fri Apr 14 19:39:10 2017: SQL: SELECT CAST(COALESCE(SERVERPROPERTY('IsHadrEnabled'), 0) as int) ARGS: $VAR1 = [];

Fri Apr 14 19:39:10 2017: RESULT: $VAR1 = [ 0 ];

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::DBI::analyze_and_check_database_subsystem

Fri Apr 14 19:39:10 2017: SQL: exec master.dbo.xp_fixeddrives ARGS: $VAR1 = [];

Fri Apr 14 19:39:10 2017: RESULT: $VAR1 = [ [ 'C', 12857 ] ];

Fri Apr 14 19:39:10 2017: SQL:

    SELECT
        name, database_id AS id, state, state_desc
    FROM
        master.sys.databases
    WHERE
        source_database_id IS NULL
    ORDER BY
        name

ARGS: $VAR1 = [];

Fri Apr 14 19:39:10 2017: RESULT: $VAR1 = [ [ 'master', 1, 0, 'ONLINE' ], [ 'model', 3, 0, 'ONLINE' ], [ 'msdb', 4, 0, 'ONLINE' ], [ 'tempdb', 2, 0, 'ONLINE' ], [ 'test', 5, 0, 'ONLINE' ] ];

Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::Component::DatabaseSubsystem::Database::override_opt

Fri Apr 14 19:39:10 2017: AUTOLOAD Monitoring::GLPlugin::Commandline::override_opt

Fri Apr 14 19:39:10 2017: SQL:

    USE
  [test]
    SELECT
      'test',
      ISNULL(fg.name, 'TLOGS'),
      dbf.name,
      dbf.is_media_read_only,
      dbf.is_read_only,
      dbf.is_sparse, -- erstmal wurscht, evt. sys.dm_io_virtual_file_stats fragen
      -- dbf.size * 8.0 * 1024,
      -- dbf.max_size * 8.0 * 1024 AS max_size,
      -- dbf.growth,
      -- FILEPROPERTY(dbf.NAME,'SpaceUsed') * 8.0 * 1024 AS used_size,
      dbf.size,
      dbf.max_size,
      dbf.growth,
      dbf.is_percent_growth,
      FILEPROPERTY(dbf.NAME,'SpaceUsed') AS used_size,
      dbf.type_desc,
      dbf.state_desc,
      UPPER(SUBSTRING(dbf.physical_name, 1, 1)) AS filesystem_drive_letter,
      dbf.physical_name AS filesystem_path
    FROM
      sys.database_files AS dbf --use sys.master_files if the database is read only (more recent data)
    LEFT OUTER JOIN
      -- leider muss man mit AS arbeiten statt database_files.data_space_id.
      -- das kracht bei 2005-compatibility-dbs wegen irgendeines ansi-92/outer-join syntaxmischmaschs
      sys.filegroups AS fg
    ON
      dbf.data_space_id = fg.data_space_id
    WHERE
      dbf.type_desc != 'FILESTREAM'

ARGS: $VAR1 = [];

Fri Apr 14 19:39:10 2017: RESULT: $VAR1 = [ [ 'test', 'PRIMARY', 'test', 0, 0, 0, 384, -1, 128, 0, 296, 'ROWS', 'ONLINE', 'C', 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test.mdf' ], [ 'test', 'TLOGS', 'test_log', 0, 0, 0, 128, 268435456, 10, 1, 59, 'LOG', 'ONLINE', 'C', 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test_log.ldf' ] ];

Fri Apr 14 19:39:10 2017: $self->{components}->{database_subsystem} = Classes::MSSQL::Component::DatabaseSubsystem->new() Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::DBI::check_database_subsystem

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::Component::DatabaseSubsystem::Database::override_opt

Fri Apr 14 19:39:10 2017: AUTOLOAD Monitoring::GLPlugin::Commandline::override_opt

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::Component::DatabaseSubsystem::Database::add_perfdata

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::Component::DatabaseSubsystem::Database::add_perfdata

[DATABASESUBSYSTEM] info: checking databases

[DATABASE] drive_reserve: HASH(0x19f8c80) filesystems: HASH(0x19f2788) full_name: test id: 5 logs_max_size: 2199023255552 logs_max_size_mb: 2097152 logs_size: 1048576 logs_size_mb: 1 logs_used_size: 483328 logs_used_size_mb: 0.4609375 max_size: 0 max_size_mb: 0 messages: HASH(0x19f8f20) name: test rows_max_size: 13484687360 rows_max_size_mb: 12860 rows_size: 3145728 rows_size_mb: 3 rows_used_size: 2424832 rows_used_size_mb: 2.3125 size: 0 size_mb: 0 state: 0 state_desc: online used_size: 0 used_size_mb: 0

[DATAFILEGROUP] database_name: test drive_reserve: HASH(0x19f8da0) filesystems: HASH(0x19f2cb0) formula: g PRIMARY msums 13484687360 (12860MB) full_name: test::PRIMARY max_size: 13484687360 max_size_mb: 12860 name: PRIMARY size: 3145728 size_mb: 3 type: ROWS used_size: 2424832 used_size_mb: 2.3125

[DATAFILE] database_name: test drive: C drive_reserve: HASH(0x19f9808) filegroup_name: PRIMARY filesystems: HASH(0x19f9598) formula: f test ulimt 13484687360 (12860MB) full_name: test::PRIMARY::test growth: 128 growth_desc: unlimited size is_media_read_only: 0 is_percent_growth: 0 is_read_only: 0 is_sparse: 0 max_size: 13484687360 max_size_mb: 12860 name: test path: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test.mdf size: 3145728 size_mb: 3 state: ONLINE type: ROWS used_size: 2424832 used_size_mb: 2.3125

[DATAFILEGROUP] database_name: test drive_reserve: HASH(0x1a04560) filesystems: HASH(0x19f2890) formula: g TLOGS msums 2199023255552 (2097152MB) full_name: test::TLOGS max_size: 2199023255552 max_size_mb: 2097152 name: TLOGS size: 1048576 size_mb: 1 type: LOG used_size: 483328 used_size_mb: 0.4609375

[DATAFILE] database_name: test drive: C drive_reserve: HASH(0x1a04980) filegroup_name: TLOGS filesystems: HASH(0x1a04530) formula: f test_log ulims 2199023255552 (2097152MB) full_name: test::TLOGS::test_log growth: 10 growth_desc: limited to 2TB is_media_read_only: 0 is_percent_growth: 1 is_read_only: 0 is_sparse: 0 max_size: 2199023255552 max_size_mb: 2097152 name: test_log path: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test_log.ldf size: 1048576 size_mb: 1 state: ONLINE type: LOG used_size: 483328 used_size_mb: 0.4609375

[TABLEITEM] drive: C mb_free: 12857

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::DBI::reduce_messages_short

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::DBI::check_messages

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::DBI::check_messages

Fri Apr 14 19:39:10 2017: AUTOLOAD Classes::MSSQL::DBI::nagios_exit

OK - db test allocated 3.0000MB, db test logs allocated 1.0000MB checking databases | 'db_test_alloc_size'=3MB;;;; 'db_test_alloc_logs_size'=1MB;;;; Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: try to clean up command and result files Fri Apr 14 19:39:10 2017: try to clean up command and result files

[lausser]

i confused it with --mode database-free. So, forget it, free pace in he filesytem is not involved here. The 3.0000MB look ok to me, it's the size of the datafile C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test.mdf. Three MB, not threethousand. (with 4 places after the decimal point) MB is the default, you could add "--units KB" as well.

[premtimibrahimi]

Is it normal to have this result with db_test_log_free=2048.00GB since I have only 30GB of disk, tested it with MB and GB ? By the way this is the coolest check I've used since.