Monitoring database query result using SCOM

For monitoring a result from DB, you must use either a script or a PS or VB type, when the script makes a query to the database analyze the information and alert according to the condition detection.

It is common that to monitor multiple servers with different queries, additional monitoring is required when each monitoring has the different settings.

In this article I will explain how this can be done by creating a single generic monitor, when the rest of data is defined in the parameters.

The specified monitor will receive the server name port and the query as parameters, this will allow you to create the monitor once and then override monitor on all requirements.

You can perform the task with two options:

• One way is by using the simple wizard script unit monitor and his parameters, PS or VB script that run the script with arguments and in the wizard you can add the SQL server name and the database and query parameters.
  • Pros – easy to create and edit
  • Cons – Override is hard to maintain, in one argument line you edit all overrides fields
• second way is by creating your custom module type, monitor type with your custom overrides
  • Pros – difference overrides blades
  • Cons – Required working with custom module

Implementation:

Option A:

Script monitor works with property bag, this is the way for a system to transfer query data to one workflow data bus, to learn on how to create simple script monitor you can use this guide – https://blogs.msdn.microsoft.com/tysonpaul/2018/08/30/how-to-analyze-a-scom-property-bag/ written by Tyson Paul.

In script you can add arguments, we can use this arguments to send needed parameters, in this example showing bellow I added three parameters, now in override i can change and targeting this monitor to another database server.

Option B:

Add new custom modules, data source module and monitor type based on this module, the result of this is:

Custom Modules:

Data source module type include the engine the run the script and capture the properties –

New monitor type now is based on data source just created, with your custom overrides’ parameters, and mapping the configuration from data source to the parameters:

Monitor collects all this information to one-unit monitor with this parameter:

The MP with those two options attached to this post, you can import the mp as is.

NOTE:

Attached MP has Run AS Profile, insert your Run as Account in it, the query will run with this user, this is required from the field and each user can be scoped to any agent, more content on how this association works can be find here – https://azurecloudai.blog/2019/06/16/add-run-as-profile-to-scom-unit-monitor/