EM12c: Using Metric Extensions to Generate Composite Alerts

There was a question on OTN forums about how to generate alerts based on two different metrics. The user wants to get alert only if the warning threshold is over 80% “AND” there is less then 20Gb of free space of a tablespace. So he doesn’t want to get alert if the tablespace is over %80 full but still has 100GB free space. Of course, he can set different thresholds for each tablespace: He can set percentage threshold for small tablespaces and set free space threshold for bigger ones. I do not know how many databases he monitors but if he’s managing lots of DBs, this could be a time consuming task.

So how can we solve it? EM12c doesn’t let you generate an alert based on two different metrics. For these situations, you can create metric extensions. All you need is to query mgmt$alert_current and see if two alerts occurred for same target. Examine the following SQL:

This query checks if both “pctUsed” (Tablespace Space Used) and “bytesFree” (Tablespace Free Space) alerts are generated for same tablespace. The query doesn’t check the level of alert (warning or critical), but if you need so, you can use the alert_state column of mgmt$alert_current view.

Now let’s see how we can create it as a metric extension. First I have set the thresholds on one of my DBs (DEVDB) to produce alert for both percentage and disk space. So I will be able to test my metric extension.

Then I opened “metric extension” page and clicked “repository-side metric extension” under “create” menu. I will create a “repository-side metric extension”, so it will let me query management views of Enterprise Manager.

Because the tablespace metrics are related with databases, I pick “database instance” as the target type. When creating your own user metric, you can pick the target type according to your metrics.

I enter the above query, and click “next”. The query should always return target_guid and an additional column which Enterprise Manager will use as metric value.

At the columns page, we see one column returned from the query (target_guid is not visible here). I clicked on it to edit, and modified the display name, comparison operator and values for warning and critical. I entered a dummy value, because if my query returns any value (the name of the tablespace), I want this alert triggered! I also modified the messages to show which tablespace has low disk space.

We can check our metric before we actually deploy it. That’s a great feature! As you can see I added several instances, and run tests on them. Only one of them produced the alert.

I reviewed my metric extension and clicked “finish” to create it.

After it’s ready, all we need is to “save it as deployable draft”, and then deploy the targets.

This is an email generated by our metric extension. I hope it helps.