cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

AppInsight for SQL Alert for Last Backup

Jump to solution

I was wanting to create an Alert based on the "Last Backup" field of a Database that is monitored by AppInsight for SQL.  For the Value, it seems to want a set Date, and not a relative Date.

Ideally, I would have something like the following

Trigger Alert when any of the following apply

  Trigger Alert when all of the following apply

    Recovery Model is equal to 0  (assuming this is simple)

    Last backup is greater than 25 hours

  Trigger Alert when all of the following apply

    Recovery Model is equal to 3 (Assuming this is full)

    Last backup is greater than 20 minutes

Is this possible?

Thanks,

Justin

1 Solution
Level 13

I believe that this can be done by "Custom SQL Alert" defined over "AppInsight for SQL: Database" object using condition similar to:

WHERE 

(

  ( APM_SqlBbDatabaseAlertsData.RecoveryModel = 3 AND DATEDIFF(HOUR, APM_SqlBbDatabaseAlertsData.LastBackup, GETUTCDATE()) > 25)

  OR

  ( APM_SqlBbDatabaseAlertsData.RecoveryModel = 1 AND DATEDIFF(MINUTE, APM_SqlBbDatabaseAlertsData.LastBackup, GETUTCDATE()) > 20)

)

pastedImage_5.png

For recovery model values see documentation (sys.databases (Transact-SQL)😞

1 = FULL

2 = BULK_LOGGED

3 = SIMPLE

View solution in original post

3 Replies
Level 9

AppInsight for SQL Alert for Last Backup query to limit with custom properties using SWQL:

 

SELECT SqlDatabase.Uri, SqlDatabase.DisplayName, SqlDatabase.lastbackup FROM Orion.APM.SqlDatabase AS SqlDatabase

WHERE
(

( SqlDatabase.[SqlServer].[Node].[CustomProperties].[Device] = 'SQL Server' )
AND
( SqlDatabase.[SqlServer].[Node].[CustomProperties].[Environment]='Prod')
AND
( SqlDatabase.[SqlDatabaseAlert].[RecoveryModel] = '1' )
AND
(HourDIFF(SqlDatabase.[SqlDatabaseAlert].[LastBackup], GETUTCDATE()) > 2)

)

0 Kudos
Level 13

I believe that this can be done by "Custom SQL Alert" defined over "AppInsight for SQL: Database" object using condition similar to:

WHERE 

(

  ( APM_SqlBbDatabaseAlertsData.RecoveryModel = 3 AND DATEDIFF(HOUR, APM_SqlBbDatabaseAlertsData.LastBackup, GETUTCDATE()) > 25)

  OR

  ( APM_SqlBbDatabaseAlertsData.RecoveryModel = 1 AND DATEDIFF(MINUTE, APM_SqlBbDatabaseAlertsData.LastBackup, GETUTCDATE()) > 20)

)

pastedImage_5.png

For recovery model values see documentation (sys.databases (Transact-SQL)😞

1 = FULL

2 = BULK_LOGGED

3 = SIMPLE

View solution in original post

Hello @Petr.Vilem  Can you help to add only specific nodes like using custom properties from the above query?

0 Kudos