Oracle® Enterprise Manager Extensibility 10g Release 2 (10.2) for Windows or UNIX Part Number B16246-02 |
|
|
View PDF |
Defining new target types in Enterprise Manager via Management Plug-ins also provides you with the opportunity to add new report definitions. Plug-ins allow you to add permanent (SYSTEM) target type-specific report definitions to Enterprise Manager using the Information Publisher PL/SQL API.
This chapter covers the following:
Note:
You must have a working knowledge of SQL and PL/SQL before using this API. Refer to the Oracle Database PL/SQL User's Guide and Reference for more information.Adding report definitions via Management Plug-in creates target type-specific SYSTEM reports. SYSTEM report definitions are handled differently than definitions created through the Information Publisher user interface. SYSTEM reports are permanent and cannot be deleted or edited by Enterprise Manager administrators. You can add multiple report definitions to a Management Plug-in, thus allowing you to associate multiple reports with a specific target type.
Adding SYSTEM report definitions using Management Plug-ins and the Information Publisher API allows users to access reports from two areas of the Enterprise Manager console:
Reports page of the target home page (optional).
Information Publisher's Report Definition page.
In the report definition file, you can specify whether or not a report is available from the Reports page of the target home page. Report definitions you add to the Reports page are available from the View Reports drop-down menu list. The following figure shows the Reports page for a Microsoft SQL Server target.
Figure 4-1 Microsoft SQL Server Reports Page
Regardless of whether you specify that a report appear in the Reports page, all report definitions added via Management Plug-ins are available from Information Publisher's Report Definitions page. As with out-of-box SYSTEM report definitions, those added via Management Plug-ins are organized according to report category and subcategory. SYSTEM report definitions cannot be deleted from the Enterprise Manager console. Figure 4-2, "Report Definitions Page" shows available report definitions for Microsoft SQL Server target types.
A report definition file is structured as a conventional PL/SQL block and contains code to extract pertinent information from the Management Repository and the report elements used to format and display that data. The Information Publisher PL/SQL API allows you specify the report elements and parameters that you normally specify interactively when creating a report definition from the Enterprise Manger console.
The following example shows the Reports page for an "oracle_orgchart" target type that was added as a Management Plug-in. A single report titled "Org breakdown by title (oracle_orgchart)" is shown in Figure 4-3. This report consists of three major areas: a pie chart showing the percentage distribution of people by title, a bar chart showing the top five managers having the most people reporting to them, and a table listing all managers in the organization the total number of direct reports.
The report definition used for this example utilizes two report element types: Chart from SQL (pie and bar), and Table from SQL. In general, these two report elements will meet most reporting needs.
Management Plug-ins allow you to define as many report definition files as is required for a particular target type. The content of a report definition file consists of a PL/SQL block that uses the Information Publisher PL/SQL API (part of the MGMT_IP package) to construct a report.
The process of developing a valid report definition file involves three steps:
Define SQL or PL/SQL queries used to extract information from the Management Repository.
Create a test report interactively from the Enterprise Manager console.
Define SQL or PL/SQL queries used to extract information from the Management Repository.
The first step in creating your report definition is to create the SQL or PL/SQL queries used to extract the requisite report information from the Management Repository. Enterprise Manager provides management views with which you can safely extract data from the Management Repository without reading from the base tables. Using repository views protects your queries from changes to the repository schema that may occur in future releases and ensures your SYSTEM report definitions remain functional. A complete listing of repository views can be found in Chapter 6, "Management Repository Views".
The following query was used to extract repository information about the distribution of employee classifications within an Oracle organization. The query uses the MGMT$METRIC_CURRENT repository view.
select 'senior mts', count(value) from mgmt$metric_current where metric_column = 'Title' and LOWER(value) like '%senior member%' and target_guid = ??EMIP_BIND_TARGET_GUID?? union select 'principal mts', count(value) from mgmt$metric_current where metric_column = 'Title' and LOWER(value) like '%principal member%'and target_guid = ??EMIP_BIND_TARGET_GUID?? union select 'consulting mts', count(value) from mgmt$metric_current where metric_column = 'Title' and LOWER(value) like '%consulting%' and target_guid = ??EMIP_BIND_TARGET_GUID?? ;
When an administrator views a report from the Enterprise Manager console that contains this SQL query string, Information Publisher automatically binds the unique identifier for the selected target to the ??EMIP_BIND_TARGET_GUID?? placeholder in the SQL query string. The documentation for Chart from SQL and Table from SQL parameters provide information on this bind variable placeholder as well as others you can include in your SQL query string.
The data returned from the SQL query is then used by the Chart from SQL report element to render the pie chart shown in the Reports page of an oracle_orgchart target home page as shown in Figure 4-3.
Create a test report interactively from the Enterprise Manager console.
Once you have written and tested the SQL or PL/SQL query, you can use the Enterprise Manager console to generate a version of your report interactively using the Chart from SQL and Table from SQL report elements. By using the Information Publisher user-interface, you can easily prototype reports without having to create a report definition file and import Management Plug-in Archives. You can also use this method of interactive prototyping to refine your queries and ensure that the data extracted from the Management Repository and how that information is rendered in your report meets your reporting requirements. See Enterprise Manager online help for the Chart from SQL and Table from SQL 'Set Parameters' function for information and examples on how to format your queries.
Use the PL/SQL API to create a report definition file.
Once you are satisfied with the way your report is being rendered by Information Publisher, you are ready to create the report definition file. As previously mentioned, the report definition file consists of a PL/SQL block defining the report elements and the SQL or PL/SQL queries used by the elements to extract repository information and render the report. Example 4-1 shows the content of the report definition file associated with the oracle_orgchart target type.
Example 4-1 Oracle Organization Report Definition File
BEGIN DECLARE l_target_types MGMT_IP_TARGET_TYPES; l_param_values MGMT_IP_PARAM_VALUE_LIST; l_target_type MGMT_IP_TARGET_TYPES; l_report_guid RAW(16); l_element_guid RAW(16); l_report_title_nlsid VARCHAR2(128); l_report_owner VARCHAR(100); l_curr_order NUMBER; l_curr_row NUMBER; BEGIN -- specify the target type associated with this report -- in this case the target type is 'oracle_orgchart' l_target_type := MGMT_IP_TARGET_TYPES(); l_target_type.extend(1); l_target_type(1) := 'oracle_orgchart'; l_report_title_nlsid := 'Org breakdown by title (oracle_orgchart)<>'; l_report_owner := mgmt_user.get_repository_owner; -- create a report definition for the report l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => l_report_title_nlsid, p_description_nlsid => 'Charts showing the breakdown by title', p_owner => l_report_owner, p_category_nlsid => 'Test Org Chart Reports', p_sub_category_nlsid => 'Interesting Org Data', p_late_binding_target_types => l_target_type, p_show_table_of_contents => 0, p_system_report => 1, p_component_name => l_target_type(1) ); -- Add report so that it shows up under "reports" tab in default home page mgmt_mp_homepage.add_report ( p_target_type => l_target_type(1), p_report_title => l_report_title_nlsid, p_report_owner => l_report_owner, p_report_order => 1 ); -- create the first element in the report -- it will be a text element with a description of the report contents -- set the parameters for the styled text element -- we?ll provide a message and a display style l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(2); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'Test Org Chart Monitoring'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); -- initialize the sequencing variables -- l_curr_order should sequentially increase from 1 to the number of elements in -- the report -- l_curr_row indicates the row on which to display an element l_curr_order := 1; l_curr_row := 1; l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_STYLED_TEXT', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlslid => null, p_element_order => l_curr_order, p_element_row => l_curr_row, p_parameters => l_param_values, p_targets => null ); -- the second element in the report is a pie chart showing -- the count of employees by title l_param_values := mgmt_ip_param_value_list(); l_param_values.extend(3); l_param_values(1) := mgmt_ip_param_value_record( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'select ''senior mts'', count(value) from mgmt$metric_current where metric_column = ''Title'' and LOWER(value) like ''%senior member%'' and target_guid = ??EMIP_BIND_TARGET_GUID?? union select ''principal mts'', count(value) from mgmt$metric_current where metric_column = ''Title'' and LOWER(value) like ''%principal member%'' and target_guid = ??EMIP_BIND_TARGET_GUID?? union select ''consulting mts'', count(value) from mgmt$metric_current where metric_column = ''Title'' and LOWER(value) like ''%consulting%'' and target_guid = ??EMIP_BIND_TARGET_GUID?? ' ); l_param_values(2) := mgmt_ip_param_value_record( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'pieChart'); l_param_values(3) := mgmt_ip_param_value_record( 'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels', 'true'); l_curr_order := l_curr_order + 1; l_curr_row := l_curr_row + 1; -- add pie chart to report definiton -- l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlslid => 'people by title', p_element_order => l_curr_order, p_element_row => l_curr_row, p_parameters => l_param_values , p_targets => null ); --add a bar chart showing number of reports by manager -- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'select * from (select key_value, to_number(value) "Number of Reports" from mgmt$metric_current where target_type = ''oracle_orgchart'' and metric_column = ''Reports'' and value is not null and target_guid = ??EMIP_BIND_TARGET_GUID?? order by to_number(value) DESC ) where rownum < 6' ); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'barChart'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.width', '800'); l_curr_order := l_curr_order + 1; l_curr_row := l_curr_row + 1; l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlslid => 'Top 5 Number of Reports By Manager', p_element_order => l_curr_order, p_element_row => l_curr_row, p_parameters => l_param_values, p_targets => null ); -- the next element is a table the manager name and number of reports per manager l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(1); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'select key_value "Person", to_number(value) "Number of Reports" from mgmt$metric_current where target_type = ''oracle_orgchart'' and metric_column = ''Reports'' and value is not null and target_guid = ??EMIP_BIND_TARGET_GUID?? order by to_number(value) DESC ' ); l_curr_order := l_curr_order + 1; l_curr_row := l_curr_row + 1; l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlslid => 'Number of Reports By Manager', p_element_order => l_curr_order, p_element_row => l_curr_row, p_parameters => l_param_values , p_targets => null ); END; END;
With the ability to add report definitions to Enterprise Manager comes the responsibility of maintaining and updating the report definitions. Familiarity with the way in which Enterprise Manager handles report definitions will allow you to anticipate system behavior and plan for backwards compatibility.
When report definitions are deployed via Management Plug-in, Enterprise Manager only allows newer versions of the report definitions to be installed. Older report definitions are deleted and deregistered so as not to appear on the Reports subtab of a target home page. These actions eliminate potential version conflicts by ensuring that updated report definitions are deployed to clean systems. Enterprise Manager will not install older versions of a report definition.
Report definitions, as with Management Plug-ins in general, should be designed with backwards compatibility in mind. Future versions of report definitions should support previous versions of the target type metadata. Report definition-metadata version incompatibility will be most apparent in the following situations:
Report definitions included with Management Plug-in version 1 AND not included with Management Plug-in version 2 will disappear when version 2 is deployed, even if version 1 deployments remain on some Management Agents.
If version 1 and version 2 of a Management Plug-in are both deployed to the system, Management Agents will collect data based on the metadata of the version installed at that Agent; some will collect for version 1 metadata and some for version 2 metadata. Only the version 2 report definitions will be installed (appear in the Enterprise Manager console). For this reason, version 2 report definitions must support both versions of the metadata.
The Information Publisher PL/SQL API allows you to create a report definition file.
Use the following PL/SQL methods to create and/or manipulate report definitions when creating report definition files.
Call this method to create a new report definition. Once a report definition is created, elements can be added. The create_report_definition method is part of the MGMT_IP PL/SQL package.
Note:
All of the Management Plug-in reports should set p_system_report to 1. This parameter defines the report definition as a SYSTEM report, which cannot be deleted or edited by Enterprise Manager administrators. p_owner should be set tomgmt_user.get_repository_owner
for all Management Plug-in reports.
The p_component_name must be set to the target type of the management plug-in.
Input
Parameter | Description |
---|---|
p_title_nlsid |
report title. |
p_description_nlsid |
description |
p_owner |
owner name (should be the value returned from mgmt_user.get_repository_owner for Plug-In reports) |
p_category_nlsid |
category name |
p_sub_category_nlsid |
subcategory name |
p_late_binding_target_types |
target type for late binding, or null if not late binding |
p_show_table_of_contents |
1=show 0=hide |
p_system_report |
1=system report, 0=end user report. This must be set to 1 for Management Plug-in reports. |
p_show_navigation |
Show navigation headers in report (tabs, etc) 1=show, 0=hide |
p_product_name |
Product name, 'EM'(default) |
p_component_name |
Product component. This must be set to the Management Plug-in target type. |
p_version |
Version, '10.2' (default) |
p_parameters |
Parameters for this report definition |
Output
Returns the GUID for this report definition.
Code
FUNCTION create_report_definition ( p_title_nlsid IN VARCHAR2, p_description_nlsid IN VARCHAR2, p_owner IN VARCHAR2, p_category_nlsid IN VARCHAR2, p_sub_category_nlsid IN VARCHAR2, p_late_binding_target_types IN MGMT_IP_TARGET_TYPES DEFAULT NULL, p_show_table_of_contents IN NUMBER DEFAULT 0, p_system_report IN NUMBER DEFAULT 1, p_show_navigation IN NUMBER DEFAULT 1, p_product_name IN VARCHAR2 DEFAULT 'EM', p_component_name IN VARCHAR2 DEFAULT '', p_version IN VARCHAR2 DEFAULT '10.2.0.1.0', p_parameters IN MGMT_IP_PARAM_VALUE_LIST DEFAULT NULL ) RETURN RAW;
Call this method to add a new report element to an existing report definition. The add_element_to_report_def method is part of the MGMT_IP PL/SQL package.
Input
Parameter | Description |
---|---|
p_report_guid |
GUID to identify the report definition. |
p_element_name_nlsid |
The element name. |
p_element_type_nlsid |
The element type name. |
p_header_nlslid |
The element header or null. |
p_element_order |
The order of this element, 1 based. |
p_element_row |
The row for this element, 1 based. |
p_parameters |
The parameters for this element. |
Output
Returns the GUID for this element instance.
Code
FUNCTION add_element_to_report_def( p_report_guid IN RAW, p_element_name_nlsid IN VARCHAR2, p_element_type_nlsid IN VARCHAR2, p_header_nlsid IN VARCHAR2 DEFAULT NULL, p_element_order IN NUMBER, p_element_row IN NUMBER, p_parameters IN MGMT_IP_PARAM_VALUE_LIST, p_targets IN MGMT_IP_TARGET_LIST ) RETURN RAW;
Call this method to register a report for display in the Reports subtab on the target home page for a report. The add_report method is part of the MGMT_MP_HOMEPAGE PL/SQL package.
The input parameters p_target_type, p_report_title, and p_report_owner MUST be identical to the report definition being registered.
IMPORTANT:
The value returned frommgmt_user.get_repository_owner
must be specified as the report owner in order for the report to appear on the Reports subtab of a target home page.Input
Parameter | Description |
---|---|
p_target_type |
The target type. |
p_report_title |
The report title. |
p_report_owner |
The report owner. |
p_order |
The order the report shows up in the homepage. |
Output
None.
Code
PROCEDURE add_report(p_target_type IN VARCHAR2, p_report_title IN VARCHAR2, p_report_owner IN VARCHAR2, p_report_order IN NUMBER);
Call this function to convert the time zone of v_date_in
from the v_from_tz
time zone to the v_to_tz
time zone.
Input
Parameter | Description |
---|---|
v_date_in |
Date to be converted to a different time zone |
v_from_tz |
time zone of date being converted. |
v_to_tz |
time zone into which date will be converted. |
Output
Returns the adjusted date in the new time zone.
Code
FUNCTION ADJUST_TZ(v_date_in DATE, v_from_tz VARCHAR2, v_to_tz VARCHAR2) RETURN DATE;
Three PL/SQL types are required to use the PL/SQL methods documented in Chapter4, "PL/SQL Methods for Creating Report Definitions" . The definitions for these types are shown below.
Use MGMT_IP_TARGET_TYPES type to pass the target type your report definition supports to the create_report_definition API as the p_late_binding_target_types parameter.
CREATE OR REPLACE TYPE MGMT_IP_TARGET_TYPES AS TABLE OF VARCHAR(64);
Example 4-2 MGMT_IP_TARGET_TYPES
DECLARE l_target_type MGMT_IP_TARGET_TYPES; BEGIN -- specify the target type associated with this report -- in this case the target type is 'oracle_orgchart' l_target_type := MGMT_IP_TARGET_TYPES(); l_target_type.extend(1); l_target_type(1) := 'oracle_orgchart'; END;
MGMT_IP_PARAM_VALUE_LIST and MGMT_IP_PARAM_VALUE_RECORD
Use the MGMT_IP_PARAM_VALUE_LIST type to pass parameter values to the create_report_definition API as the p_parameters parameter and to the add_element_to_report_def API as the p_paremeters parameter.
CREATE OR REPLACE TYPE MGMT_IP_PARAM_VALUE_LIST AS TABLE OF MGMT_IP_PARAM_VALUE_RECORD;
Use the MGMT_IP_PARAM_VALUE_RECORD type to create a named parameter value pair to add to an object of type MGMT_IP_PARAM_VALUE_LIST.
CREATE OR REPLACE TYPE MGMT_IP_PARAM_VALUE_RECORD AS OBJECT (PARAM VARCHAR2(100), VALUE CLOB);
Example 4-3 MGMT_IP_PARAM_VALUE_RECORD and MGMT_IP_PARAM_VALUE_LIST
DECLARE l_param_values MGMT_IP_PARAM_VALUE_LIST; BEGIN l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(2); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'Test Org Chart Monitoring'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); END;
Parameters used by some report elements dictate the operational behavior of those elements. This section lists the parameters associated with specific report elements.
The Table Element is used to show a tabular view of query results. The queries must be made against management views.
Element Name: IPMSG_USER_TABLE_FROM_SQL
Element Type: IPMSG_ANY_TARGET_TYPE
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TimePeriodParam" |
Required |
No |
Default Value |
Null |
Valid Values |
"0:0" for last 24 Hours "0:1" for last 7 Days "0:2" for last 31 Days |
Summary |
Encoded time period. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn" |
Required |
No |
Default Value |
The first column in result set. |
Valid Values |
Any valid column name. |
Summary |
If this parameter is set, the sort column indicator will be shown for the column with this column name. If not set, the sort column indicator is shown on the first column. The SQL query should include an 'order by' clause that sorts by this column. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder" |
Required |
No |
Default Value |
"ascending" |
Valid Values |
"ascending" or "descending" |
Summary |
If this parameter is set, the sort column indicator will be shown either as ascending or descending, according to the value. If not set, the sort column indicator is shown as ascending. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay" |
Required |
No |
Default Value |
|
Valid Values |
Positive integer value. |
Summary |
If this parameter is set and only one row is returned from the query, the results are displayed in a vertical list of name-value pairs. This value should be set to the number of name/value columns that should be displayed, normally "1". |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow" |
Required |
No |
Default Value |
"10" |
Valid Values |
Positive integer value. |
Summary |
Number of rows to display at one time in the generated table. The user can scroll through additional rows using the UI controls.. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql" |
Required |
No |
Default Value |
"false" |
Valid Values |
"true" or "false" |
Summary |
Whether a SQL statement is PL/SQL. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.sqlStatement" |
Required |
No |
Default Value |
<None> |
Valid Values |
Any valid SQL SELECT statement. |
Summary |
SQL statement can optionally bind values for targets, locale information, and start/end date. The format of the SQL statement should include a bind variable placeholders for the options to be bound. Bind Placeholders
There should be no semi-colon (;) appended to the end of the SQL statement unless it is a PL/SQL statement. |
Example 4-4 Specifying an Anonymous PL/SQL Block as a Parameter to an Element Definition
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'BEGIN DECLARE rep_tz VARCHAR2(200); user_tz_in VARCHAR2(200); TYPE CURSOR_TYPE IS REF CURSOR; result_cursor_out CURSOR_TYPE; start_date_in DATE DEFAULT NULL; end_date_in DATE DEFAULT NULL; query_string VARCHAR(6000); BEGIN result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??; start_date_in := ??EMIP_BIND_START_DATE??; end_date_in := ??EMIP_BIND_END_DATE??; user_tz_in := ??EMIP_BIND_TIMEZONE_REGION??; select TIMEZONE_REGION into rep_tz from mgmt$target where TARGET_TYPE = ''oracle_emrep''; query_string := ''WITH dates AS (SELECT mgmt_view_util.adjust_tz(:1,:2,:3) as start_date, mgmt_view_util.adjust_tz(:4,:5,:6) as end_date from dual) SELECT label, time, sum(violations) --) as violations FROM( ( SELECT ''''''||''NEW_LBL''||'''''' as label, mgmt_view_util.adjust_tz(rollup_timestamp, :7, :8) as time, average as violations --new violations FROM MGMT$METRIC_HOURLY WHERE key_value in (''''18'''',''''20'''',''''25'''') AND ROLLUP_TIMESTAMP > (SELECT start_date FROM dates) AND ROLLUP_TIMESTAMP < (SELECT end_date FROM dates) AND target_type = ''''oracle_emrep'''' AND metric_name = ''''TARGET_SECURITY_NEW_VIOLATIONS'''' AND metric_column = ''''NEW_VIOLATIONS'''' ) UNION ( SELECT ''''''||''FIXED_LBL''||'''''' as label, mgmt_view_util.adjust_tz(rollup_timestamp,:9,:10) as time, average as violations --cleared violations FROM MGMT$METRIC_HOURLY WHERE key_value in (''''18'''',''''20'''',''''25'''') AND ROLLUP_TIMESTAMP > (SELECT start_date FROM dates) AND ROLLUP_TIMESTAMP < (SELECT end_date FROM dates) AND target_type = ''''oracle_emrep'''' AND metric_name = ''''TARGET_SECURITY_CLEARED_VIOLATIONS'''' AND metric_column = ''''CLEARED_VIOLATIONS'''' ) ) GROUP BY time, label ORDER BY time ASC''; OPEN result_cursor_out for query_string using start_date_in, user_tz_in, rep _tz, end_date_in, user_tz_in, rep_tz, user_tz_in, rep_tz, user_tz_in, rep_tz; END; END;');
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.maxNumberOfRowsAllowed" |
Required |
No |
Default Value |
"2000" |
Valid Values |
Any scalar numeric value. |
Summary |
Set the maximum number of rows retrieved for display in the table. For example, show the top 10 xyz's element would set the value to "10". |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.nullDataStringSubstitue" |
Required |
No |
Default Value |
"" |
Valid Values |
A string. |
Summary |
A string that will be substituted for null values returned. |
Split Table into Multiple Tables by Column
Attribute | Description |
---|---|
Parameter Name |
TableRenderBean.TABLE_SPLIT_COLUMN |
Parameter String |
"oracle.sysman.eml.ip.render.elem.TableRender.tableSplitColumn" |
Required |
No |
Default Value |
null |
Valid Values |
Any valid column name. |
Summary |
If this parameter is set, the table will be split into separate tables with subheaders as the value in this column changes. The data should be ordered by this column. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader"n |
Required |
No |
Default Value |
null |
Valid Values |
Header string to use for a column group. |
Summary |
This parameter provides a column header string. This column group header will span columns between the columns specified in "oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStart Col"n and oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol"n. The n suffix is a numeric value starting with 1 for the first column group, sequentially ascending for subsequent column groups. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol"n |
Required |
No |
Default Value |
null |
Valid Values |
Any valid column name. |
Summary |
Specifies the first column for a given column group. The n suffix is a numeric value starting with 1 for the first column group, sequentially ascending for subsequent column groups. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol"n |
Required |
No |
Default Value |
null |
Valid Values |
Any valid column name. |
Summary |
Specifies the last column for a given column group. The n suffix is a numeric value starting with 1 for the first column group, sequentially ascending for subsequent column groups. |
Use Separate Rows for Values within a Cell
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.useSeparateRowsColumns" |
Required |
No |
Default Value |
null |
Valid Values |
Comma separated list of valid column names. |
Summary |
If this parameter is set, the delimited values of the column with the given name specified will be displayed on separate rows within a containing row cell. More than one column can be designated for this treatment by adding comma-separated column names. |
Use Separate Rows as Delimiters
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.useSeparateRowsDelimiter" |
Required |
No |
Default Value |
, (comma) |
Valid Values |
Any string. |
Summary |
A character used to delimit tokens within a string. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.severityColumn" |
Required |
No |
Default Value |
null |
Valid Values |
Any valid column names. |
Summary |
A severity icon will be substituted for valid severity values returned. To omit an icon, your result set can contain null values in this column. |
Availability Status Icon in Column
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.availabilityStatusColumn" |
Required |
No |
Default Value |
null |
Valid Values |
Any valid column names. |
Summary |
An availability status icon will be substituted for valid values returned. To omit an icon your result set can contains null values in this column. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.imageFilenameColumns" |
Required |
No |
Default Value |
null |
Valid Values |
Comma separated list of column names. |
Summary |
Optional parameter to display the given image filename in the indicated columns. Indicate for which columns the given image should be rendered. Specify a comma separated list of column names. The image filename returned should contain a relative path starting with '/images' such as '/images/xyz.gif'. Normally, a SQL decode function would be used to translate a numeric value into the appropriate image filename. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TableRender.targetTypeColumns" |
Required |
No |
Default Value |
null |
Valid Values |
Comma separated list of column names. |
Summary |
Optional parameter to indicate for which columns the value returned should be used as an internal target type to be translated into a display string for that type. Specify a comma separated list of column names. |
The Chart Element is used to show a graphical view of query results. The queries must be made against Management Repository views.
Element Name: IPMSG_USER_CHART_FROM_SQL
Element Type: IPMSG_ANY_TARGET_TYPE
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.ChartParamController.chartType" |
Required |
No |
Default Value |
"pieChart" |
Valid Values |
"barChart" or "lineChart" or "pieChart" or "timeSeriesChart" "timeSeriesBarChart" |
Summary |
Chart type to display. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TimePeriodParam" |
Required |
No |
Default Value |
Null |
Valid Values |
"0:0" for last 24 Hours "0:1" for last 7 Days "0:2" for last 31 Days |
Summary |
Encoded time period. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.ChartParamController.fill" |
Required |
No |
Default Value |
"none" |
Valid Values |
"none", "absolute", or "cumulative" |
Summary |
Indicates if a line chart should fill the area under the lines. "none": no fill under lines. "absolute": lines are identical to "none" setting but with the area under the lines filled. "cumulative": causes the values for the lines to be added or stacked, then the areas underneath the lines are filled. Use caution when using the fill attribute to ensure there is no confusion for the report user as to whether the data in the chart is cumulative or absolute. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.ChartParamController.height" |
Required |
No |
Default Value |
"200" |
Valid Values |
n, where n is any String that will correctly parse to a positive integer. |
Summary |
Sets the display height of the chart in pixels. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.ChartParamController.visualOrientation" |
Required |
No |
Default Value |
"horizontal" |
Valid Values |
"horizontal" or "vertical" |
Summary |
Visual orientation of the chart. This attribute is only valid with the chartType attribute set to barChart or timeSeriesChart. The attribute does not affect the pieChart. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.ChartParamController.legendPosition" |
Required |
No |
Default Value |
"east" |
Valid Values |
"default", "east", "south" |
Summary |
Specifies where the legend should be placed relative to the chart. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql" |
Required |
No |
Default Value |
"false" |
Valid Values |
"true" or "false" |
Summary |
Set to "true" to indicate that the SQL statement is a PL/SQL statement. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.sqlStatement" |
Required |
No |
Default Value |
<None> |
Valid Values |
Any valid SQL SELECT statement or PL/SQL block. |
Summary |
SQL or PL/SQL statement can optionally bind values for targets, locale information, and start/end date. The format of the statement should include a bind variable placeholders for the options to be bound. Bind Placeholders
There should be no semi-colon (;) appended to the end of the SQL statement unless it is a PL/SQL statement. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.ChartParamController.stacked" |
Required |
No |
Default Value |
"false" |
Valid Values |
"true" or "false" |
Summary |
Indicates if a bar chart should be stacked. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.ChartParamController.title" |
Required |
No |
Default Value |
<None> |
Valid Values |
|
Summary |
Chart title to identify chart for Americans with Disabilities Act compliance. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.ChartParamController.width" |
Required |
No |
Default Value |
"400" |
Valid Values |
n, where n is any String that will correctly parse to a positive integer. |
Summary |
Specifies the display width of the element in pixels. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.yAxisLabel" |
Required |
No |
Default Value |
<None> |
Valid Values |
String |
Summary |
If this parameter is supplied, it is used as the y-axis label for charts that have an y-axis. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels" |
Required |
No |
Default Value |
<None> |
Valid Values |
"true" or "false" |
Summary |
If this parameter is supplied, it controls whether each slice is labeled with a percentage value. This attribute is ignored for chartTypes other than pieChart. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.ChartParamController.pieValuesInLegend" |
Required |
No |
Default Value |
"value" |
Valid Values |
"percent", "value" or "none" |
Summary |
For pie charts, this parameter specifies whether values for pie slices are included in the legend along with the label for the pie slice. The default value for this attributes is "value". If specified as either "percent" or "value" then the numeric value is displayed along with the pie slice label in the form, "pie slice label (numeric value)". If "percent" is specified, then the percentage out of the total of all slice values is calculated and displayed, otherwise, the raw value of the slice is displayed. To omit a value in the legend, specify "none" as a value for this parameter. This attribute is ignored for chartTypes other than pieChart. |
Classes directly involving the Metric Details report element and its parameters are located in the oracle.sysman.eml.ip.render.elem
package. This element also accesses parameter-related constants defined in the oracle.sysman.emSDK.eml.EmlConstants class.
Element Name: IPMSG_METRIC_DETAILS
Element Type: IPMSG_ANY_TARGET_TYPE
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.MetDetInternalTargetType" |
Required |
No |
Default Value |
"oracle_database" |
Valid Values |
Any valid internal target type name. |
Summary |
The type of target to be shown in the graph. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.MetDetSelectedMetric" |
Required |
Yes |
Default Value |
|
Valid Values |
Valid metric name according to target type selected. |
Summary |
Metric to be graphed. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.MetDetSelectedMetricColumn" |
Required |
Yes |
Default Value |
|
Valid Values |
Valid column name according to the metric and target type selected. |
Summary |
Column of metric to be graphed. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TimePeriodParam" |
Required |
No |
Default Value |
null |
Valid Values |
"0:0" for last 24 Hours "0:1" for last 7 Days "0:2" for last 31 Days |
Summary |
Encoded time period. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.MetDetWidth" |
Required |
No |
Default Value |
300 |
Valid Values |
n, where n is any String that will correctly parse to a positive integer. |
Summary |
Width of the image in pixels. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.MetDetHeight" |
Required |
No |
Default Value |
300 |
Valid Values |
n, where n is any String that will correctly parse to a positive integer. |
Summary |
Height of the image in pixels. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.MetDetLegendPosition" |
Required |
No |
Default Value |
|
Valid Values |
"south" (default), "east" |
Summary |
Position of the legend relative to the chart. |
The Text Element is used to display any message text you wish to provide for your report.
Element Name: IPMSG_STYLED_TEXT
Element Type: IPMSG_ANY_TARGET_TYPE
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage" |
Required |
No |
Default Value |
"" (empty String) |
Valid Values |
Any message |
Summary |
Set the message to display in the report. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass" |
Required |
No |
Default Value |
"OraInstructionText" |
Valid Values |
"OraInstructionText" "OraTipText" |
Summary |
Specifies the style class for the message text to adopt when displayed. |
Attribute | Description |
---|---|
Parameter Name |
"oracle.sysmn.eml.ip.render.elem.TextParamBean.textDestination" |
Required |
No |
Default Value |
None |
Valid Values |
Any URI. |
Summary |
Specifies an optional link destination for this text element. |
The following parameters apply to all reporting elements within the report definition.
You can provide a dynamic time period selector for your report definition that allows the report user to choose a specific time period with which to view the report. The dynamic time period option is available only when viewing reports from the Reports tab, is not available on a target home page report. The time period used on a target home page report is the default time period set for the report definition. To specify this using the PL/SQL API when creating your report definition file, set the following parameters for your report definition.
Example 4-5 Dynamic Time Selector Report Definition Parameters
l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); -- this report has a time period associated with it l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodOption', 'true'); -- the user can customize the time period while viewing the report l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust', 'true'); -- set the default value to last 24 hours l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodParam', '0:0');
Pass l_param values
to mgmt_ip.create_report_definition
as the p_parameters
argument.
If you are using Table from SQL or Chart from SQL report elements, you can structure your SQL statement such that the start and end dates will be bound automatically for you by Information Publisher. You achieve this by inserting placeholders (for example, ??EMIP_BIND_START_DATE??
) for the start and end date values as shown in Example 4-6.
Example 4-6 Automatic Binding of Start and End Dates
'SELECT COLUMN_LABEL, ROLLUP_TIMESTAMP, AVERAGE FROM MGMT$METRIC_HOURLY WHERE TARGET_GUID = ??EMIP_BIND_TARGET_GUID?? AND METRIC_LABEL = ''Load'' AND KEY_VALUE = '' '' AND ROLLUP_TIMESTAMP > ??EMIP_BIND_START_DATE?? AND ROLLUP_TIMESTAMP < ??EMIP_BIND_END_DATE?? ORDER BY ROLLUP_TIMESTAMP'
See the online help documentation for Table from SQL or Chart from SQL for detailed information.
Oracle recommends adhering to the following guidelines when defining the PL/SQL for a report definition file:
Use the PL/SQL API to create your report definition.
Report definitions should be created using the PL/SQL API rather than non-standard coding constructs. Using the PL/SQL API insulates your code from any Management Repository schema changes.
Every report definition added to a Management Plug-in must be defined as a SYSTEM report.
Each call to the create_report_definition
method must pass p_system_report => 1
. SYSTEM report definitions are handled differently in the Enterprise Manager console. For example, SYSTEM report definitions cannot be deleted or edited by administrators.
Specifying the report owner.
You must specify the value returned from mgmt_user.get_repository_owner
as the owner for each report definition. Report definitions specifying any owner other than mgmt_user.get_repository_owner
will not appear in the Reports subtab of the target home page. These report definitions will, however, appear in the Information Publisher Report Definitions page.
The component name must be set to the target type.
The component name must be set to the target type in order for Enterprise Manager to associate specific report definitions with a particular Management Plug-in. For example,
BEGIN -- specify the target type associated with this report -- in this case the target type is 'oracle_orgchart' l_target_type := MGMT_IP_TARGET_TYPES(); l_target_type.extend(1); l_target_type(1) := 'oracle_orgchart'; l_report_title_nlsid := 'Org breakdown by title (oracle_orgchart)<>'; l_report_owner := mgmt_user.get_repository_owner; -- create a report definition for the report l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => l_report_title_nlsid, p_description_nlsid => 'Charts showing the breakdown by title', p_owner => l_report_owner, p_category_nlsid => 'Test Org Chart Reports', p_sub_category_nlsid => 'Interesting Org Data', p_late_binding_target_types => l_target_type, p_show_table_of_contents => 0, p_system_report => 1, p_component_name => l_target_type(1) );
When Using Chart from SQL and Table from SQL elements
If your element accepts a single non-aggregate target (only), which is the case for most Management Plug-in target types, you can take advantage of automatic time zone date adjustment built into the Chart from SQL and Table from SQL elements by setting the 'oracle.sysman.eml.ip.render.elem.adjustTimes'
parameter on your element to 'true'
. When this parameter is set, the start and end dates bound to your SQL query will be adjusted from the report time zone to the target time zone. Conversely, dates returned from the query will be adjusted from the target time zone to the report time zone.
If your element accepts multiple targets or aggregate targets, you are responsible for handling time zone adjustment for your date values. You can obtain the report time zone from the ??EMIP_BIND_TIMEZONE_REGION??
bind variable. In order for the report viewer to understand the dates shown, dates displayed in a report must either conform to the report time zone or explicitly display the time zone associated with each date. The following examples illustrate common use cases.
Example 4-7 Adjusting a date returned in your select statement from the time zone of a given target to the report time zone.
select mgmt_view_util.adjust_tz(tbl.date, tgt.timezone_region, ??EMIP_BIND_TIMEZONE_REGION??)
from mgmt$target tgt, sometable tbl
where <your where clause here>
Example 4-8 Adjusting a report time period start and end dates used in the WHERE clause of your SELECT statement from the report time zone to your targets time zone
select <your selected columns here>
from mgmt$target tgt, sometable tbl
where
tgt.target_guid = ??EMIP_BIND_TARGET_GUID?? and
tbl.Mydate > MGMT_VIEW_UTIL.ADJUST_TZ(
??EMIP_BIND_START_DATE??,
??EMIP_BIND_TIMEZONE_REGION??,
tgt.TIMEZONE_REGION)
and
tbl.Mydate < MGMT_VIEW_UTIL.ADJUST_TZ(
??EMIP_BIND_END_DATE??,
??EMIP_BIND_TIMEZONE_REGION??,
tgt.TIMEZONE_REGION)
Recommended Coding Practice
When calling the PL/SQL API methods, you should use named notation rather than positional notation. If you have =>
in your call, you are using named notation. For example,
l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'Org breakdown by title (oracle_orgchart)', p_description_nlsid => 'Charts showing the breakdown by title', p_owner => mgmt_user.get_repository_owner, p_category_nlsid => 'Test Org Chart Reports', p_sub_category_nlsid => 'Interesting Org Data', p_late_binding_target_types => l_target_type, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_component_name => 'oracle_orgchart' );
Using named notation insulates your report definition from any code changes to future releases of the PL/SQL API and also helps make your code self-documenting.