Submit a ticketCall us

Quickly Address Software Vulnerabilities
Patch Manager is an intuitive patch management software which extends the capabilities of WSUS and SCCM to not only patch Windows® servers and workstations, and Microsoft® applications, but also other 3rd-party applications which are commonly exploited by hackers. Learn more about our patch management solution.

 

Home > Success Center > Database Performance Analyzer (DPA) > Database Performance Analyzer Getting Started Guide > Alerts > Create a custom alert to display the name of the active node in a SQL Server cluster

Create a custom alert to display the name of the active node in a SQL Server cluster

Created by Melanie Boyd, last modified by Melanie Boyd on Sep 29, 2016

Views: 28 Votes: 0 Revisions: 4
DPA_GS_Home.png

Custom alerts in DPA are user-defined SQL statements or stored procedures that run against a monitored database or the DPA repository. Each SQL statement or procedure returns a number (or set of numbers) that can trigger an alert depending on user-defined thresholds. Custom alerts can be used to alert against a wide variety of conditions.

This example shows how to configure two alerts that work together to cause DPA to display the physical machine name of the active node in a SQL Server failover cluster. By default, DPA shows only the cluster name.

  • The first alert gets the name of the active node.
  • The second alert appends this name to the name of the cluster that DPA displays. If the active node changes, it updates the name and also notifies recipients that a failover has occurred.

Task 1: Create an alert to get the physical machine name

This alert runs against a monitored database instance and retrieves the name of the physical machine that the instance is currently running on.

  1. On the DPA menu, click Alerts.
  2. Click the Manage Alerts tab.
  3. Choose Custom as the category and Custom SQL Alert - Multiple Numeric Return as the type.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/070/040/gs-custom-alert-category-type.png

  4. Click Create Alert.
  5. Enter a unique name and select the execution interval.

    Set the execution interval based on the average frequency of failovers in your clustered environment. The execution frequency affects the accuracy of the machine names that DPA displays. In this example, the alert interval is 10 minutes. But if failovers occur infrequently, you might want to choose a longer interval.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/070/040/gs-custom-alert-name.png

  6. In the Notification Text box, provide a description of the alert.

    This alert does not send notifications, but the description provides information for other users.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/070/040/gs-custom-alert-info.png

  7. Under Database Instances, select the SQL Server cluster.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/070/040/gs-custom-alert-instance.png

  8. Enter the following SQL in the SQL Statement box:

    select coalesce(SERVERPROPERTY('ComputerNamePhysicalNetBIOS'), SERVERPROPERTY('MachineName')) HOST, 0

  9. Verify that Monitored Database is selected from the Execute Against drop-down menu.
  10. Enter Machine Name 0 in the Units box.

  11. Under Configure Alert Levels and Recipients:

    1. Enter 1 as the Min value for the High level.

      The query returns a numeric value of 0. Entering 1 as the threshold ensures that the status is always Normal. Because this alert only retrieves the machine name, it should not be triggered.

    2. Don't select a contact because no one needs to receive notifications.

  12. Click Save.

Task 2: Create an alert to append the machine name to the cluster name

This alert runs against the DPA Repository database. It appends ' Node: @nodeName' to the database instance name that DPA displays. Each time it runs, it determines whether the node name has changed. If so, it updates the display name and sends a notification so that you can investigate why the failover occurred.

This alert can also determine why the name has changed with different error levels. The error levels are: 0=no change, 1=node change, 2=initial update.

  1. From the Manage Alerts tab, choose Custom as the category and Custom SQL Alert - Multiple Numeric Return as the type.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/070/040/gs-custom-alert-category-type.png

  2. Click Create Alert.
  3. Enter a unique name and select the execution interval.

    This execution interval must be larger than the interval for the Get Physical Machine Name alert. The execution intervals for both alerts affect the accuracy of the name that DPA displays. In this example, the execution intervals are 10 minutes and 12 minutes, but you should determine what intervals are appropriate for your environment.

  4. Enter the email notification text.

    File:Success_Center/New_Articles/DPA_Getting_Started_Guide/070/040/gs-custom-alert-info2.png

  5. Under Database Instances, select the SQL Server cluster.

    You must select the SQL Server cluster for both alerts. If you don't select it for this alert, the instance name is not updated in DPA. If you don't select it for the Get Physical Machine Name alert, that alert does not run and has a status of Broken.

  6. Enter the following SQL in the SQL Statement box. To copy and paste this SQL, move your mouse pointer over the SQL block and click the View Source icon  in the upper-right corner. 

    declare 
    @mach_name varchar(100),
    @current_name varchar(100),
    @update_flag smallint
    
    begin 
    select @mach_name=c.name 
    from con_alert_db a, con_alert b, con_alert_db_results c
    where b.id = a.alertid
    and b.alertname = 'Get Physical Machine Name'
    and a.alertid = c.ALERTID
    and a.DBID = c.DBID
    and a.DBID = #DBID#
    
    if @mach_name is not null
      select @update_flag = CHARINDEX('  Node',NAME)  
      from COND 
      where ID = #DBID#
    
    if @update_flag != 0
      begin
      select @current_name = substring(NAME, CHARINDEX('  Node',NAME) +7, 100)
      from cond
      where ID = #DBID#    
    
      if ltrim(rtrim(@current_name)) != ltrim(rtrim(@mach_name))
        begin
          update COND 
          set NAME = substring(NAME, 1,CHARINDEX('  Node',NAME)-1 )
          where ID = #DBID#
           
          update COND
          set NAME = NAME + '  Node: ' + @mach_name
          where ID = #DBID#
           
          select @mach_name,1
        end
      end
    else
      begin
        update COND
        set NAME = NAME + '  Node: ' + @mach_name
        where ID = #DBID#
         
        select @mach_name,2
      end;
    select @mach_name, 0
    end;
  7. Select Repository from the Execute Against drop-down menu.
  8. Enter :Node|Error Level in the Units box.

  9. Under Configure Alert Levels and Recipients:

    1. Enter 1 as the Min value for the High level.

      When the name of the active node changes, this alert is triggered at the High level.

    2. Select a contact to receive the email when this alert is triggered.

      If you have not added the person or group as a contact in DPA, click Add Contact or Add Contact Group. See Create contacts and contact groups.

  10. Click Save.

Last modified
17:13, 29 Sep 2016

Tags

Classifications

Public