OpsMgr SQL Full or Differential Backup Check

I helped a customer creating a OpsMgr Monitor for checking if the there has been a SQL Full or Differential Backup within a specified number of hours. David Scheltens created the SQL query.

Remark: Please read information on System Center Central for more info on this article.

This is how you could create this kind of monitor in your own environment.

Steps:

1. Create a Timed Script Two State Monitor.
2. Save Monitor in other MP than Default MP.
3. Give the Monitor a Name, like “Custom – SQL Full or Differential Backup Check”.
4. Target the Monitor to SQL Database.
5. Select a suitable Parent Monitor like Availability.
6. Disable Monitor (enable by an Override later).
7. Configure how often this Monitor should run (default is 15 mins, but you could change this depending on the time your SQL backups run)
8. Copy script from here.
   The script uses three parameters (or arguments)
   - Param 0: The SQL connection string for the server
   - Param 1: The Database to use
   - Param 2: The threshold (in hours) to use
9. Configure the Parameters.
     
10. Configure Unhealthy Expression.
    Property[@Name='NumHours'] Greater Than 1 (threshold when your backup should have been run)
    
11. Configure Healthy Expression
    Property[@Name='NumHours'] Less than or equal to 1 (threshold when your backup should have been run)
    
12. Configure Health.
    
13. Configure Alerting
    I used the Reason, BackupType and Number of Hours in the Alert Description.
    
    
14. Open the MP XML file and change the data type in the xml configuration from string to integer. Since making this change, the alerts and state changes seems to occur when at the correct time.
    More info here: (thanks to Daniele Grandini)
    <ErrorExpression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQueryType="Integer">Property[@Name='NumHours'] </XPathQuery>

              </ValueExpression>

              <Operator>Greater</Operator>

              <ValueExpression>

                <ValueType="Integer">20</Value>

              </ValueExpression>

            </SimpleExpression>

          </ErrorExpression>

          <SuccessExpression>

            <SimpleExpression>

              <ValueExpression>

                <XPathQueryType="Integer">Property[@Name='NumHours'] </XPathQuery>

              </ValueExpression>

              <Operator>LessEqual</Operator>

              <ValueExpression>

                <ValueType="Integer">20</Value>

              </ValueExpression>

            </SimpleExpression>

          </SuccessExpression>

15. Enable the Monitor via an Override.

Remark: You may need to Use a Run As Account with the right permission for this Monitor.

Result: