Create Monitor with SQL Query

Question

Hi

The SCOM Monitor "Database Status" from MSSQL on Windows Management Pack checks if a DB is in offline, recovery, suspect or emergency state. But I can't configure, that it only alerts, when the state is suspect or emergency. Offline and Recovery are not a bad state for me, so i don't wanna an alert.

Is there a way to create my own monitor, that only alerts when the state is offline or emergency? Maybe with the SQL Query SELECT name, state_desc FROM sys.databases?

Kind Regards
Stately

Answer 1

Sure you can query database state and filter out the unwanted:

select name, state_desc  
from sys.databases  
where not state_desc in ('Offline', 'Recovering')  
  

See https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15
for all existing states

Answer 2

Hi @StatelyElf ,

Is there any update on this case?

Check this posts if help:
https://tookitaway.co.uk/2015-10-25/scom-2012-run-an-sql-query-in-a-script-monitor-with-a-run-as-account
https://social.technet.microsoft.com/Forums/Lync/en-US/293e2b8e-4c35-4f33-ba87-0f32dfc4804e/sql-database-alert-scom-2016?forum=operationsmanagerauthoring

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.