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:

 

Some time ago I created a Tutorial How to Associate a Run As Account to a Monitor.

 

In Operations Manager 2007, Run As Profiles and Run As Accounts are used to select users with the privileges needed for running rules, tasks, and monitors. Management Pack authors create a rule, task, or monitor, and then associate it with a Run As Profile. The named Run As Profile is imported along with the Management Pack into Operations Manager 2007.

The Operations Manager 2007 administrator creates a named Run As Account and specifies users and groups. The administrator then adds the Run As Account to the Run As Profile and specifies the target computers that the account should run on.

The goal in this short tutorial is to create a Timed Script Two State Monitor and associate a run as account to this monitor and save it all in a Management Pack. The Timed Script Two State Monitor is a vbscript that uses WMI to check if BizTalk orchestrations are not started.

I’ve got some great help from Jakub Oleksy (https://blogs.msdn.com/jakuboleksy/default.aspx) and Steve Wilson (https://www.authormps.com/dnn/)

You can download the tutorial here.

https://skydrive.live.com/redir?resid=3AC99C5995164F2B!4978&authkey=!AEVMQobLfXesYG8

Comments

• Anonymous
  January 01, 2003
  Check out the blogpost on System Center Central about an issue with above blogpost. http://www.systemcentercentral.com/BlogDetails/tabid/143/IndexID/42852/Default.aspx

• Anonymous
  January 01, 2003
  Hi Sergey, I uploaded the script to my skydrive. Try that. Stefan

  • Anonymous
    March 29, 2017
    Hi Stefan,no idea where your skydrive is...do you perhaps a link or a direct link to the script?Thanks!
    • Anonymous
      March 30, 2017
      Hi Some One,Try the following link: https://1drv.ms/t/s!AitPFpVZnMk6pnJFTEKGy313rGBv/Stefan

• Anonymous
  January 01, 2003
  The link to the script is 404.

• Anonymous
  January 01, 2003
  Hi Stefan, Excellent blog! I recently came across a db that had a while space in the name ie. "DB Test" and so when the three parameters are passed to the vbscript there is now a total of four input parameters and the vbscript fails and its logged in the event viewer. Have you worked out a how to resolve this? Cheers, MPK  

• Anonymous
  June 15, 2010
  PK and I collaborated to resolve the 'space in the DB name issue'.  I've documented the fix on the systemcentercentral site.  Stefan, you may want to revise your process above and change the screenshots to show this difference.  It should fix the problem for anyone that has a space in the DB name (Bad practice, btw!) and should not interfere with normal operation of the script for other DBs.   For anyone that for some reason can't get to the SCC site, it's simply a matter of adding quotations around the 2nd parameter.  That way when the DB name is passed to the script, it's recognized as a single variable instead of multiple.

• Anonymous
  January 02, 2013
  Stefan, can you please post the script here? The link doesn't work.

• Anonymous
  March 26, 2013
  please post the script here. The link doesn't work (404).