OpsMgr Script: SQL DB Backup test alerting when it shouldn't

Here is an interesting discussion from the support forums (see full discussion here). ComputerBob had some trouble with a community-developed script, so he dressed it up a bit to make it work a bit more reliably it seems. Seemed something worth sharing as it may spark additional discussion and further improvements.

ComputerBob

Says ComputerBob:

I followed Stefan's blog entry here: http://blogs.technet.com/stefan_stranger/archive/2009/02/02/opsmgr-sql-full-or-differential-backup-check.aspx. Everything seems to work fine, except that SCOM creates alerts even when the NumHours property returned from the script is less than the threshold (in my case 25 hours).

I even modified part of the script so that I could tell what was going on.

If CInt(oResults(1)) > iThresholdHours Then
          ' last backup is too old
          Call objBag.AddValue("Reason", "The last full or differential backup for database " & _ 

          strDatabase & " is more than " & oResults(1) & " hours old!")
   Else
           ' backups is ok
           Call objBag.AddValue("Reason", "The last full or differential backup for database " & _ 

           strDatabase & " is not supposed to be alerting because this backup is " & oResults(1) & " hours old.")
End If

"I've got the Healthy and Unhealthy expressions set right (as per his blog), but I still get critical alerts in my console with "The last full or differential backup for database DBNAME is not supposed to be alerting because this backup is 3 hours old." I should never see these, since the rule should consider 3 hours old as Healthy."

The Workaround

"Since the script itself was doing a fine job of figuring out if the backup was under a certain threshold, and SCOM was having a hard time figuring out if 3 was less than 25, I added a line of code to the script. See below:"

If oResults.EOF Then
Call objBag.AddValue("BackupType", "Full or differential backup")
Call objBag.AddValue("NumHours", "9999")
Call objBag.AddValue("Reason", "A full backup for database " & strDatabase & " is never be made!")
Call objBag.AddValue("Status","Bad")Else
Call objBag.AddValue("BackupType", "Full or differential backup")
Call objBag.AddValue("NumHours", CStr(oResults(1)))If CInt(oResults(1)) > iThresholdHours Then
Call objBag.AddValue("Reason", "The last full or differential backup for database " & _

strDatabase & " is more than " & oResults(1) & " hours old!")
Call objBag.AddValue("Status","Bad")

Else
' backups is ok
Call objBag.AddValue("Reason", "The last full or differential backup for database " & _

strDatabase & " SHOULD NOT BE ALERTING BECAUSE IT is less than " & CStr(iThresholdHours) & " hours old.")
Call objBag.AddValue("Status","OK")
End If
End If

"Now, I've set the Healthy and Unhealthy expressions in the monitor to key off of the 'Status' variable instead of trying to compare the 'NumHours' variable. Everything is working fine now. I still have no idea why SCOM had trouble comparing the 2 numbers."

Conclusion

Nice work Bob! This script would go nicely into a 2-state monitor in OpsMgr. For steps on how to do this, see How to create a Script-based 2-State Monitor