HowTo: Create a universal UDM report page in OEM

HowTo: Create a universal UDM report page in OEM

PROBLEM:

• You have a bunch of User Defined Metrics (UDM) setup in OEM, and you want to know the staus of each of them.
• OEM does not provide a single page to show the status of UDMs - users must navigate to each instance and look at the UDM page.
  

SOLUTION:

• Create a standard OEM Report to display all of your UDMs in one place!

STEPS:

1. First, if you have not already discovered this the hard way, you cannot create a Report against a non-EMREP/non-Repository tables. Why, i'll never know. But, whatever. The thing is, if you try to create a report using something easy like showing how many records are in an Target Database table, it will return the error "ORA-00942: table or view does not exist"
2. In this case, we are actually hitting an OEM Repository table, but it's not one of the objects we are allowed to query via the "Table From SQL" report element type.
   
3. Fortunately, "bwolter" found a work-around for this sorry situation and published how to it in this Oracle OEM Forum post.
4. Follow his 3 easy steps, then continue with my Step 5 below.
5. Create a new Report and choose whatever you want for Category, etc.
6. Set the "Targets" value to the "Use the specified target" radio-button and select your Repository SID
7. Set the "Privileges" value to "Run report using target privileges of the report owner (SYSTEM)"
8. Click the "Elements" sub-tab, then Add a new element using the new "target_user_table_from_sql" type.
9. Click the "Set Parameters" icon and enter your SQL. Note that your value of KEY_VALUE will be different than mine!

10. SELECT DISTINCT UPPER(target_name) "Target Name",
           key_value || '__' "Metric Name",
           TO_CHAR(collection_timestamp, 'MM-DD-YYYY HH24:MI') "Last Collected_____",
           string_value "Metric Value"
      FROM sysman.em$current_metrics
     WHERE metric_name = 'SQLUDM'
       AND UPPER(key_value) LIKE 'ENSURE%'
       AND string_value IS NOT NULL
     ORDER BY 1, 2

11. My KEY_VALUE (the "Name" you used for the UDM) is "Ensure PMON is running as MST", so obviously you will set your value to whatever the Name of your UDM is.
12. Rinse-and-repeat Steps 8-10 for each UDM type or gorup you want to show on the Report.

NOTES:

• I use underscore in my column alias names to stretch the columns out for easy reading.
• During this project I found a UDM which had encountered errors and stopped running! OEM showed no indication outside of a small icon on the Instance's UDM page (nor did it send a notification). This report will save you from that potential embarrasement!!
  

RESULTS:

• Here is what my final UDM Report looks like:

HowTo: Setup User Defined Metrics in OEM Grid Control

User Defined Metrics are a fantastic feature of OEM Grid Control. Do you use them? Well, you should! They are very easy to setup and are just as reliable as any other OEM alerts.

As an example, let's say you want to be notified whenever a certain schema has any invalid objects in it. This is how to setup such an alert:

1. Navigate to the database instance "Home" page
2. Click the "User Defined Metrics" link at the bottom of the page
   
3. Click the "Create" button to create a new UDM
   
4. Complete all of the form fields
   
   
   
1. SQL query you would like to run (don't use a semi-colon at the end!)
2. Credentials
3. Thresholds
   
4. Schedule
5. Note that you can customize the subject line of the notification emails
6. Click the "Test" button to make sure the results are what you want
7. Click "Ok" to save this UDM
   
5. Now, if you merely stop here, no notifications will ever be sent! Why? Because there is no Notification Rule defined, so let' s do that next...
   
6. Click "Preferences" in the top right corner of any Grid Control page
   
7. Click "Rules" in the left nav bar
8. Create a new Notification Rule
   
   
9. Voila! Now email notifications will be sent whenever your threshold is exceeded and the this metric automatically integrates with all of the Alert display boxes throughout OEM.

Cool huh?