How to create a User Defined metric(UDM) in Grid

Using Grid it is possible to create user defined metrics that capture information about the state of a database, a host or an application. Once you have a script or sql statement that returns a value then a UDM can be created. Within EM a report can be created to report the metrics or an alert can be raised.

Firstly I will create a UDM that reports when an account is locked and automatically raise an alert. It may be a simple example but I show one or two issues that can occur.

EM/Select the database/User defined Metrics at the bottom of the main page. Create a new UDM and I suggest starting the name off with a business area such as Retail or EBS as you can end up with quite a few metrics once you get the hang of them.

The key choice to make is whether the output will be a number or a string and whether or not you want one or two values output. In my case I want to know the status of an account so that will be string and I want to output two columns, the username and the status. Enter your query and an account to use for the connection. Ensure that the query works and the account has the privilege to run that query. In this case when  I tested the UDM it fails as I had not  created the accounts beforehand. Select a comparison operator (equal, less than MATCH etc) to the sql query you are running and then format the output you want in the alert.

Note the %Key% and the %value% keywords have to be in exactly that case.

Set the test time to be 5 minutes in the schedule team for the purposes of testing it out and then run the test command (or save if fully confident). Remember to reschedule later. Note 5 minutes is the lowest frequency you can use.

If you have followed my example you will have had a failure which is not immediately obvious why. The first options in the drop down select box cover those applicable to the number value and the MATCH and CONTAINS are applicable for when choosing a string.

The test results show the result of the query and the UDM can then be saved.

Now it is time to schedule the job but first lock an account to ensure the UDM has something to pick up on.

One trick to see when the metric is next scheduled to run on the agent is to set the environment to the agent home and execute the command below and search the output of what the scheduler has been doing. Normally the metric page will  show the last time it ran but this is the first run so the field is empty in the screenshot below.

emctl status agent scheduler|grep -i account
2010-12-07 14:58:58 : oracle_database:MOMSIT2A:sga_start+incident_meter+BUS_AREA: Key account locked

If you want to know details of what collections are enabled then look in the $AGENT_HOME/sysman/emd/collections directory and search for a file called oracle_database_SID.xml or rac_database_SID.xml if RAC system. PS Note that if you are creating a UDM on a RAC cluster do it on the cluster not the instances.

A few minutes later we have an metric raised that one of our accounts is locked.

To set a notification up go to the home page and the preferences / notification rules. If you have a dashboard enabled then the alert will be raised in that automatically. Create a new rule and stick to the same naming convention of business area that we chose earlier. Decide if you want e-mail notification (Preferences / General page to set up if you have not done so already). Then it is a matter of selecting the instance or group of instances that you want the notification to act on. The Metric name in the Notifcation rules is always ‘User Defined Numeric/String metric’ which sometimes makes it hard to distinguish if you have multiple metrics.

If you are geting several notifications for a metric then check that script is selected rather than all object.

A couple of helpful pieces of SQL are

In an example where you have two columns and a number output but want  to show multiple columns then you can concatenate them

select
'Wave:'||WAVE_NBR||' Time: '||to_char(WAVE_END_TS,'DD-MON-YY:HH24:MI:SS'),
LINES_PER_MINUTE
from wmsprd.distribution_metrics
where wave_end_ts = (
select max(WAVE_END_TS)
from wmsprd.distribution_metrics
where wave_nbr <> 999
and selected_lines > 999
)

To query the OEM repository for UDM details use following code

 select target_name "Database", key_value2 "Vdate", value "Result", collection_timestamp "Collected"
from SYSMAN.MGMT$METRIC_CURRENT
where metric_label='User-Defined SQL Metrics' and column_label = 'Retail MOM Vdate Check'
and collection_timestamp =
(select max(collection_timestamp) from SYSMAN.MGMT$METRIC_CURRENT where metric_label='User-Defined SQL Metrics' and column_label = 'Bus_area: Vdate Check')

If you have a situation where you are looking for a string that can be one of  ABORTED, ENABLED, DISABLED or null then you cannot match that using the comparison operators available. Therefore code it with a case statement and use the ‘critical Thresholds by Key’ column in EM to map ABORTED to ERROR , DISABLED to ERROR, ENABLED to NOERROR and MATCH ON ERROR

 

I hope that has been helpful. In my next blog I will demonstrate how to graph application server memory utilisation over time and also raise an alert if it exceeds a threshold.