- 0 minutes to read

Monitoring Failed LogEvents

💡Detect failed LogEvents early to prevent processing backlogs, data loss, and compliance gaps.

How do I monitor failed LogEvents?

You can use the Nodinite Database Monitoring Agent to monitor the LogEvents table for failed entries. The Database Monitoring Agent provides automated, continuous monitoring with instant alerts through Nodinite Alarm Plugins, eliminating the need for manual SQL Server Agent job configuration.

Why should I use the Database Monitoring Agent?

✅ Automated monitoring - Continuously evaluates your LogEvents table without manual SQL Agent setup
✅ Instant alerts - Trigger notifications via email, Slack, Teams, or custom webhooks when thresholds are exceeded
✅ Centralized visibility - View LogEvents monitoring status alongside all other integration resources in Nodinite Monitor Views
✅ Self-service management - Empower teams to execute troubleshooting scripts on-demand via Remote Actions

How do I create a monitoring stored procedure?

The stored procedure below follows the Nodinite Database Monitoring Agent conventions for SQL Statements monitoring. Deploy this procedure to your SQL Server database, then configure it in Nodinite as described in the SQL Statements documentation.

Key concepts

  • Return codes: -10 = ERROR, 10 = WARNING, 0 = OK
  • SELECT output: The first column of the first row becomes the Log Text displayed in Nodinite resources
  • Thresholds: Adjust @errorLimit and @warningLimit to match your operational tolerances
  • Lookback window: Set @lookbackHours to 0 for all-time counts, or limit to recent failures (e.g., 24 hours)

Run this query on a schedule via the Database Monitoring Agent, or deploy it as a stored procedure in SQL Server Agent. Tune thresholds and the lookback window to match your operational needs.

-- Monitor failed LogEvents and return status codes:
--   -10 = ERROR,  10 = WARNING,  0 = OK
CREATE PROCEDURE dbo.MonitorFailedLogEvents
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @failedCount INT,
        @errorLimit INT = 50,        -- Adjust to your environment
        @warningLimit INT = 10,      -- Adjust to your environment
        @lookbackHours INT = 24,     -- 0 = count all; otherwise limit to recent window
        @ret INT = 0,
        @log NVARCHAR(400);

    SELECT @failedCount = COUNT(1)
    FROM dbo.LogEvents
    WHERE ValidationFailed = 1
      AND (
            @lookbackHours = 0
            OR Created >= DATEADD(hour, -@lookbackHours, SYSUTCDATETIME())
          );

    IF (@failedCount > @errorLimit)
    BEGIN
        SET @log = N'ERROR: ' + CAST(@failedCount AS NVARCHAR(12))
                 + N' failed LogEvents in last ' + CAST(@lookbackHours AS NVARCHAR(6)) + N'h (threshold ' 
                 + CAST(@errorLimit AS NVARCHAR(12)) + N')';
        SELECT @log;
        SET @ret = -10;
    END
    ELSE IF (@failedCount > @warningLimit)
    BEGIN
        SET @log = N'WARNING: ' + CAST(@failedCount AS NVARCHAR(12))
                 + N' failed LogEvents in last ' + CAST(@lookbackHours AS NVARCHAR(6)) + N'h (threshold '
                 + CAST(@warningLimit AS NVARCHAR(12)) + N')';
        SELECT @log;
        SET @ret = 10;
    END
    ELSE
    BEGIN
        SELECT N'OK';
    END

    RETURN @ret;
END;
GO

How do I configure monitoring in Nodinite?

Once the stored procedure is deployed, configure it in Nodinite to enable automated monitoring:

Step 1: Open Database Monitoring Agent configuration

Navigate to Administration → Monitor Management → Monitor Agents → Select your Database Monitoring Agent

Step 2: Add SQL Statement

  • Go to the SQL Databases tab and select your connection
  • Click the SQL Statements tab
  • Click Add to create a new SQL Statement configuration

Step 3: Configure General settings

  • Enabled: ✅ Checked
  • Display Name: LogEvents - Failed Validation Monitor
  • Description: Monitors the LogEvents table for validation failures and triggers alerts when thresholds are exceeded

Step 4: Configure Monitoring Script

  • Command Type: Stored Procedure
  • Execute Command: [dbo].[MonitorFailedLogEvents]

Step 5: Save and synchronize

  • Click Save and close
  • The agent will automatically pick up the new configuration on the next sync cycle
  • The stored procedure will appear as a new Resource in your Monitor Views

Step 6: Assign to Monitor View (optional)

Add the new resource to a Monitor View to enable alerting:

  • Navigate to Administration → Monitor Management → Monitor Views
  • Select or create a Monitor View
  • Add the LogEvents - Failed Validation Monitor resource
  • Configure Alarm Plugins to receive notifications (email, Slack, Teams, webhooks)

How do I test my monitoring configuration?

Tip

Testing your configuration:
After setup, verify monitoring is working by intentionally inserting an invalid LogEvent into the table. The Database Monitoring Agent should detect the failure within the configured polling interval (typically 60-300 seconds) and trigger an alert if thresholds are exceeded.

Test steps

  1. Insert an invalid LogEvent (malformed JSON):

    INSERT INTO dbo.LogEvents (LogEvent, ValidationFailed) 
VALUES (N'{invalid-json', 0);

  2. Wait for the next monitoring cycle (60-300 seconds)

  3. Check the resource status in your Monitor View

  4. Verify that alerts are triggered if thresholds are exceeded

  5. Clean up the test data:

    DELETE FROM dbo.LogEvents 
WHERE LogEvent = N'{invalid-json';

What do the return codes mean?

Note

The Database Monitoring Agent evaluates SQL Statements based on:

  • Return code: -10 = Error state, 10 = Warning state, 0 = OK state
  • SELECT output: First column of first row becomes the Log Text displayed in the resource

See the complete SQL Statements documentation for advanced configuration options, including User Action Scripts for on-demand troubleshooting.

Return code meanings

Return Code State Description
-10 Error Failed LogEvents exceed error threshold (@errorLimit)
10 Warning Failed LogEvents exceed warning threshold (@warningLimit)
0 OK Failed LogEvents below warning threshold or none found

How do I adjust monitoring thresholds?

Edit the stored procedure and modify these variables to match your environment:

@errorLimit INT = 50,        -- Trigger error state above this count
@warningLimit INT = 10,      -- Trigger warning state above this count
@lookbackHours INT = 24,     -- Time window for counting failures

Recommendations:

  • High-volume environments: Increase thresholds (e.g., @errorLimit = 500, @warningLimit = 100)
  • Low-volume environments: Decrease thresholds (e.g., @errorLimit = 10, @warningLimit = 3)
  • Real-time monitoring: Use shorter lookback windows (e.g., @lookbackHours = 1)
  • Trend analysis: Use longer lookback windows (e.g., @lookbackHours = 168 for 7 days)