nagios - Issue with alert for checking a SQL Server service - Stack Overflow

I'm having difficulty configuring an alert rule to monitor the free space of a SQL Server database in LibreNMS.

The service correctly returns the data, indicating the allocated space, the free space, and finally the free space percentage of the database.

check type: mssql
parameters: -H <hostname> -d <DB Name> -u <User> -p <Password> -q "USE [<DB Name>]; SELECT dbf.name AS FileName, dbf.type_desc AS FileType, (dbf.size * 8.0 / 1024) AS AllocatedSpaceMB, ((CAST(FILEPROPERTY(dbf.name, 'SpaceUsed') AS int) * 8.0) / 1024) AS UsedSpaceMB, ((dbf.size * 8.0 / 1024) - (CAST(FILEPROPERTY(dbf.name, 'SpaceUsed') AS int) * 8.0 / 1024)) AS FreeSpaceMB, ROUND(((((dbf.size * 8.0 / 1024) - (CAST(FILEPROPERTY(dbf.name, 'SpaceUsed') AS int) * 8.0 / 1024)) / (dbf.size * 8.0 / 1024)) * 100.0), 2) AS FreeSpacePercentage FROM sys.database_files AS dbf WHERE dbf.type_desc != 'FILESTREAM'"

The result is:

SQL OK: <DB Name>;ROWS;72252.187500;811.375000;71440.812500;98.880000000000000

I am unable to configure the alert rule to notify me when the limit, for example, 90%, is exceeded. The query I am using is the following, but it yields no results:

SELECT * 
FROM devices, services 
WHERE (devices.device_id = ? 
       AND devices.device_id = services.device_id) 
  AND services.service_message REGEXP ".* ([0-9]+(\\.[0-9]+)?)%" 
  AND ROUND(CAST(SUBSTRING_INDEX(service_message, ';', -1) AS DECIMAL(5,2))) <= 40;

I'm having difficulty configuring an alert rule to monitor the free space of a SQL Server database in LibreNMS.

The service correctly returns the data, indicating the allocated space, the free space, and finally the free space percentage of the database.

check type: mssql
parameters: -H <hostname> -d <DB Name> -u <User> -p <Password> -q "USE [<DB Name>]; SELECT dbf.name AS FileName, dbf.type_desc AS FileType, (dbf.size * 8.0 / 1024) AS AllocatedSpaceMB, ((CAST(FILEPROPERTY(dbf.name, 'SpaceUsed') AS int) * 8.0) / 1024) AS UsedSpaceMB, ((dbf.size * 8.0 / 1024) - (CAST(FILEPROPERTY(dbf.name, 'SpaceUsed') AS int) * 8.0 / 1024)) AS FreeSpaceMB, ROUND(((((dbf.size * 8.0 / 1024) - (CAST(FILEPROPERTY(dbf.name, 'SpaceUsed') AS int) * 8.0 / 1024)) / (dbf.size * 8.0 / 1024)) * 100.0), 2) AS FreeSpacePercentage FROM sys.database_files AS dbf WHERE dbf.type_desc != 'FILESTREAM'"

The result is:

SQL OK: <DB Name>;ROWS;72252.187500;811.375000;71440.812500;98.880000000000000

I am unable to configure the alert rule to notify me when the limit, for example, 90%, is exceeded. The query I am using is the following, but it yields no results:

SELECT * 
FROM devices, services 
WHERE (devices.device_id = ? 
       AND devices.device_id = services.device_id) 
  AND services.service_message REGEXP ".* ([0-9]+(\\.[0-9]+)?)%" 
  AND ROUND(CAST(SUBSTRING_INDEX(service_message, ';', -1) AS DECIMAL(5,2))) <= 40;

• sql-server
• nagios

Share Improve this question Follow edited Jan 29 at 19:25 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Jan 29 at 16:24 Luca MaggiLuca Maggi 111 silver badge 2

• Your FreeSpaceMB is only calculating the free space within the file, not on the disk, so I can't see why you'd want an alert for that. I think you'd want sys.dm_os_volume_stats see eg stackoverflow/a/47542177/14868997 – Charlieface Commented Jan 29 at 16:32
• SQL Server doesn't support REGEXP - what dialect of SQL are you using for your query? And where are you running it? – Dale K Commented Jan 29 at 19:01

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

I just took a quick look on your code and two things caught my attention:

1 - I think you could remove the '%' character from your regex search, since there's none on the result. (98.880000000000000).

2 - You want an alert, when the threshold hit 90%, but you're setting it to 40, so it does make sense to not generate any alerts, since the space is never less than 40 (as shown in the result you sent).

You're right, I got confused and missed that "small" detail, but now I've fixed it. If it can be helpful, I'll also share the template that retrieves the data from the service.

Thanks All

**Alert: Spazio Libero Critico nel Database MSSQL**

@php
$message_parts = explode(';', $alert->faults[1]['service_message']);
$percentuale_spazio_libero = isset($message_parts[5]) ? round($message_parts[5], 2) : 'N/A';
@endphp

Nome del Database: {{ isset($message_parts[0]) ? $message_parts[0] : 'N/A' }}

Tipo di File: {{ isset($message_parts[1]) ? $message_parts[1] : 'N/A' }}

Spazio Allocato: {{ isset($message_parts[2]) ? $message_parts[2] : 'N/A' }} MB

Spazio Utilizzato: {{ isset($message_parts[3]) ? $message_parts[3] : 'N/A' }} MB

Spazio Libero: {{ isset($message_parts[4]) ? $message_parts[4] : 'N/A' }} MB

Percentuale di Spazio Libero: {{ $percentuale_spazio_libero }}%