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.
14,155 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Olaf Helper 45,366 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 No comments

  2. Cris Zhan-MSFT 6,641 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 No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.