4 Replies Latest reply on Nov 30, 2012 2:40 PM by erod2727

    Alerting on "Last Database Sync"?

    warbird

      Hey guys,

      I have the "Polling Engine Status" added to my main view.  Within that box, for all 4 of my polling engines, is a field called "Last Database Sync".  I want to set up an alert to trigger when that value is greater than 5 minutes for any of the polling engines.  I haven't found a way to do this but it should be possible, yes?  Any ideas?

      I understand that SW is working on ways for us to be able to monitor the pollers themselves but in the interim, can't we set up an alert to keep an eye on the db syncs?

        • Re: Alerting on "Last Database Sync"?
          warbird

          Never mind.  I just found this thread:

          Re: Building a custom alert based on "Last Database Update"

          Looks like it is not possible to do what I wanted.

            • Re: Alerting on "Last Database Sync"?
              smargh

              We use two background MSSQL Agent jobs to monitor the poller and the  universal device pollers, because the universal poller [job scheduler?] has broken in the  past while the KeepAlive value was updating normally.

              See  http://msdn.microsoft.com/en-us/library/ms190307.aspxhttp://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database  for a screenshot-laden guide to setting up MSSQL mail profiles.

              Note that the script only caters for 1 database engine - you'll  need to update it to check multiple engines.

              Please be aware that I  have no idea if the @LastPollerUpdate variable in the second script  will work in all timezones, maybe particularly in places west of UTC/GMT -  I've never been good with maths.

              Any enhancement suggestions would  be gratefully received. Please let me know if the forum makes the code  unusuable due to newlines etc.

               

              --
              -- Check SolarWinds database is being updated correctly.
              --

              DECLARE @MessageCount INT
              DECLARE @Messagetext VARCHAR(2000)
              DECLARE @Subjecttext VARCHAR(2000)
              DECLARE @LastUpdate DATETIME

              SELECT @LastUpdate = MAX([KeepAlive]) FROM [NetPerfMon].[dbo].[Engines]  WHERE [EngineID] = 1

              IF DATEDIFF(minute,@LastUpdate,GETDATE()) > 6 BEGIN

                      SET @Messagetext = 'The SolarWinds Orion engine has not updated  the database since ' + CAST(@LastUpdate AS VARCHAR) + '. This can be  caused by the connection between SERVER1 and SQLSERVER being  interrupted. The SolarWinds Network Performance Monitor service on  SERVER1 may need to be restarted. This alert will repeat every 10  minutes while the problem exists.'
                      SET @Subjecttext = '*** SolarWinds Orion database not updated  since ' + CAST(@LastUpdate AS VARCHAR) + ' ***'
                     
                      EXEC msdb.dbo.sp_send_dbmail
                              @recipients= N'recipient@yourdomain.local',
                              @body= @Messagetext,
                              @importance= 'High',
                              @subject = @Subjecttext ,
                              @profile_name = 'Default'

              END





              --
              -- Check SolarWinds custom pollers are updating correctly.
              --

              DECLARE @MessageCount INT
              DECLARE @Messagetext VARCHAR(2000)
              DECLARE @Subjecttext VARCHAR(2000)
              DECLARE @LastPollerUpdateUTC DATETIME
              DECLARE @LastPollerUpdate DATETIME

              -- Solarwinds stores this date as UTC
              SELECT @LastPollerUpdateUTC = MAX([DateTime]) FROM  [NetPerfMon].[dbo].[CustomPollerStatus]

              -- Convert to retrieved UTC time to local time by finding
              -- the difference between GETUTCDATE() and GETDATE()
              SELECT @LastPollerUpdate = DATEADD(mi,DATEDIFF(mi,  GETUTCDATE(),GETDATE()),@LastPollerUpdateUTC)

              IF DATEDIFF(hour,@LastPollerUpdate,GETDATE()) > 1 BEGIN

                      SET @Messagetext = 'No SolarWinds Orion custom pollers have  updated since ' + CAST(@LastPollerUpdate AS VARCHAR) + '. The Orion  maintenance staff have been informed. This alert will repeat every two  hours while the problem exists. Orion up/down alerts should continue to  be sent correctly. Alerts which use custom pollers, such as hardware  health information, may be based upon stale data. Automatic scheduled  reports and network discovery may not be working correctly.'
                      SET @Subjecttext = '*** SolarWinds Orion custom pollers not  updated since ' + CAST(@LastPollerUpdate AS VARCHAR) + ' ***'
                     
                      EXEC msdb.dbo.sp_send_dbmail
                              @recipients= N'recipient@yourdomain.local',
                              @body= @Messagetext,
                              @importance= 'High',
                              @subject = @Subjecttext ,
                              @profile_name = 'Default'

              END

            • Re: Alerting on "Last Database Sync"?
              erod2727


              NPM 10.3.1

              I noticed in this version in advanced alert manager there is a Custom SQL Alert (Property Monitor). One thing I noticed is that the Nodes table Last Sync column will not update until the database syncs with teh poliing engine again. So in hopes to monitor teh Last Database Sync my team created the below SQL Alert. Not sure if it works since my database is in sync right now, but wondering if any SQL or NPM gurus could advise if this will work with teh alerting system NPM provides.

               

              Trigger Condition:

              declare @LastSync datetime

               

              select @LastSync=max(lastsync) from Nodes (nolock)

               

              select @LastSync where DATEDIFF(minute,@LastSync,GETDATE()) > 10

               

              Reset Condition:

              declare @LastSync datetime

               

              select @LastSync=max(lastsync) from Nodes (nolock)

               

              select @LastSync where DATEDIFF(minute,@LastSync,GETDATE()) > 10