Alerting and advanced dashboarding in Oracle Enterprise Manager App for Grafana 4.0

May 18, 2023 | 9 minute read
Hozefa Palitanawala
Director of Engineering
Anarav Patel
Sr. Engineer
Text Size 100%:

Oracle Enterprise Manager (EM) is an integrated platform for managing Oracle databases, middleware, and applications. Whether self-hosted or hosted on the cloud, EM provides a single console to administrators to monitor, diagnose, and manage systems. 

A key component of EM is its Oracle Management Repository (OMR), which stores all the configuration data, performance data, and other metadata collected from the system it is managing. EM can provide a centralized view of the entire Oracle ecosystem and is developing new ways to visualize all the information.

In this blog post, the focus is on new capabilities and features in Oracle Enterprise Manager App for Grafana version 4.0.0.

Key updates

  1. Support Grafana alerting on the data queried from the EM repository or a target
  2. New UI for defining query template variables
  3. Define custom variables using data from the EM repository
  4. Dynamically fetch data from different tables based on user-selected time range
  5. Automatically change the time range from other variables
  6. Conditional expressions in queries for dynamic data display
  7. Two new out-of-the-box capacity planning dashboards for Exadata and Exadata Cloud

Support Grafana alerting on the data queried from the EM repository or a target

The EM App for Grafana now supports the built-in Grafana alerting capabilities. These alert rules can be configured using values from a metric or a custom SQL query, similar to the queries from a dashboard panel. Refer to the official Grafana documentation for the most up-to-date information on setting up alerts. 

Here's an example alert rule configured against the "Current Logons Count" metric, which is set to alert when the average of the collected metric value goes over 100:

An image of an alert rule configuration
Figure 1:  Example of an alert rule creation

New UI for defining query template variables

In the previous versions of the app, three query template variables were supported for the EM data source: target type, target name, and named credential. These query template variables allow the creation of dynamic dashboards that would adapt to the selections of a dashboard view. For example, the Exadata dashboards use the target name variable to present the users with a list of all the available Exadata machines in the given EM site. Once a viewer of this dashboard selects an exact Exadata machine from the presented drop-down, the capacity planning details of the selected target are fetched and rendered.

Previously, these variables had to be defined as a JSON string with specific key and value pairs to fetch the correct data. An example variable definition to fetch all the named credentials would look like the following:

 

An image of the named credential query variable definition in 3.0.0
Figure 2:  Named Credential Template Variable Definition in 3.0.0

 

Here, oem_gf_target_type and oem_gf_target_name are two separately defined template variables. The schema for this raw JSON string was strict and although there were out-of-the-box templates provided to use these variables, defining these variables through JSON strings left room for a lot of errors. Starting with v4, the JSON string has now been replaced with a user-friendly UI. This will allow dashboard designers to move away from knowing the specific schema required, and instead, present them with drop-down options to make the process of defining query template variables fast and easy. Below is an example of what the same named credential query template variable would look like in the new UI.

 

An image of the named credential query variable definition in 4.0.0
Figure 3:  Named Credential Template Variable Definition in 4.0.0

 

Although the examples shown above are for the named credential query template variable, this usability improvement pertains to the other two supported template variables as well, namely, target type and target name.

New query template variables for designing dashboards

Currently, only three query template variables are supported: target type, target name, and named credential. In v4, three additional template variables have been added: Repository Query, Time Picker, and Time Difference.

Define custom variables using data from the EM repository (Repository Query)

The currently supported template variables cover a lot of use cases, but they are certainly not sufficient to address every use case out there. Instead of introducing specific template variables, a new variable, "Repository Query" has been introduced to allow dashboard designers to specify a custom SQL query that will get executed against their EM repository database for populating a drop-down. This provides dashboard designers with the flexibility to create arbitrary template variables according to their specific visualization needs. For example, a dashboard designer might want to create a dashboard that displays data filtered by a particular management agent. The designer could then define a template variable with a query like below:

 

An image of the new Repository Query template variable definition
Figure 4:  Repository Query Template Variable Definition

 

The variable defined above can then be used in any of the dashboard panels to only display data from targets managed by this chosen agent from the drop-down. An example of what such a time series would look like is as follows (where $agent represents the variable defined above):  

SELECT target_name as metric, average as value, rollup_timestamp as time_sec FROM mgmt$metric_hourly WHERE metric_column = 'cpuusage_ps' AND target_guid in (SELECT target_guid FROM mgmt$agents_monitoring_targets WHERE agent_name in ( $agent ))

Note that the template variable repository query selects two columns: display_name and target_name.

In general, the first column will be used for the label (displayed in the user-selectable drop-down), while the second column will be the actual value (the substituted value when the variable is used in a panel). This allows designers to display user-friendly display names for user selection while also using internal identifiers to fetch the correct data from other tables.

NOTE: If the label and value are expected to be the same, only one column can be specified in the SELECT clause. This will cause both label and value to use the result from the same column. If more than two columns are specified in the SELECT clause, all other columns besides the first two are ignored.

Dynamically fetch data from different tables based on user-selected time range (Time Difference)

There are cases when designing a dashboard that the designer wants to fetch the data to display from different tables dynamically, depending on the user-selected time range. For example, if a dashboard user wants to visualize data over a week vs. over a year, the dashboard designer might want to fetch the data from two distinct tables, such as using mgmt$metric_hourly when visualizing data over a week vs. using mgmt$metric_daily when visualizing data over a year. An example of this template variable definition is shown below:

An image of the new Time Difference query template variable definition
Figure 5:  Time Difference Template Variable Definition

 

Here, the From and To control how to calculate the time difference, while the Unit identifies the unit of the time difference. In the example above, the built-in ${__from} and ${__to} Grafana global variables are utilized. In general, the From and To values can either be these Grafana global variables or they must be other template variables (i.e., ad-hoc values cannot be specified directly).

Automatically change the time range from other variables (Time Picker)

A dashboard user has granular control over the time range they can select using the built-in Grafana time picker. However, there are cases for certain dashboards where such a granular control might not make sense. Instead, the dashboard designer might want to only present a static list of time ranges that are relevant to their dashboard. For example, consider designing a dashboard for viewing data for a limited-time event such as a summer promotional event, where data was only collected every week. Allowing arbitrary time ranges might not serve as the best experience for dashboard viewers if they are not aware of the limited-time event and its weekly granularity. This new template variable would solve this use case as it would allow the dashboard designer to hide the time picker and instead present the dashboard user with a static drop-down, such as Week 1, Week 2, and Week 3, ... which maps to value 2022-05-01;2022-05-8, 2022-05-09;2022-05-17, 2022-05-18;2022-05-25, ... respectively. This will serve to provide the user with a frustration-free experience as they will not be confused when no data is displayed if they select a time range that is outside of this promotional event.

There are two options for defining this variable, a FromTo time range or an Interval with a Unit of time, and both values are expected to come from other template variables (i.e., only other template variables are available to be selected in the drop-down, ad-hoc values cannot be directly specified)

  1. FromTo - value is expected to resolve into the form of "<from>;<to>" OR "<from>", where <from> and <to> are widely recognized datetime strings (ISO 8601, RFC 2822, Unix timestamps) or relative Grafana strings (now-6d, now-6h). When the value is of the form "<from>", the value for "<to>" is implicitly assumed to be "now" (i.e. "now-6d; now" is equivalent to "now-6d" or "2022-01-01; now" is equivalent to "2022-01-01")
  2. Interval + Unit - value for Interval is expected to resolve into a numerical value, representing the relative time interval from "now" in the specified Unit of time.

 

Conditional expressions in queries for dynamic data display

Consider a use case where a dashboard designer wants to display distinct data depending on the target type. These target types might not share the same metrics and so two different queries would be required to display the appropriate metric data depending on the target type selected. Currently, to handle this use case, either two different dashboards would have to be written, one for each target type, or a panel would have to be written with two queries with an appropriate WHERE clause such that only the applicable query gets executed. Neither of these solutions is ideal as the first doesn't allow the dashboard designer to present a single pane view for the user-selected target type while the second incurs the unnecessary cost of querying against EM (when it is known that no data is expected). Starting with v4, each query can optionally include conditional expressions which is simply a comparison between two values (statically defined or dynamically evaluated from a template variable). The query will only be executed if this defined expression evaluates to true.
The supported operators for comparison are:

  • = (equals)
  • != (not equals)
  • > (greater than)
  • >= (greater than or equal to)
  • < (less than)
  • <= (less than or equal to)

In addition to these comparisons, multiple such comparisons can be chained together using logical operators to form a more complex expression. Currently supported logical operators are:

  • AND (evaluates to true only when BOTH left expression and right expression evaluates to true)
  • OR (evaluates to true when EITHER or BOTH left expression and right expression evaluate to true)

The expressions are evaluated left-to-right in case more than two comparison expressions are specified. This allows dashboard designers to provide a single pane view for various user-selectable options by giving them granular control over which queries get executed. Furthermore, this feature has the added benefit of saving resources by not unnecessarily querying against the database. Here's an example of a conditional expression:

An image of a query with several conditional expressions defined
Figure 6:  Conditional Expression Definition

 

New dashboards for Exadata and Exadata Cloud Capacity Planning

Two new Exadata Capacity Planning dashboards in version 4.0 contain information like the existing EM Exadata Capacity Planning Business Intelligence Publisher (BIP) reports. These dashboards give a detailed view of Storage Capacity Planning, CPU and Memory Capacity Planning, I/O Capacity Planning, and Network Capacity Planning.

 

Resources

Oracle Exadata Capacity Planning dashboards in Oracle Enterprise Manager App for Grafana 4.0

Download this new version from Oracle Enterprise Manager App for Grafana.

Refer to the Oracle Enterprise Manager App for Grafana User’s Guide for up-to-date installation instructions and learn about the capabilities of this app.

Hozefa Palitanawala

Director of Engineering

Anarav Patel

Sr. Engineer


Previous Post

Configure Management Gateways for High Availability with an HAProxy Load Balancer

Next Post


Enhanced proxy features in Enterprise Manager 13.5 improve the Cloud Bridge experience