4 User-Defined Metrics

Create Your OS Monitoring Script

Using a scripting language of your choice, create a script that contains logic to check for the condition being monitored. For example, scripts that check for disk space or memory usage. All scripts to be run with user-defined metrics should be placed in a directory to which the Management Agent has full access privileges. Scripts themselves must have the requisite permissions set so that they can be executed by the Management Agent. The script runtime environment must also be configured: If your script requires an interpreter, such as a Perl interpreter, this must be installed on that host as well.

All monitoring scripts should contain code to perform the following basic functions:

• Code to check the status of monitored objects

• Code to return script results to Enterprise Manager

print "em_result=200\n"

"The value is $em_result".

print "em_message=Disk usage is high\n"

print "em_result=200\n" 

print "em_result=200\nem_message=Disk usage is high\n" 

print "em_message=Disk usage is at $em_result.\n"

em_message=The value is $em_result 

print "em_result=200\n"

Register the Script as a User-Defined Metric

Once you have created the monitoring script, you are ready to add this monitoring functionality to Enterprise Manager as a user-defined metric.

Creating an OS-Based User-Defined Metric

1. From the home page of the Host that has your OS monitoring script (Related Links), choose User-Defined Metrics. The User-Defined Metrics summary page appears containing a list of previously defined User-Defined Metrics. From this page, you perform edit, view, delete, or create like functions on existing User-Defined Metrics.

2. Click Create. The Create User-Defined Metric page appears.

3. Enter the requisite metric definition, threshold, and scheduling information. For the Command Line field, enter the full path to your script, including any requisite shell or interpreters. For example, /bin/sh myscript. See the following section for more details.

4. Click OK. The User-Defined Metric summary page appears with the new User-Defined Metric appended to the list.

If the user-defined metric has been created and the severity has not been updated recently, it is possible that there are metric errors associated with the user-defined metric execution. In this situation, access the Errors subtab under Alerts tab to check.

Create User-Defined Metric Page (OS-based User-Defined Metric)

The Create User-Defined Metric page allows you to specify the metric information required to successfully register the metric with Enterprise Manager. The page is divided into functional categories:

• Definition: You define the operational and environmental parameters required to run your script, in addition to the name of the script itself.

• Operating System Credentials: You enter the credentials used to run the monitoring script. See Enterprise Manager online help for more details on Response Actions. This functional area appears when creating OS-based user-defined metrics.

• Thresholds: To have the value returned by your script compared with set threshold values, enter the requisite threshold information. The value of the monitored metric returned by your script (as specified by em_result) will be compared against the thresholds you specify. If the comparison holds true, a warning or critical alert may be generated.

• Schedule: Specify the start time and frequency at which the user-defined script should be run. The time zone used is that of the Agent running on the monitored host.

The following figures show the Create User-Defined Metric pages for an OS-based user-defined metric. When accessing this page from any Host home page, the Create User-Defined Metric page appears as shown in Figure 4-1.

Figure 4-1 Create User-Defined Metric Page (OS-Based)

Description of "Figure 4-1 Create User-Defined Metric Page (OS-Based)"

Key elements of this page are described in the following tables.

/u1/bin/perl /u1/scripts/myScript.pl

var1=%perlBin% var2=%scriptsDir% var3=/u1/orahome10

The User-Defined Metric Schedule interface lets you specify when the Management Agent should start monitoring and the frequency at which it should monitor the condition using your OS script.

OS-Based User-Defined Metric Example

The sample Perl script used in this example monitors the 5-minute load average on the system. The script performs this function by using the 'uptime' command to obtain the average number of jobs in the run queue over the last 5 minutes.

The script is written in Perl and assumes you have Perl interpreter located in /usr/local/bin on the monitored target.

This script, called udmload.pl, is installed in a common administrative script directory defined by the user. For example, /u1/scripts.

Full text of the script:

#!/usr/local/bin/perl


# Description: 5-min load average.

# Sample User Defined Event monitoring script.


$ENV{PATH} = "/bin:/usr/bin:/usr/sbin"; 


$DATA = `uptime`; 

$DATA =~ /average:\s+([\.\d]+),\s+([\.\d]+),\s+([\.\d]+)\s*$/; 

  


if (defined $2) { 

   print "em_result=$2\n"; 

} else { 

   die "Error collecting data\n"; 

} 

1. Copy the script (udmload.pl) to the monitored target. For example: /u1/scripts. Make sure you have an Enterprise Manager 10g Management Agent running on this machine.

2. Edit the script, if necessary, to point to the location of the Perl interpreter on the monitored target. By default, the script assumes the Perl interpreter is in /usr/local/bin.

3. As a test, run the script: udmload.pl You may need to set its file permissions so that it runs successfully. You should see output of this form:

   em_result=2.1 
   

4. In Create User-Defined Metric page, create a new user-defined metric as follows:

   1. Definition Settings

      • Metric Name: Test User-Defined Metric

      • Metric Type: Number

      • Command Line: %perlBin%/perl /u1/scripts/udmload.pl

      • Environment: leave blank

      • Operating System User Name: <OS user able to execute the script>

      • Password: ******

   2. Threshold Settings

      • Comparison Operator: >=

      • Critical Threshold: 0.005

      • Warning Threshold: 0.001

      • Consecutive Occurrences Preceding Notification: 1

      In this example, we want the metric to trigger an alert at a Warning level if the 5-minute load average on the machine reaches 0.001, and trigger an alert at a Critical level if the 5-minute load average reaches 0.005. Feel free to change these thresholds depending on your system.

   3. Schedule Settings:

      • Start: Immediately after creation

      • Frequency: Repeat every 5 minutes. You must specify at least a 5 minute interval.

Setting Up the Sample Script as a User-Defined Metric

When the 5-minute load reaches at least 0.001, you should see the metric trigger an alert.

SQL-Based User-Defined Metric Examples

For a database version 9i and higher, you can run the example queries as dbsnmp, which is the default monitoring user account for the Management Agent. On a 8.1.7 database (which does not have SELECT ANY DICTIONARY system privilege), you must grant dbsnmp the following privileges in order for the queries to run successfully:

For example #1:

grant select on sys.dba_tablespaces to dbsnmp;

grant select on sys.dba_data_files to dbsnmp;

grant select on sys.dba_free_space to dbsnmp;

For example #2:

grant select on sys.dba_extents to dbsnmp;

The above grant statements can be run as SYSDBA after logging in via "connect internal". The queries can also be run by any user who has been granted the DBA role.

SELECT d.tablespace_name,

     round(((a.bytes - NVL(f.bytes,0))*100/a.maxbytes),2) used_pct

FROM   sys.dba_tablespaces d,

      (select tablespace_name, sum(bytes) bytes, sum(greatest(maxbytes,bytes)) maxbytes

         from sys.dba_data_files group by tablespace_name) a,

      (select tablespace_name, sum(bytes) bytes

         from sys.dba_free_space group by tablespace_name) f

WHERE d.tablespace_name = a.tablespace_name(+)

 AND d.tablespace_name = f.tablespace_name(+)

 AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') 

SELECT decode(nvl(partition_name, ' '),

             ' ',   owner || '.' || segment_name || ' ' || segment_type,

             owner || '.' || segment_name || '.' || partition_name || ' ' || segment_type) as segment,

      count(extent_id) as extent_count

FROM dba_extents

WHERE (segment_type like 'TABLE%' OR segment_type like 'INDEX%') AND

     (owner != 'SYSTEM' AND owner != 'SYS')

GROUP BY owner, segment_name, partition_name, segment_type

ORDER BY EXTENT_COUNT DESC 

select Tablespace, case when (MxAvail <= 15) and (MxFreeMB < 20000) then 'CRITICAL, '||MxUsed||'%' when (MxAvail <= 20) and (MxFreeMB < 20000) then 'WARNING, '||MxUsed||'%' else 'OK' end Error_Level, MxAvail,MxUsed,MxFreeMB,MxExdMB from (select nvl(b.tablespace_name, nvl(a.tablespace_name,'UNKNOWN')) as Tablespace, data_files as NumDBFs, mbytes_alloc as AllocMB, Round( mbytes_alloc-nvl(mbytes_free,0),0) as UsedMB, Round(nvl(mbytes_free,0),0) "AllocFreeMB", Round((( mbytes_alloc-nvl(mbytes_free,0))/ mbytes_alloc)*100,0) as AllocUsed, MaxSize_Mbytes as MxExdMB, Round(MaxSize_Mbytes - (mbytes_alloc-nvl(mbytes_free,0)),0) as MxFreeMB, Round((mbytes_alloc/MaxSize_Mbytes*100),0) as MxUsed, Round((MaxSize_Mbytes - (mbytes_alloc-nvl(mbytes_free,0)))/MaxSize_Mbytes *100,0) as MxAvail from ( select sum(bytes)/1024/1024 mbytes_free, max(bytes)/1024/1024 largest,tablespace_name from sys.dba_free_space group by tablespace_name ) a, ( select sum(bytes)/1024/1024 mbytes_alloc, sum(decode(maxbytes,0,bytes,maxbytes))/1024/1024 MaxSize_Mbytes, tablespace_name,count(file_id) data_files from sys.dba_data_files group by tablespace_name )b where a.tablespace_name (+) = b.tablespace_name order by 1)

CREATE OR REPLACE TYPE tablespace_obj as OBJECT

(

    tablespace_name VARCHAR2(256),

    free_mb NUMBER);

/


CREATE OR REPLACE TYPE tablespace_array AS TABLE OF tablespace_obj;

/

CREATE OR REPLACE FUNCTION calc_tablespace_free_mb

RETURN tablespace_array

IS

    tablespace_data TABLESPACE_ARRAY := TABLESPACE_ARRAY();

BEGIN

    SELECT tablespace_obj(tablespace, mxfreemb)

        BULK COLLECT INTO tablespace_data

    FROM

(

select Tablespace, case when (MxAvail <= 15) and (MxFreeMB < 20000) then

 'CRITICAL, '||MxUsed||'%' when (MxAvail <= 20) and (MxFreeMB < 20000) then

 'WARNING, '||MxUsed||'%' else 'OK' end Error_Level,

 MxAvail,MxUsed,MxFreeMB,MxExdMB from (select nvl(b.tablespace_name,

 nvl(a.tablespace_name,'UNKNOWN')) as Tablespace, data_files as NumDBFs, mbytes

_alloc as AllocMB, Round( mbytes_alloc-nvl(mbytes_free,0),0) as UsedMB,

 Round(nvl(mbytes_free,0),0) "AllocFreeMB", Round((( mbytes_alloc-nvl(mbytes

_free,0))/ mbytes_alloc)*100,0) as AllocUsed, MaxSize_Mbytes as MxExdMB,

 Round(MaxSize_Mbytes - (mbytes_alloc-nvl(mbytes_free,0)),0) as MxFreeMB,

 Round((mbytes_alloc/MaxSize_Mbytes*100),0) as MxUsed, Round((MaxSize_Mbytes -

 (mbytes_alloc-nvl(mbytes_free,0)))/MaxSize_Mbytes *100,0) as MxAvail from (

 select sum(bytes)/1024/1024 mbytes_free, max(bytes)/1024/1024 largest,tablespace

_name from sys.dba_free_space group by tablespace_name ) a, ( select

 sum(bytes)/1024/1024 mbytes_alloc,

 sum(decode(maxbytes,0,bytes,maxbytes))/1024/1024 MaxSize_Mbytes, tablespace

_name,count(file_id) data_files from sys.dba_data_files group by tablespace_name

 )b where a.tablespace_name (+) = b.tablespace_name order by 1)

) CUSTOMER_QUERY;


  RETURN tablespace_data;

END calc_tablespace_free_mb;

/

Getting Notifications for User-Defined Metrics

As with regular metrics, you can receive e-mail notifications when user-defined metric critical or warning alert severities are reached. Assuming you have already defined your e-mail addresses and notification schedule, the remaining task is to set up a notification rule for the user-defined metric.

To set up notification rules:

1. Click Preferences.

2. From the vertical navigation bar, click Rules if you are a Super Administrator or My Rules if you are a regular Enterprise Manager administrator.

3. Click Create to define a new notification rule. The Create Notification Rule pages appear.

4. From the General page, enter the required rule definition information and choose Target Type Host for OS-based user-defined metrics or choose Database Instance or Cluster Database for SQL-based user-defined metrics.

5. On the Metrics page, click Add. A list of available metrics appears. To view all metrics simultaneously, choose Show All from the drop-down menu.

6. Select User-Defined Numeric Metric or User-Defined String Metric based on the type of value returned by your user-defined metric.

7. In the Objects column, choose whether you want to receive notification for all user-defined metrics (All Objects) or specific user-defined metrics (Select).

   
   

   When choosing the Select option, enter the name of the user-defined metric, or specify multiple user-defined metrics separated by commas. You can use the wildcard character (%) to match patterns for specific user-defined metrics.

   You can search for available user-defined metrics using the search function (flashlight icon). However, search results will only show user-defined metrics that have at least one collected data point. For metrics that have not yet collected at least one data point, as may be the case for a newly created user-defined metric, you must specify them in the Select text entry field.

   The format used to specify individual user-defined metrics in the Select text entry field depends on the type of value(s) returned. The following three examples illustrate how to specify user-defined metrics for three possible return value situations.

   Formats shown in the following table summarize how to specify multiple user-defined metrics returning single and double values. Specific examples follow.

   Return Values	Select Text Entry Field Format
   Single	MY_UDM1,MY_UDM3,MY_UDM5
   Double (2-column), All Key Values	MY_UDM1;%,MY_UDM2;%,MY_UDM4;% User-defined metrics returning two columns are specified as <UDM Name>;% where "%" is a wildcard signifying ALL key values associated with that user-defined metric. Note: 2-column values apply to SQL user-defined metrics only.
   Double (2-column), Specific Key Values	MY_UDM1;K1,MY_UDM1_K2 Note: 2-column values apply to SQL user-defined metrics only.

   
   

   Example 1: Host User-Defined Metrics Returning a Single Value

   In this example, you will receive notifications for two host user-defined metrics: MY_UDM9 and MY_UDM11. Both metrics return numeric values. To receive notifications for all host user-defined metrics, select the All Objects (Script) option where Script refers to the user-defined metric name.

   
   

   Example 2: SQL User-Defined Metric Returning a Single Value

   In this example, you will receive notifications for four SQL user-defined metrics: MY_UDM1 and MY_UDM3 (numeric value returned) and MY_UDM5 and MY_UDM7 (string value returned). To receive notifications for all SQL user-defined metrics, select the All Objects (Script) option where Script refers to the user-defined metric name.

   
   

   Example 3: SQL User-Defined Metrics Returning Two Values

   To receive notifications for SQL user-defined metrics returning two values, the syntax is slightly different. In this situation, you specify the user-defined metric using the following format: <Metric ID;Key> where Metric ID is the name of the user-defined metric and Key is the specific key value you want returned. To receive notifications for all SQL user-defined metrics, select the All Objects (Metric ID;Key) option.

   
   

   For SQL user-defined metrics returning two values, you have the option of receiving notifications for all key values or just specific values for a given metric. Use a wildcard character (%) to specify all key values. Example: MY_UDM1;%, MY_UDM2;%.

   To receive notifications for specific key values, specify the exact key value to be returned. Example: MY_UDM5;K1,MY_UDM5;K2 (only key values K1 and K2 are returned from MY_UDM5).

8. Select the severity or corrective action state for which you would like to receive the notification and then click Continue.

9. If you want to receive e-mail for the specified user-defined metric, go to the Notification Rule and check the "Send me E-mail" option.

10. Click OK to create the new notification rule. If you made the notification rule public, other administrators can subscribe to the same rule.

Setting Corrective Actions for User-Defined Metrics

Corrective actions allow you to specify automated responses to alerts ensuring that routine responses to alerts are automatically executed. Corrective actions can be defined for both SQL and OS-based user-defined metrics.

To set up corrective actions:

1. From a target home page, click Metric and Policy Settings from Related Links.

2. Locate and edit the user-defined metric.

3. From the Edit Advanced Settings page, click Add under Corrective Actions for the Critical or Warning alert severity and define the corrective action. Corrective actions can be defined for one or both alert severities.

Deploying User-Defined Metrics Across Many Targets Using Monitoring Templates

Monitoring Templates simplify the task of standardizing monitoring settings across your enterprise by allowing you to specify the monitoring settings once and applying them to your monitored targets. You can thus use Monitoring Templates as a way to propagate user-defined metrics across a large number of targets.

Assuming you have created the user-defined metric on the host or database target, you can use Monitoring Templates to propagate the user-defined metric to other hosts or database targets.

To create a Monitoring Template for the user-defined metric:

1. Click Setup.

2. From the vertical navigation bar, click Monitoring Templates

3. Click Create. The Copy Target Settings page appears.

4. Specify the host or database on which you defined the user-defined metric and click Continue.

5. Fill in the requisite information on the General page.

6. On the Metric Thresholds page, you can choose to keep or remove the other metrics that have been copied over from the target.

7. You can also edit the user-defined metric's thresholds, collection schedule, and corrective actions.

8. On the Policies page, you can choose to keep or remove any policy rules that have been copied over from the target.

9. Click OK to save the template settings to the Management Repository.

Once the template containing the user-defined metric has been created, you can propagate the user-defined metric by applying the template to other hosts or databases.

To apply the monitoring template:

1. On the Monitoring Templates page, select the monitoring template and click Apply.

2. On the Apply Monitoring Template page, add the targets on which the user-defined metric should be created.

3. If a two-column SQL-based user-defined metric is part of the template, the Metric with Multiple Thresholds option is applied according one of the following situations:

   • Situation One: The target to which the template will be applied does not contain the two-column SQL user-defined metric defined in the template. In this situation, regardless of which Metric with Multiple Thresholds option is chosen, the user-defined metric is copied to the target when you apply the template.

   • Situation Two: The target to which the template will be applied does contain the two-column SQL user-defined metric. The name (case insensitive) and return value (numeric, scalar, or two column) of both the target and template user-defined metrics must match. In this situation, you must select one of the Metric with Multiple Thresholds options:

     • Apply threshold settings for monitored objects common to both template and target: For only those keys which the target has in common with the template, the target threshold values will be set to the values defined in the template. This option is chosen by default and is recommended for most situations.

     • Duplicate threshold settings on target: For keys which are common between target and template, the thresholds will be set to the values defined in the template. Any extra keys (and their thresholds) that exist in the template but not on the target will be copied to the target in anticipation that these keys will be created in the target at some point in the future. Any extra keys (and their thresholds) that exist on the target but not in the template will be deleted from the target.

   Important: If there are other metrics in the monitoring template, refer first to the Enterprise Manager online help for implications of what this option means for these other metrics.

4. Click Continue.

5. On the subsequent page, specify the credentials that should be used when running the user-defined metric on the destination targets.

6. Click Finish.

7. When you return back to the Monitoring Templates page, check that "Pending Apply Operations" count for your template is zero. This indicates the number of template apply operations that could be pending. Once they are all complete, the count should be zero.

Deploying User-Defined Metrics Using Scripts

An alternate method of deploying user-defined metrics to large numbers of targets is to use the Enterprise Manager Command Line Interface (EMCLI). Using the EMCLI "apply_template" verb, you can deploy user-defined metrics via custom scripts. For more information about the "apply_template" verb, see the Oracle Enterprise Manager Command Line Interface manual.

Deleting User-Defined Metrics Across Many Targets Using Monitoring Templates

Just as templates can be used to deploy user-defined metrics across targets, templates can also be used to delete these metrics across targets should these metrics no longer be in use.

To create a Monitoring Template for the user-defined metric:

1. Click Setup.

2. From the vertical navigation bar, click Monitoring Templates

3. Click Create. The Copy Target Settings page appears.

4. Specify the host or database on which there is a user-defined metric that needs to be deleted and click Continue.

5. Fill in the requisite information on the General page.

6. On the Metric Thresholds page, remove all metrics from the template except the user-defined metric to be deleted.

7. Click the pencil icon to access the Edit Advanced Settings page.

8. Check the Mark for Delete option and click Continue. The Mark for Deletion icon now appears next to the user-defined metric on the Metric Thresholds page.

9. On the Policies page, remove all policies.

10. Click OK to save the template settings to the Management Repository.

To apply the monitoring template:

1. On the Monitoring Templates page, select the monitoring template and click Apply.

2. On the Apply Monitoring Template page, add the targets on which the user-defined metric should be deleted.

3. Click Continue.

4. On the subsequent page, specify the credentials that should be used when running the user-defined metric on the destination targets.

5. Click Finish.

6. On the Monitoring Templates page, check that "Pending Apply Operations" count for your template is zero. This indicates the number of template apply operations that could be pending. Once they are all complete, the count should be zero.

Enterprise manager will delete all user-defined metrics found on the selected target that match the following criteria:

• Name of the user-defined metrics (case insensitive)

• Return value of the user-defined metric (numeric or scalar)

• For SQL-based user-defined metrics, the output of the query (single value or two columns). The match does not take into consideration the actual script used by the user-defined metric. For this reason, even though the script on the target user-defined metric may be different from that of the template user-defined metric, the target user-defined metric will still be deleted.

• Host User-Define Metrics using scripts: You must delete the script from the host on which you want the UDM deleted.

• SQL-based user-defined metrics using function calls: You must delete the function from the database on which you want the user-defined metric deleted.