sqlserver IM Configuration

Contents
uimpga-ga
sqlserver_IM
 
Contents
 
 
 
2
 
 
This article describes the configuration concepts and procedures to set up the SQL Server Monitoring (sqlserver) probe. To configure the probe, you must create a connection to the SQL Server database and a profile. The default checkpoints are automatically added to the profile to monitor the server performance or unwanted events. You can generate alarms and QoS using these checkpoints.
 
This article is for probe versions 4.9 and later.
 
Configure the Probe
The following procedures list the minimum steps to configure the probe.
 
Follow these steps:
 
 
 
3
 
3
 
 
Verify Prerequisites
Review the following prerequisites before you configure the probe:
  • Verify that required hardware and software is available.
  • Verify that the required drivers are available. You can install the drivers from: http://www.microsoft.com/en-us/download/details.aspx?id=20065. The SQL Server driver name has been changed from SQLOLEDB to MSDASQL in version 4.30. 
  • Before you deploy the probe version 5.20 on your Infrastructure Manager (IM), you must enable the FIPS encryption on the system where the probe is configured.
(Optional) Configure General Properties
When the probe is deployed, it is installed with a predefined set of configuration settings. You can choose to modify these settings according to your organization needs. For example, configure the log, alarm, and QoS status details for the probe.
 
Follow these steps:
 
  1. Navigate to the 
    Setup
     tab and update the following information:
    •  
      Suppress All Alarm: 
      subdue all the alarms, such as checkpoint alarms, profile alarms, SQL timeout alarms, and startup clear alarms. Select this check box to disable the 
      Generate status only 
      field.
      Default: Not selected
    •  
      Generate status only: 
      generate status and not an alarm when the threshold is breached. Select the 
      Status 
      tab to see the status for the different checkpoints.
      Default: Selected
    •  
      Clear Alarm on Restart: 
      clears the alarm when the probe is restarted.
      Default: Selected
    •  
      Alarm severity filter: 
      select a severity level filter on events to be considered as alarms. The probe checks many areas of the databases. Some events that are generated are key to the performance and availability of the database. For example, as a database administrator, you want to convey important events on to the operations center or help-desk, so that the event can trigger emails.
      The 
      Alarm severity filter
       considers the events matching the selected severity level and higher as alarms and pass these on whenever the 
      Generate status only
       option is cleared. If you set alarm severity to be 
      major
      , then only messages with severity major and upward are considered as alarms.
      Default: information 
    •  
      Status Auto-Update: 
      if selected, you can specify the automatic refresh time of the monitoring profile. The 
      Status Auto-Update 
      parameter specifies the automatic refresh interval of monitoring profiles that are displayed in the 
      Status
       tab. To automatically update the status every x second, set this parameter value more than 0 and select a profile on the 
      Status
       tab.
      Default: Not selected
      Default value when selected: 60 sec 
       The 
      Status Auto-Update
       value is saved in the configuration file but the check box is cleared when you restart the probe.
    •  
      Log Size: 
      specify a maximum size of the probe log file. When this size is reached, the existing contents are cleared.
      Default: 100 KB
    •  
      Log level: 
      specify the level of information that is written in the log file. You can select the following log levels:
      • 0 - Logs only severe information (default)
      • 1 - Logs error information
      • 2 - Logs warning information
      • 3 - Logs general information
      • 4 - Logs debugging information
      • 5 - Logs tracing/low-level debugging information
      : Log as little as possible during normal operation to minimize disk consumption, and increase the amount of detail when debugging.
    •  
      QoS V2 Compatibility: 
      insert QoS data of sqlserver version 2.x in the database.
      : This field is valid when you are upgrading the probe from version 2.x to a higher version. For more information, refer to the V2 QoS Compatibility Mode in sqlserver (SQL Server Monitoring) Release Notes.
  2. Click 
    OK
     to save the configuration.
Create a Connection
You must connect to the SQL Server database that you want to monitor.
 
Follow these steps:
 
  1. Right-click in the 
    Connections
     tab and select the 
    New
     option.
  2. Enter the name of the connection in the 
    Add new connection
     dialog and click 
    OK
    . The following fields require mention:
    •  
      Authentication: 
      select from the following authentication method to connect to the database:
      •  
        SQL Server authentication:
         provide the username and password of the SQL Server database. (default)
      •  
        Windows authentication: 
        provide the username and password of the domain.
      •  
        Robot Service authentication
        : connects to the local SQL Server instance using the robot's login credentials, generally NT Authority\ System. Since the login is automatic, the username and password fields are disabled and the server name is set to 
        localhost
    •  
      Encryption: 
      encrypt the communication between database and the server.
      Default: Not selected
    •  
      Detect domain automatically
      : if you have selected 
      Windows authentication, 
      you can choose to automatically detect the domain by selecting this option.
    •  
      (From Version 5.0) Monitor Always On:
       monitor the Always On Availability Groups in the SQL Server database.
      Default: Not selected
       The Always On Availability Groups are supported from SQL Server 2012 or later.
    •  
      Enable Cross Domain: 
      if you have selected 
      Windows authentication
      , you can choose to enable access to the database which is in another domain.
       
    •  
      User ID: 
      enter the authenticated user name to access the SQL server database.
       Ensure that the specified 
      User ID
       has the required privileges on the Microsoft SQL Server Database. For more information, see the 
      Access Prerequisites
       section in the sqlserver (SQL Server Monitoring) Release Notes.
    •  
      Password:
       enter the password of the specified 
      User ID
      .
    •  
      Server name: 
      enter the server name of the database. If the server is not running on the standard port, then you can specify the port along with server name or IP address. See the following screenshot, as an example:
      nonstandardport_sqlserver.png  
    •  
      Retry attempts: 
      enter the number of attempts that the probe makes to connect when there is a failure. The value 0 means that only the initial connection is attempted.
      Default: 0
    •  
      Retry delay: 
      enter the time that the probe waits between two connection attempts.
      Default: 1 sec
    •  
      Timeout: 
      enter how long the probe waits for answer before it aborts the connection process.
      Default: 5 sec
       Specify a higher 
      Timeout
       value, for example 10 seconds, if your robot has multiple networks hops to the monitored SQL Server.
    •  
      TLS Enabled
      : enables the TLS 1.2 version for the probe.
  3. Click 
    Test
    .
     
    If connection is successful, it returns the instance name and its version number. If not, an error message is returned. The connection to the database server is verified when the icon turns green.
  4. Click 
    OK
    .
Create a Profile
You can create a profile to monitor the required SQL database using the existing connections in the probe. 
 We recommend that you limit the number of profiles to less than 30 for an optimized GUI performance.
 
Follow these steps:
 
  1. Right-click on the 
    Profiles
     tab and select the
     New
     option.
  2. Enter a name for the profile in 
    Add New Profile 
    dialog and click 
    OK
    . The following fields require mention:
    •  
      Active: 
      start monitoring, on creation.
      Default: Selected
    •  
      Heartbeat
      : enter the interval, after which schedules of all profile checkpoints are tested and executed.
    • Default: 5 sec
      : This number is a common denominator to all used check interval values. The higher the value, the lower is the profile overhead.
    •  
      Connection: 
      select the connection that is used by this profile. The connections must exist in the probe before creating a profile.
    •  
      Check Interval
      : enter the interval after which the profiles are scanned.
      Default: 5 min
      : Reduce this interval to generate alarms frequently. A shorter interval can also increase the system load.
    •  
      Clear message:
       enter the message to be used when the threshold is no longer breached.
    •  
      SQL Timeout: 
      enter the SQL query timeout. Every checkpoint query runs asynchronously. If the query reaches the SQL timeout, the checkpoint processing is terminated and a 
      Message 
      is generated. The probe processes the next checkpoint and generates an alarm.
      Default: 30 sec 
    •  
      Profile Timeout: 
      enter the maximum processing time for all checkpoints in the profile. If this timeout is reached, the interval processing is terminated and a 
      Message 
      is generated. The probe waits for next heartbeat to evaluate any checkpoint schedules, and generates an alarm.
    • Default: 15 min
    •  
      Delay Threshold: 
      enter the timeout threshold for the profile delay alarm.
      Default: 15 sec
    •  
      Delay Severity: 
      specifies the severity of the alarm that is raised if the profile is delayed by the threshold.
    •  
      Timeout severity: 
      specifies the severity for timeout messages.
    •  
      Group:
       allows you to select the defined groups in the 
      Groups
       tab.
      Default: blank
    •  
      Alarm Source: 
      overrides the source name of the alarm on the Unified Service Management (USM). If you do not specify a value, robot IP address is used.
    •  
      QoS Source
      : overrides the source name of the QoS on USM.
      (From Version 5.0)
       Type $ in the field and select the 
      instance
       variable to use the 
      $instance
       variable to display as the alarm or QoS source.
    •  
      Use FQDN As QoS Source: 
      enables the probe to use FQDN as the QoS source instead of a simple host name.
       CA does not recommend you to change the source fields after the initial configuration. If you change the QoS source later, multiple graphs are displayed on the USM Metrics view (one for every QoS source value). Also, we recommend you to keep the source identical for both alarm and QoS.
    •  
      Suspended/Resumed (indicator): 
      indicates the status of the profile. This indicator is green when the profile is active. The indicator changes to yellow when the profile is suspended and to black when deactivated.
  3. Select the checkpoints that you want to monitor from the 
    Profile Checkpoints 
    section.
  4. Click 
    OK 
    to configure the profile.
    :
    • To suspend an existing profile, right-click on a profile and select 
      Suspend
      .
    • To resume a suspended profile, right-click on the profile and select 
      Resume.
       
    • To add checkpoints to an existing profile, open the profile by double-clicking it and select the the required checkpoints from the
       Profile Checkpoints 
      section.
Configure Alarm Thresholds
Every checkpoint requires at least one threshold which is used to compare with the monitored value. If this threshold is breached, then the alarm is generated. You can define the threshold values for a checkpoint that is used in a specific profile.
Alarms for a checkpoint can be configured in the following two ways:
  •  
    Key alarms:
     These are the key specific alarms for any data column that are generated if the alarm condition matches the specified key value.
  •  
    Default alarms: 
    These are the default alarms for any data column that are generated based on the following two conditions:
    • When no key specific alarm is configured for the specified data column.
    • When there is a key specific alarm that is configured for that data column but it does not match the alarm condition.
The threshold identification consists of an object name, such as tablespace name, userid, and a threshold ID, which is numbered from 0. Alarms on threshold value come with respect to first alarm being of top priority.
 
Follow these steps:
 
  1. In the 
    Thresholds 
    section of the
     Edit template checkpoint 
    dialog, right-click and select the 
    New
     option.
  2. Complete the following information:
    •  
      Threshold object name: 
      defines the monitoring object name. Some special checkpoints have a second threshold that is called ‘count" (for example, "locked_users").
    •  
      Condition: 
      specifies the comparison operator that is used for threshold evaluation (for example, +, !=, >=, and <=).
    •  
      Threshold value: 
      defines the value that is used for threshold evaluation.
    •  
      Current interval value: 
      specifies the last measured value, if invoked from the status report.
    •  
      Severity: 
      specifies the alarm severity.
    •  
      Message: 
      defines the name of message that is used for threshold alarm.
    •  
      Message text: 
      provides the text of the message, containing variables, which will be replaced in run time. If the message text is changed from a profile list, you are forced to create message.
    •  
      Variables: 
      provides the list of variables available in the checkpoint.
    •  
      Clear Message: 
      specifies the clear message to be sent.
    •  
      Scheduling
      : if you do want to use the checkpoint schedule and want to refine the time to run the threshold value for monitoring, you can use the Scheduling option. For example, create a schedule, such as Wednesday mornings at 8:00 A.M. To define a schedule, right click on the Schedules section, and select 
      New. 
       Define the parameters of the schedule. An example is shown in the following screenshot:
      Scheduling.jpg  
      Consider the following points for the schedule:
      • First execution of schedule happens when you specify the value in
         Date from 
        and
         To 
        fields
        .
         
      • To run the threshold once a day in the defined period, select
         Run once 
        option.
      • To run the threshold multiple times, select 
        Run interval 
        option.
      • This alarm schedule should be a subset of one or more schedules used in the checkpoint. For example, if checkpoint schedule is Monday 8:00 AM to Friday 9 PM. then threshold schedule should be same or within the this time frame, as follows:
        • Monday 8:00 AM to Friday 9 PM
        • Monday 8:10 AM to Friday 6 PM
        • Tuesday 9:00 AM to Thursday 2 PM
      Select one of the following options from the 
      Scheduling 
      drop down:
      •  
        rules: 
        if you select this drop down option, the profile is monitored according to the time defined in the schedule. 
      •  
        exceptions: 
        if you select this drop down option, the profile will not be monitored during the time that is defined in the schedule. For example, if you have defined a schedule as every Saturday and Sunday, then the profile will not be monitored during these two days.
    •  
      Key Column Name: 
      specifies the column name for which you can generate a key specific alarm.
    •  
      Key Column Value: 
      defines the key value on which an alarm condition would be compared for the specified column name. If the condition matches, the alarm is generated.
  3. Click 
    OK
     to save the modifications.
(Optional) Configure Checkpoints
When defining a profile, you can use two different strategies to handle checkpoints in a profile. 
  •  
    Static: 
    allows you to manage the checkpoint properties locally. Set the checkpoint to static in your profile before modifying it. When modified, the new settings become valid for this profile only.
  •  
    Template: 
    allows you to define the checkpoint properties globally (under the 
    Templates 
    tab) that represent the default settings. Every time that you change the template value, it reflects on all profiles that uses the dynamic templates. Double-click the checkpoint in the profile list or 
    Templates
     tab. When modified, the new settings become valid for all profiles, unless overruled by static settings in the profile.
    To edit the properties for a checkpoint template, double-click the checkpoint in the profile list or 
    Templates
     tab. When modified, the new settings will be valid for all profiles, unless overruled by static settings in the profile.
There can be both "template" and "static" checkpoints in one profile. If a checkpoint is static, the checkpoint name appears in the list with a 
blue
 color, and it is marked as static in the column 
Type
.
 Define a higher time value of the 
Check Interval
 and 
Profile Timeout
 fields depending on your database size, if your profile has a large number of activated checkpoints.
:
  • Checkpoints "logic_fragment" and "scan_density" are not supported on SQL Server 2005. Use "av_fragmentation" instead.
  • The checkpoint "database_status" has been replaced by "database_state". Database status values from SQL Server 2000 (and lower) are mapped to database_state as defined in SQL Server 2005.
 
(From Version 5.0)
 The probe includes checkpoints to monitor different parameters of the Always On Availability Group in the SQL Server database. For example, you can monitor the AlwaysOn WSFC nodes state with the aag_cluster_members_state checkpoint. All these checkpoints remain disabled in a profile until the 
Monitor Always On
 feature is enabled for the connection that is specified for the profile. The probe also includes new checkpoints to monitor statistics of the wait checkpoints at every interval that is enabled in your profile. These checkpoints ensure that the database performance is optimal. For more information about the checkpoints, see sqlserver Metrics.
 The 
Monitor Always On
 feature is not available for the connections or profiles existing on probe versions 4.9 or earlier.
 
Follow these steps:
 
  1. Double-click a checkpoint in an existing profile.
    The 
    Edit static checkpoint [
    <Checkpoint Name>
    ] in profile 
    <Profile Name>
     
     or the 
    Edit template checkpoint [
    <Checkpoint Name>
    ]
     window appears, based on the type of checkpoint to be modified. You can also open the 
    Template
     tab to view the list of checkpoints.
    Only template checkpoints can be edited from the 
    Template
     tab. Static checkpoints can only be edited for respective profiles.
  2. Set or modify the following values as required:
    •  
      Description: 
      specifies additional information about the checkpoint.
    •  
      Active: 
      activates the checkpoint.
    •  
      Condition: 
      specifies the information describing how the threshold values are evaluated.
    •  
      Check Interval: 
      specifies the time interval after which the SQL server is scanned.
      : Reduce this interval to generate alarms frequently. A shorter interval can also increase the system load.
    •  
      Send Quality of Service: 
      enables you to activate QoS values being sent into the QoS database. If not available for a checkpoint, check box is disabled.
    •  
      QoS List: 
      allows you to define QoS to be used in the checkpoint. Click  greater_than.png  to open the 
      QoS list
       dialog that displays the current QoS definition list. By default, the current checkpoint definition is listed. For more information on defining QoS, see Define QoS Values
    •  
      Samples
      : specifies the number of samples to calculate an average value, which is compared with the specified alarm threshold. The probe generates an alarm when the average value of any sample breaches the threshold. The probe behavior for the following 
      Samples
       value is described as follows:
      • 0: Sampling is done based on the number of samples defined in the template. If no number is specified in the template, no sampling is done.
      • 1: No sampling is done (default)
      • 3: The probe performs the following:
        • uses the first sample value in the first interval 
        • uses the average of samples 1 and 2 in the second interval
        • uses the average of samples 1, 2, and 3 in the third interval
    •  
      Use excludes: 
      enables you to define list of exclude patterns that you do not want to monitor for a checkpoint.
      : You can select this check box for a custom checkpoint from version 4.9 onward only. In previous versions, this check box is disabled after you create a custom checkpoint. 
    •  
      Excludes list: 
      enables you to define a list of patterns that you do not want to monitor for a checkpoint. Click  greater_than.png  to open the 
      Exclude list
       dialog and specify the patterns to be excluded for the checkpoint. You can define objects that you do not want to monitor using the checkpoint. When the 
      Use excludes
       check box is marked, you can define exclude patterns in the 
      Exclude list
       dialog.
    •  
      Scheduling
      : if you do NOT want to use the 
      Check interval
       time and want to refine the time interval of running the profile, you can use the scheduling option. For example, you want the profile to monitor the SQL server only on Wednesday mornings at 8:00 A.M. To define a schedule, right click on the 
      Schedules
       section and select 
      New. 
       Define the parameters of the schedule. An example is shown in the following screenshot:
      Scheduling.jpg
      Consider the following points for the schedule:
      • First execution of schedule happens when you specify the value in
         Date from 
        and
         To 
        fields
        .
         
      • To run the threshold once a day in the defined period, select
         Run once 
        option.
      • To run the threshold multiple times, select 
        Run interval 
        option.
      • This alarm schedule should be a subset of the schedule(s) used in the checkpoint. For example, if checkpoint schedule is Monday 8:00 AM to Friday 9 PM. then threshold schedule should be same or within the this time frame, as follows:
        • Monday 8:00 AM to Friday 9 PM
        • Monday 8:10 AM to Friday 6 PM
        • Tuesday 9:00 AM to Thursday 2 PM
      Select one of the following options from the 
      Scheduling 
      drop down:
      •  
        rules: 
        if you select this drop down option, the profile will be monitored according to the time defined in the schedule. 
      •  
        exceptions: 
        if you select this drop down option, the profile will not be monitored during the time defined in the schedule. For example, if you have defined a schedule as every Saturday and Sunday, then the profile will not be monitored during these two days.
    •  
      Use Includes
      : enables you to define a list of patterns that you want to monitor for a checkpoint. 
    •  
      Includes list
      : allows you to specify the patterns to be included in the checkpoint. Click  greater_than.png  to open the 
      Include list
       dialog and specify the patterns to be included for the checkpoint. You can define objects that you want to monitor using the checkpoint. When the 
      Use Includes
       check box is marked, you can define include patterns in the  
      Include list
       dialog. 
       CA recommends you to define objects that you want to exclude or include from monitoring, in large databases. If you do not limit the monitored objects, the probe might not be able to execute all the checkpoints within the profile timeout value. For example: tablespace_free, free_space checkpoints.
    •  
      Clear severity
      : selects the severity used for message issued when the threshold is not breached.
    •  
      Send Alarm
      : specifies to send alarm information. This option is enabled by default. If this option is cleared, then only QOS information is shared.
    •  
      Clear message
      : specifies the message for clear alarm message.
    •  
      Sql Timeout
      : specifies the SQL query timeout. If the query reaches the SQL timeout, the checkpoint processing is terminated. The probe processes the next checkpoint and generates an alarm.
  1. Click 
    OK
     to save the modifications.
(Optional) Define QoS Values
You can define QoS values for a checkpoint as required. The 
Edit QoS
 dialog offers available metrics (numerical variables that are reported as QoS) and available object variables (to be added to the QoS source).
 
Follow these steps:
 
  1. Click  greater_than.png  to open the 
    QoS list
     dialog.
  2. Right-click and select 
    New
    .
  3. Set or modify the following values as required:
    •  
      Name
      : specifies the name of the QoS.
    •  
      Description
      : specifies additional information about the QoS.
    •  
      Unit
      : specifies the unit for QoS.
    •  
      Abbreviation
      : specifies the abbreviation for QoS.
    •  
      Metric
      : selects an appropriate unit for the Metric.
    •  
      Max Value
      : allows you to specify the maximum permissible value for the QoS.
    •  
      Object
      : specifies the object variable that you want to monitor.
  4. Click 
    OK
    .
: If a custom QoS is added to an existing monitoring profile, the Unified Management Portal (UMP) creates a Custom node in the Metric section. Further, it does not display the user-defined description and unit.
(Optional) Create Custom Checkpoint
You can define a checkpoint and customize its properties as required.
 
Follow these steps:
 
  1. Click the 
    Templates 
    tab, right-click in the checkpoint list and select 
    Create new
    .
    SQL query is the data source, and therefore, it is integral part for defining a checkpoint. CA recommends testing the query first with SQL Server Management Studio (or other tool) before using it in a checkpoint.
  2. Enter the name of the checkpoint in the 
    Add new user template checkpoint
     dialog.
  3. Navigate to the 
    Query 
    tab in the 
    Edit template checkpoint
     dialog.
  4.  
    (Version 5.40) 
    Test the Pre-defined Connection:
    • In
       Connection, 
      select from the available connections to test the connection with SQL server.
    • Click
       
      the
       Edit/New 
      button, verify the connection details, and then click the 
      Test
       button to verify that the connection is successful.
      Connection field displaying available connections  
  5.  
    (Version 5.30 or earlier): 
    Define the Connection details and test.
    • Click the 
      Edit/New
       button and define the connection details. For more information, see Create a Connection.
    • Click the 
      Test
       button to verify that the connection is successful.
        Edit/New button to test connection  
      • The connection information (for example, User ID, Password, and Server name) defined in this form must be same as defined in the SQL Server profile.
      • If there are multiple profiles using different connections, then the custom checkpoints must be converted to static and in each static checkpoint, the connection should match with the connection used in the SQL Server profile.
  6. Enter the required query statement in the 
    Query 
    text box and click 
    Test
     to validate the query.
    The 
    Query Result
     dialog appears, displaying the query result. A single query is executed for all the databases of the SQL server. If any one of the databases is unable to execute the query, the probe generates a failure message. The query has to return at least one numeric column that will be used as checked value (all numeric formats are supported). If the query returns more than one row, the probe needs unique identification per row. This will be used as part of suppression key and QoS definition. The row key can be created by concatenating several columns in the checkpoint definition. Additional columns can be retrieved to be used in generated messages.
    • Use rtrim and ltrim functions to remove leading and trailing blanks from string variables. Use explicit column names for manipulated values. Avoid generated names, such as Col0 and Col1. 
    • From probe version 4.30, multiline custom query must include "SET NOCOUNT ON" as the initial line.
  7. Click 
    Close
    .
  8. Set or modify the following values in the 
    Query
     tab of 
    Edit template checkpoint 
    dialog:
    •  
      Row identification
      : specifies the unique identification code of the row for which reporting is done.
    •  
      Message variables
      : specifies the variables to be used in the message. Click 
      Edit 
      to choose one or more message variables. A new window opens with a list of all available columns and their possible usage. You have to define their use and format.
      Numeric columns can be used as:
      •  
        Value
        : specifies the value for verifying the checkpoint.
      •  
        Value size
        : specifies the file size (in B, KB, MB, GB or TB).
      •  
        Value int
        : specifies the integer number.
      •  
        Information
        : specifies the standard formatting (if available 2 digits after comma).
      Character columns can be used as:
      •  
        Row key
        : specifies the row identification for string formatting.
      •  
        Information
        : specifies the string formatting.
    •  
      Query file
      : specifies the file that stores the query. Each query is stored in separate files. Click the 
      Read 
      button to read the SQL query stored in the file. The query file name should contain full path, otherwise the file will be stored in the probe work directory.
    •  
      Interval modus
      : enables you to subtract the variable value from the value generated at the end of the interval. On selecting the 
      Interval modus 
      check box, the variable $column_interval_value.i is added to the 
      Message variables
       text box. This list can be viewed in the 
      Edit Threshold
       dialog box. The 
      interval_value
       variable can be configured from the probe 
      Raw Configuration
       interface. If you do not select the 
      Interval modus
       check box, the value of variable as returned from the query will be used for checking and QoS.
    •  
      Report Per Second Metric
      : enables you to select the column metric type as count/sec. Also, it will only allow “count” to be used as the threshold object. Your query should have a column name as “count” whose metric type will be Count/sec.
  9. Navigate to the 
    General
     tab of 
    Edit template checkpoint
     dialog and provide the details as mentioned in Step 2 of Configure Checkpoints.
  10. Click 
    QoS List
    . The 
    QoS list
     dialog appears.
  11. Right-click in the grid view and select 
    New
     from the context menu.
  12. Enter a description of the QoS metric that you want to define.
  13. Set or modify the following values as required in the 
    Edit
     dialog:
    •  
      Name
      : specifies the name of the QoS metric.
    •  
      Description
      : specifies the additional information about the QoS metric.
    •  
      Unit
      : indicates the unit of QoS metric.
    •  
      Metric
      : selects the required QoS metric.
    •  
      Abbreviation
      : specifies the abbreviated name of the QoS metric.
    •  
      Max value
      : specifies the maximum value of the QoS metric.
  14. Click 
    OK
     in the Edit dialog and in the 
    QoS list
     dialog.
  15. Create a hint text in the 
    Hint Editor
     tab, if required.
  16. Save the definition and restart the probe to use the checkpoint.
  • If a custom checkpoint is added to an existing monitoring profile, UMP creates a Dynamic node in the Metric section. Further, it does not display the user-defined description and unit.
  • Stored procedures can be used in custom checkpoints.
(Optional) View Profile Status
The 
Status
 tab lists the profiles that are defined and their corresponding checkpoint templates. You can also modify the properties of an individual checkpoint object from the 
Status
 tab. 
 
Follow these steps:
 
  1. Select a profile in the right pane and a monitored checkpoint in the left pane.
  2. Double-click an object in the right pane. If the object belongs to a template object, you are warned that a modification will make the checkpoint static for the selected profile. Refer Configure Checkpoints.
(Optional) Create a Group
You can create multiple groups, which can be associated with profiles. This allows you to group the checkpoints under a specific category that can be used in a profile.
 
Follow these steps:
 
  1. Right click in the 
    Group
     tab and select the 
    New
     option.
    The 
    Add New Group
     
     
    dialog appears.
  2. Enter a name for the group and click 
    OK
    .
    The 
    New Group 
    dialog appears.
  3. Enter a description for the group in the 
    Description 
    field.
  4. From the 
    Group checkpoints
     
     
    section, select the check points that you want to enable for the group.
  5. Click 
    OK
    .
    The new group appears in the 
    Groups
     list. You can also select the 
    Group
     drop-down in the 
    Edit Profiles
     dialog.
(Optional) Create Alarm Message
You can select messages from this list when editing the properties for a checkpoint. Right-clicking in the list allows you to add, edit, copy, or delete messages.
 
Follow these steps:
 
  1. Right-click in the message list and select the 
    New 
    option.
    The 
    New message
     dialog is displayed.
  2. Specify a message name in the 
    Name
     field and click 
    OK
    .
    The 
    Edit message
     screen appears.
     Use the checkpoint name in the 
    Name 
    field to help find the alarm message when it is selected in the Properties dialog for the checkpoint. 
  3. Select the checkpoint for which you want to create the alarm message in the 
    Checkpoint
     drop-down list.
    All the available variables for that checkpoint are listed in the 
    Variables
     section.·  
 Define a higher time value of the 
Check Interval
 and 
Profile Timeout
 fields depending on your database size, if your profile has a large number of activated checkpoints.