How to add Azure SQL Database custom alert queries in SCOM

In this blog, I will focus on Two State / Three State monitor in SCOM for Windows Azure SQL Database. In my previous blog, I talked about custom queries can be defined in Authoring console with option to create Two state or Three State monitoring Windows Azure SQL database. Now let me walkthrough the process.

Scenario: Alerts Notification for deadlocks in Windows Azure SQL database, We are going to create two state monitor alert for Deadlocks count exceeding > 0.

In the Operation Manager Console, go to “Authoring”

Under Management Packs Objects. Right Click on Monitor –> Create a Monitor –> Unit Monitor

Azure SQL Database 1

In “Select a monitor type” wizard. Expand Windows SQL Database.

Select “User-defined SQL Query Two State Monitor” and Select Designation Management Pack

Azure SQL Database 2

Click Next.  In the General Properties.

Enter: “specific friendly name”

Monitor Target” select target Windows Azure SQL database”. Enable Monitor.

Azure SQL Database 3

SQL Query: Database: “master”. (Deadlock query runs in Master database).

Query: type SQL query with SQL Database name

select count(*) as Count from sys.event_logwhere database_name = ‘Enter SQL database Name’and event_type=’deadlock’

Azure SQL Database 4

Test Condition: Select Scalar Value

Azure SQL Database 5

In Test condition: Enter Friend Name

In Cell Value: Equal to 0.

(Value 0 will be the normal status without deadlock. Moment deadlock occur, value will not match with defined value. It will cause Critical Alert Notification)

Azure SQL Database 6Azure SQL Database 7

Configure your schedule: 15 min.  (Here SQL query will run every 15 min to verify the defined value)

Azure SQL Database 8

Click on Next. Leave Configure Health as default and click on Next.

Azure SQL Database 9

Configure Alerts Setting: Check Generate alert for Monitor

Alert Name: Specify Friendly alert name and click on Create.

Azure SQL Database 10

Now custom alert is configured for any deadlock occur in Azure SQL Db. You can use the same procedure for any custom alert using SQL query and Two/ Three State Monitor. Hope this will gives pointers for custom alert management using SCOM.

Happy Reading!


Azure Managed Services