Share via

Create Monitor with SQL Query

StatelyElf 21 Reputation points
2020-10-16T11:26:00.61+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,362 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,246 Reputation points
    2020-10-16T13:17:59.247+00:00

    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

    0 comments
  2. Cris Zhan-MSFT 6,616 Reputation points
    2020-10-19T07:03:05.86+00:00

    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.

    0 comments