Count Log Entries Stored Procedure and check by Nagios

We created a stored procedure that returns the count of log entries in a table within the last time period.  The resultant count is monitored by Nagios.  When the value is less than "X", an alert is triggered.  I know there will be at least X number of entries in a given time frame because I have other Nagios alerts checking the "front end" of the application.  The alert would signal an issue with the front end not logging data to the backend correctly, eg. an Application Problem.

Below is the stored procedure that accepts TWO variables...

  1. The "identifier" of the application which helps determine the table name (eg. log_appID)
  2. The integer value of minutes back in time, eg. "the last 10" minutes.
USE [PRD_00101]


ALTER PROCEDURE [dbo].[GetLogCountForLastNumberOfMinutes]
(   @PortalID int,
    @NumberOfMinutesBack int -- Number of minutes before the current time
    DECLARE @TableRowCount int, @TableName nvarchar(50), 
    @SQLStatement nvarchar(4000), @SQLParameterList nvarchar(500), 
    @Suffix nvarchar(6);

    SET @TableRowCount = -1;

SELECT @Suffix = CAST(@PortalID AS nvarchar(6))
SELECT @TableName = 'Analysis_Log_' + @Suffix
-- PRINT @PortalID
-- PRINT @NumberOfMinutesBack
-- PRINT DATEADD(mi,-@NumberOfMinutesBack,GETDATE())

SELECT @SQLStatement = 'IF EXISTS (SELECT * FROM sys.indexes 
WHERE object_id = OBJECT_ID(N''[dbo].[' + @TableName + ']''))'

SELECT @SQLStatement = @SQLStatement + 'SELECT @TableRowCount = COUNT(*) 
FROM ' + @TableName + ' WITH (READPAST) WHERE PortalID = @PortalID 
AND hitdatetime > DATEADD(mi,-@NumberOfMinutesBack,GETDATE())'
SET @SQLParameterList = '@TableRowCount INT OUTPUT, @PortalID INT, 
@NumberOfMinutesBack INT';
-- PRINT @SQLStatement
    -- Get The Current Row Count For The Log_XXX table ...
EXECUTE sp_executesql   @SQLStatement, @SQLParameterList, @TableRowCount OUTPUT, 
@PortalID, @NumberOfMinutesBack;
-- PRINT @TableRowCount
SELECT @TableRowCount

Here's an example of the Nagios Command Line Check using the check_mssql plugin.

./check_mssql -H mssqlclus -p 1433 -U username -P password \ 
 -D database -w 2.0 -c 3.0 -q \ 
 "exec database.dbo.GetVisitLogCountForLastNumberOfMinutes \
 appID, 10" -W30: -C10: -s

If there are less than 30 entries in the last 10 minutes, a WARNING will be triggered.  A CRITICAL alert will be triggered if there are less than 10 entries in the last 10 minutes.