Creating a Widget Using Custom SQL

Navigate to the widget builder.

Optionally, click the Open/close filter panel icon (

) in the upper-left corner to view the filters added to the dashboard and make changes, if required.

From the Data Source drop-down list, select one of the following data sources:

EM Repository: Select this option to query data from Enterprise Manager Management Repository views and tables. For information on Management Repository views, see About Management Repository Views in Oracle Enterprise Manager Management Repository Views Reference.
EM Federation: Select this option to query data from federated Enterprise Manager sites. For information on Enterprise Manager Federation, see Configuring Enterprise Manager Federation in Oracle Enterprise Manager Advanced Installation and Configuration Guide.

Note:
The EM Federation data source option is only available when creating a widget using custom SQL. You cannot use this data source when creating a widget using predefined SQLs.

From the SQL Selector drop-down list, select Custom SQL.

The Custom SQL option is selected by default and a sample query is added to the SQL Query field and the corresponding widget is displayed. In the sample query, the SELECT statement queries column names from the selected data source, EM Repository or EM Federation. The visualization options are also selected by default to visualize the data retrieved by the sample query.

In the SQL Query field, replace the sample query with a new query or customize the SQL query to meet specific requirements by adding custom input parameters for the widget.

Optionally, to add a custom input parameter, you can replace the part of the query that you want to customize with a standard bind parameter marker denoted by a question mark (?). This is similar to JDBC bind parameter support. Custom input parameters are of two types, a regular input parameter to specify input such as target types and a time input parameter, which is essentially a composite parameter, to specify the duration of time (start and end time) for which data will be displayed in the widget.

Note:

This is an advanced concept and some familiarity with the widget builder and bind parameters is required. If you are creating a widget using custom SQL for the first time, it is recommended that you go to step 6.

To add a regular input parameter and specify a custom value:
1. Replace the part of the query to which you want to add the regular input parameter with the standard bind parameter marker (?). Here is an example in which the target types in the custom SQL query are replaced with a bind parameter marker, so you can add custom input values for the target types you want to view in the widget.
  Replace the target types in bold:
```
SELECT
	target_type as "Target Type",
	type_display_name as "Type Display Name",            
	count(target_type) as "Target Type Count"
FROM
	mgmt$target
WHERE
	target_type in (            
		'oracle_database', 'weblogic_j2eeserver',
		'host','oracle_apache','rac_database','oracle_exadata','exalytics_machine'            
	)
GROUP BY
	target_type,
	type_display_name
```
  with the bind parameter marker
```
SELECT
	target_type as "Target Type",
	type_display_name as "Type Display Name",            
	count(target_type) as "Target Type Count"
FROM
	mgmt$target
WHERE
	target_type = ?
GROUP BY
	target_type,
	type_display_name
```
2. In the Settings tab in the lower-right corner, click Add input +.
3. In the Configure input for Untitled Widget (Required) dialog:
  1. Is time input: Ensure that this check box is not selected. Note that this check box should only be selected to specify time input parameters. For information, see To add a time input parameter and specify custom values.
  2. Input Name: Enter a name for the input. For example, TargetType.
  3. Input Label: Enter a display name for the input. The input name is displayed in this field and you can make changes to it, if required.
  4. Parameter Indexes: Enter the index value to map the input parameter's value to the bind parameter marker in the SQL query.
  5. Default settings: Review and make changes to the default settings, if required.
    
    Note:
    If you update the Default settings to configure the input parameter, the same settings will be applied whenever this widget is added to a different dashboard. To configure an input parameter whose value can be edited after the widget is created and added to the widget library, use the options in the Configure widget input section of this dialog.
    - Required: Deselect to indicate that this is not a required widget input. By default, this check box is selected and if you do not configure the input parameter, data will not be displayed in the widget.
    - Allow multiple values: Select to allow multiple values to be configured for the input parameter. Here is an example:
```
WHERE
    TARGET_TYPE IN (select /*+ Cardinality(theArray 10)*/ COLUMN_VALUE from (TABLE (CAST(? AS MGMT_MEDIUM_STRING_TABLE)) theArray) )
```
    - Allow widget users to link the input with a filter: Deselect this check box to only specify a fixed input parameter value. This check box is selected by default and if you deselect it, the fields available to link the input to a filter are not displayed in the dialog.
    - Allow widget users to specify the input: Deselect this check box to only link the input to a filter. This check box is selected by default and if you deselect it, the fields available to specify a fixed input parameter value are not displayed in the dialog.
  6. Configure widget input: Specify the value for the regular input parameter:
    - Link the <name> input with an existing filter: Select to link the input parameter to an existing filter in the widget.
    - Specify the <name> input: Select to specify a fixed input value.
    - Not configured: Select this option to leave the input unconfigured.
  7. Click Save changes.
To add a time input parameter and specify custom values:
1. Add bind parameter markers for a time input parameter in the custom SQL query. Here are a couple of examples:
  - WHERE clause
```
WHERE mtx.collection_timestamp > CAST(TO_UTC_TIMESTAMP_TZ(?) at time zone tgt.timezone_region AS DATE) 
AND mtx.collection_timestamp < CAST(TO_UTC_TIMESTAMP_TZ(?) at time zone tgt.timezone_region AS DATE)
```
    In this example, mtx.collection_timestamp is in the target time zone and the bind variable (for both the start and end time represented by ?) is the UTC time zone string that is cast to the target time zone for comparison with collection_timestamp. When constructing the WHERE clause for a time input parameter:
    - Avoid using a function around timestamp columns that are indexed or partitioned, as this may nullify the usage of the index or partition. If you are bounding timestamps by an upper or lower value, apply the function to that side of the equality operation.
    - Ensure that the TIMESTAMP and DATE values contain no built-in time zone information but can be cast from one time zone to another.
  - SELECT clause
```
SELECT FROM_TZ(CAST(mmh.rollup_timestamp as TIMESTAMP), mmh.timezone_region) AT TIME ZONE 'UTC' from mgmt$metric_hourly mmh
WHERE ...
```
    In this example, a column select statement is used to convert time in one particular time zone (timezone_region) to the UTC time zone and return a value in the TIMESTAMP WITH TIME ZONE format.
    
    Note:
    You must ensure that the selected TIMESTAMP and DATE values are returned in the correct time zone as various columns and tables in Enterprise Manager store TIMESTAMP and DATE values differently.
2. In the Settings tab in the lower-right corner, click Add input +.
3. In the Configure input for Untitled Widget (Required) dialog:
  1. Is time input: Select this check box.
  2. Input Name: This field is not editable and displays time.
  3. Input Label: Edit the default display name, Time, if required.
  4. Start Time Indexes: Enter the index value to map the time input parameter's start time value to the bind parameter marker in the SQL query.
  5. End Time Indexes: Enter the index value to map the time input parameter's end time value to the bind parameter marker in the SQL query.
  6. Default settings: Review and make changes to the default settings, if required.
    
    Note:
    If you update the Default settings to configure the input parameter, the same settings will be applied whenever this widget is added to a different dashboard. To configure an input parameter whose value can be edited after the widget is created and added to the widget library, use the options in the Configure widget input section of this dialog.
    
    For information on the Default settings, see To add a regular input parameter and specify a custom value.
  7. Configure widget input: Specify the values for the time input parameter:
    - Link with Dashboard time: Select to link the time input parameter values with the time range selected for the dashboard.
    - Specify the Time input: Select this option to specify a custom time range.
    - Not configured: Select this option to leave the input unconfigured.
  8. Click Save changes.

On adding the regular or time input parameters to the SQL query, you can view the input parameter and value in Configured widget inputs in the Settings tab. You can also view the bind index mapped to the input parameter in Parameter Index Mapping adjacent to the SQL query.

Review the changes made to the SQL query and click Run.

On clicking Run, the query is executed and data is displayed in the widget. Below the widget, a table with the various attributes of the raw data for the widget is displayed and you can click Hide raw data to hide it.

Use the other tabs in the lower-right corner to perform the following tasks:

Note:

You can click JSON in the upper-right corner to specify or edit the details on the About, Visualization, and Settings tabs, in a JSON editor. The JSON editor provides you with greater flexibility and more options to visualize data in the widget, however, it's recommended that you use it only if you are familiar with editing widget JSON. For more information on the JSON editor, contact Oracle Support.

About: Add a name and description for the widget.
Visualization: Select a chart type and customize the visualization by specifying or modifying additional options.
If you select Table as the chart type, then you have the option of removing some of the columns or select the All Columns check box to display all columns. Chart types that have Y and X axes, such as the Line chart have the following additional visualization options:
- Y Axis: Select the data attribute you want projected on the Y axis. Y axis supports numeric data attributes and only those are listed.
- X Axis: Select the data attribute you want projected on the X axis.
- Series: Select the data attribute that you want to plot in separate series in the chart. To use this option, add the GROUP BY clause for a certain data attribute in the query and view the categories of data in the widget. This option is also available for a Pie chart.
- Color By: Select the data attribute for which you want to view color distinct values in the chart. This option is also available for a Pie chart.
- Y Axis Title: Specify a title for the data attribute projected on the Y axis.
- Legend: Specify the location of the legend in the widget. This option is also available for a Pie chart.
- Stacked: Select this option if you want to stack the data attributes displayed in the chart, by color.
- Use solid color: When you use an Area chart for visualization, the area that represents the presence of data is covered in solid color. This option is selected by default.

Click Save to save the widget.