This chapter provides examples of how to use the Management Repository views. It includes examples for the following views:
To return all targets under blackout, enter the following query:
SELECT target_name, target_type, start_time, end_time FROM mgmt$blackout_history WHERE sysdate BETWEEN start_time AND NVL(end_time,sysdate+1/60*60*24);
The MGMT$BLACKOUTS file contains definitions of all blackouts known in the system. To view a list of future scheduled blackouts, enter the following query:
SELECT blackout_name, reason, created_by, schedule_type, scheduled_time FROM mgmt$blackouts WHERE status = 'Scheduled';
The MGMT$BLACKOUT_HISTORY file contains an overview of all finished blackouts for each target. To view the number of targets blacked out in the last 30 days, enter the following query:
ORA-00936: missing expressionSELECT target_type, COUNT(*) cnt FROM mgmt$blackout_history WHERE start_time > SYSDATE-30 GROUP BY target_type (*) = 1;
How do I view all the repository compliance rules for a specific author?
How do I view all compliance standards owned by a specific user
How do I query results for compliance standards with no included standards
How do I obtain the results for compliance standards with included standards?
How do I obtain the results for compliance standard rules in a compliance standard for a target?
How do I obtain association information for compliance standards and targets?
How do I obtain the violation ID for an active violation of a compliance rule?
How do I access the compliance rule violation context definition-related metadata?
How do I find all observations (all states) for all bundles in violation?
How do I get a list of all the actions that occurred on all targets during a specific time range?
How do I get a list of all actions that occurred on a single target during a specific time range?
How do I get a list of all unauthorized actions that occurred during a specific time range?
To view a list of all the compliance rules, enter the following query:
SELECT * FROM mgmt$compliance_standard_rule;
To view the monitoring compliance rules only, enter the following query:
SELECT * FROM mgmt$compliance_standard_rule WHERE RULE_TYPE='Monitoring';
To view all the repository compliance rules where the author is John Smith:, enter the following query:
SELECT * FROM mgmt$compliance_standard_rule WHERE RULE_TYPE='Repository' AND AUTHOR='John Smith';
The following queries provide examples about how to use this view:
To view a list of all the compliance standards, enter the following query:
SELECT * FROM mgmt$compliance_standard;
To view all compliance standards owned by John Smith, enter the following query:
SELECT * FROM mgmt$compliance_standard WHERE OWNER='John Smith';
To view a list of all the compliance standard groups, enter the following query:
SELECT * FROM mgmt$compliance_standard_group;
To view all compliance standard groups in production, enter the following query:
SELECT * FROM mgmt$compliance_standard_group WHERE LIFECYCLE_STATUS='Production';
To query results for compliance standards with no included standards, enter the following query, where ? represents the values for each attribute:
SELECT * FROM mgmt$cs_eval_summary WHERE cs_guid = ? AND target_guid = ?;
Note:
To obtain CS_GUID, query the MGMT$COMPLIANCE_STANDARD view on compliance standard attributes such as name or target type. For example:SELECT CS_NAME FROM mgmt$compliance_standard;
The following queries provide examples of how to use this view:
To obtain the results for compliance standards with included standards, enter the following query, where ? represents the value of each attribute:
SELECT * FROM mgmt$composite_cs_eval_summary WHERE root_cs_guid = ? AND root_target_guid = ?;
This query returns values for all the following possible results for the root compliance standard or root target:
ROOT_GUID
RQS_GUID
CS_GUID
ROOT_TARGET_GUID
To obtain the results for compliance standard rules in a compliance standard for a target, enter the following query where ? represents the values for ROOT_CS_GUID and ROOT_TARGET_GUID:
SELECT * FROM mgmt$cs_rule_eval_summary WHERE root_cs_guid = ? AND root_target_guid = ?;
To obtain the results for compliance standard groups, enter the following query where ? is the value for CS_GUID:
SELECT * FROM mgmt$cs_group_eval_summary WHERE cs_guid = ?;
Note:
To obtain CS_GUID, query the MGMT$COMPLIANCE_STANDARD view on compliance standard attributes such as name or target type. For example:SELECT CS_NAME FROM mgmt$compliance_standard;
To obtain association information for compliance standards and targets, enter the following query where ? represents the value for each of the attributes:
SELECT * FROM mgmt$cs_target_assoc WHERE cs_guid = ? AND target_guid = ?;
Note:
To obtain CS_GUID, query the MGMT$COMPLIANCE_STANDARD view on compliance standard attributes such as name or target type. For example:SELECT CS_NAME FROM mgmt$compliance_standard;
To obtain the violation GUID, enter the following query, where ? represents the value for RULE_GUID:
SELECT * FROM mgmt$csr_current_violation WHERE rule_guid=?;
Note:
To obtain the RULE_GUID, query the MGMT$CS_EVAL_SUMMARY view.For more information, see "How do I obtain the results for compliance standard rules in a compliance standard for a target?".
To obtain the additional columns defined in a compliance rule to be collected for a violation, enter the following query where ? represents the value for VIOLATION_GUID:
SELECT * FROM mgmt$csr_violation_context WHERE violation_guid=?;
Note:
To obtain the VIOLATION_GUID, query the MGMT$CSR_CURRENT_VIOLATION view. For example:SELECT * FROM mgmt$csr_current_violation WHERE rule_guid=?;
For more information, see "How do I obtain the violation ID for an active violation of a compliance rule?".
To access the compliance rule violation context definition-related metadata, enter the following query, where ? represents the value for RULE_GUID:
SELECT * FROM mgmt$em_rule_viol_ctxt_def WHERE rule_guid=?;
Note:
To obtain the RULE_GUID, query the MGMT$CS_EVAL_SUMMARY view.For more information, see "How do I obtain the results for compliance standard rules in a compliance standard for a target?".
To find all bundles that are in violation (that is, at least one unauthorized observation in the bundle), enter the following query:
SELECT * FROM mgmt$ccc_all_obs_bundles WHERE bundle_in_violation = 'true';
To find all observations (all states) for all bundles in violation, enter the following query:
SELECT * FROM mgmt$ccc_all_observations o, mgmt$ccc_all_obs_bundles b WHERE o.bundle_id=b.bundle_id AND b.bundle_in_violation='true';
To get a list of all the actions that occurred on all targets during a specific time range, enter the following query where hh:mm is the time in hours and minutes:
SELECT * FROM mgmt$ccc_all_observations WHERE action_time BETWEEN hh:mm AND hh:mm;
To get a list of all actions that occurred on a single target during a specific time range, enter the following query where hh:mm is the time in hours and minutes and target_name is the name of the target:
SELECT * FROM mgmt$ccc_all_observations WHERE action_time BETWEEN hh:mm AND hh:mm and target = target_name;
To get a list of all the file changes that occurred on a single target during a specific time range, enter the following query where hh:mm is the time in hours and minutes and target_name is the name of the target:
SELECT * FROM mgmt$ccc_all_observations WHERE action_time BETWEEN hh:mm and hh:mm and target = target_name and entity_type = 'OS File';
Note:
You can replace'OS File'
with any entity type from the Cloud Control console, such as 'OS Process'
or 'OS User'
.To get a list of all unauthorized actions that occurred during a specific time range, enter the following query where hh:mm is the time in hours and minutes and target_name is the name of the target:
SELECT * FROM mgmt$ccc_all_observations WHERE action_time BETWEEN hh:mm and hh:mm and target = target_name and audit_status='Unauthorized';
To get a list of all occurrences of sudo, enter the following query where hh:mm is the time in hours and minutes and target_name is the name of the target:
SELECT * FROM mgmt$ccc_all_observations WHERE action_time BETWEEN hh:mm and hh:mm and target = target_name and action = 'osuser_sudo_suc';
All possible actions can be seen in the EM_CCC_META_OBSTYPE table.
How do I view the latest comparison job results for a first target, second target combination?
How do I view all the comparison jobs that ran for a first target, second target combination?
How do I view the number of comparison differences for each configuration item in a comparison?
How do I view comparison CCS data source detail information?
How do I view the CCS-parsed data visible to the current Enterprise Manager user?
To view a list of all snapshots visible to the current Enterprise Manager user, including both current and saved snapshots, enter the following query:
mgmt$ecm_visible_snapshotsSELECT * from mgmt$ecm_visible_snapshots WHERE target_type = 'oracle_database';
To view a list of all Enterprise Configuration Management current configuration snapshots and limit access to the snapshots based on the logged in user, enter the following query:
SELECT * from mgmt$ecm_current_snapshots WHERE target_type='oracle_database'mgmt$ecm_current_snapshots
To get targets involved in comparison, enter the following query:
SELECT target_name, target_type FROM mgmt$ecm_cmp_visible_configs;
To get the latest comparison job results for a first target, second target combination, enter the following query:
SELECT job_name, job_owner FROM mgmt$ecm_cmp_job_last_results WHERE first_target ='?' AND second_target='?'
To get all the comparison jobs that ran for a first target, second target combination, enter the following query:
SELECT job_name, job_owner FROM mgmt$ecm_cmp_jobs WHERE first_target ='?' AND second_target='?' ;
To get the number of comparison differences for each configuration item in a comparison.
Bind variables must be specified for the execution.SELECT config_item , total_ci_diffs , first_target, second_target FROM mgmt$ecm_cmp_rpt_ci_diffs WHERE first_target='? AND second_target='?' ;
To view the comparison CCS data source information, such as CCS file name, enter the following query:
SELECT ccs_ds, ccs_ds_attr_diff_type FROM mgmt$ecm_cmp_rpt_ccs_ds WHERE first_target='?' AND second_target='?' ;
To view comparison CCS data source detail information, retrieve the file level differences for a given comparison by entering the following query:
SELECT ccs_ds_attr_diff_type, attr_diff_type, ccs_ds, display_attr_col_name, first_target, second_target FROM mgmt$ecm_cmp_rpt_ccs_ds_dtls ;
To retrieve the CCS-parsed data available for a given comparison (the same data as differences), enter the following query:
SELECT first_target, second_target, ccs_ds, path, attr_col_name, parsed_diff_type, first_attr_value, second_attr_value FROM mgmt$ecm_cmp_rpt_ccs_pd_all ;
To view a list of all CCS data sources or collected CCS files visible to the current Enterprise Manager user, enter the following query:
SELECT ccs_ui_name, display_target_name, data_source_name, full_path FROM mgmt$ccs_data_source_visible WHERE target_type='weblogic_j2eeserver';
To get a list of the current (that is, the most recently collected) CCS data sources or collected CCS files visible to current Enterprise Manager user, enter the following query:
SELECT ccs_ui_name, cm_target_name, data_source_name, full_path FROM mgmt$ccs_data_source;
To view the CCS-parsed data visible to the current Enterprise Manager user, enter the following query:
SELECT distinct ccs_ui_name, data_source_name, attr, value FROM mgmt$ccs_data_visible WHERE target_type='weblogic_j2eeserver';
To get the CCS-parsed current data, enter the following query:
SELECT distinct ccs_ui_name, data_source_name, attr, value FROM mgmt$ccs_data WHERE cm_target_type='weblogic_j2eeserver';
The following example is an example of a rule between an Oracle VM Guest target type and a host. The rule relies on a published EDK MGMT$HW_NIC view for the host and an ECM-generated CM$VT_VM_VNIC view. While the rule resides in the plug-in that defines the Oracle VM Guest target type, then it can reference the CM$ view for the snapshot type belonging to that target type and any EDK-accessible view (such as MGMT$ views) from the host target type, which might reside in a different plug-in.
<Rule name="host_deployed_on_oracle_vm_guest"> <query> SELECT 'deployed_on' AS assoc_type, host.target_guid AS source_me_guid, guest.cm_target_guid AS dest_me_guid FROM mgmt$hw_nic host, cm$vt_vm_vnic guest WHERE guest.mac_address = host.mac_address_std </query> <trigger> <targetType>host</targetType> <snapshotType>ll_host_config</snapshotType> <table>MGMT$HW_NIC</table> <idColumn>source</idColumn> </trigger> <trigger> <targetType>oracle_vm_guest</targetType> <snapshotType>ovm_guest_config</snapshotType> <table>CM$VT_VM_VNIC</table> <idColumn>destination</idColumn> </trigger> </Rule>;
How do I get a list of the secure and unsecure Management Agents for each operating system?
How do I get a list of all the Management Agent-side targets that are discovered today?
How do I get the number of discovered systems for each operating system?
How do I get the maximum number of targets of the same type that are discovered on a single system?
How do I get the number of databases for each category version?
How do I get the number of databases for each category version and CPU count?
How do I get the number of databases for each category version and OS platform?
How do I find the number of hosts grouped by operating system?
How do I find the number of Management Agents grouped for each version?
How do I view a list of all metrics for the Management Agent on the oms.test.com system?
How do I view a list of all clustered targets in the repository?
To return the number of targets for a metric, enter the following query:
SELECT metric_name, COUNT(DISTINCT target_name) FROM mgmt$target_type WHERE target_type = 'oracle_database' GROUP BY metric_name;
To return the number of Management Agents for a version, enter the following query:
SELECT property_value, COUNT(*) FROM mgmt$target_properties WHERE target_type = 'oracle_emd' AND property_name = 'Version' GROUP BY property_value;
To return a list of all Agent-side targets:
SELECT target_type, type_display_name, COUNT(*) cnt FROM mgmt$target WHERE emd_url IS NOT NULL GROUP BY target_type, type_display_name ORDER BY target_type ;
To return a list of the secure and unsecure Management Agents for each operating system, enter the following query:
SELECT DECODE(type_qualifier1,' ','-unknown-',NULL,'-error-',type_qualifier1) os, SUM(DECODE(SUBSTR(emd_url,1,5),'https',1,0)) secure, SUM(DECODE(SUBSTR(emd_url,1,5),'https',0,1)) unsecure FROM mgmt$target WHERE target_type = 'oracle_emd' GROUP BY type_qualifier1 ORDER BY os ;
To get a list of the top 5 Management Agents with the most discovered targets of a given target type, enter the following query:
SELECT host_name, target_type, type_display_name, cnt FROM ( SELECT host_name, target_type, type_display_name, COUNT(*) cnt FROM mgmt$target WHERE emd_url IS NOT NULL GROUP BY host_name, target_type, type_display_name ORDER BY cnt DESC ) WHERE rownum <= 5 ;
To retrieve a list of all the Agent-side targets discovered today, enter the following query:
SELECT target_guid, target_name, target_type, host_name FROM mgmt$target WHERE emd_url IS NOT NULL AND creation_date > TRUNC(SYSDATE) ORDER BY host_name, target_type, target_name ;
To retrieve the number of broken targets for each host, enter the following query:
SELECT host_name, COUNT(*) cnt, broken_reason, MAX(broken_str) broken_str FROM mgmt$target WHERE broken_reason > 0 GROUP BY host_name, broken_reason ORDER BY host_name, broken_reason ;
To get the number of discovered systems for each operating system, enter the following query:
SELECT DECODE(type_qualifier1,' ','-unknown-',NULL,'-error-',type_qualifier1) os, COUNT(*) cnt FROM mgmt$target WHERE target_type = 'host' GROUP BY type_qualifier1 ORDER BY type_qualifier1 ;
To get the maximum number of targets of the same type that are discovered on a single system, enter the following query:
SELECT DISTINCT target_type, type_display_name, cnt FROM ( SELECT host_name, target_type, type_display_name, cnt, RANK() OVER (PARTITION BY target_type ORDER BY cnt DESC) rnk FROM ( SELECT host_name, target_type, type_display_name, COUNT(*) cnt FROM mgmt$target WHERE emd_url IS NOT NULL GROUP BY host_name, target_type, type_display_name ) ) WHERE rnk = 1 AND cnt > 1 ORDER BY target_type ;
To return the listener port for each database, enter the following query:
SELECT target_name, property_value FROM mgmt$target_properties WHERE target_type = 'oracle_database' AND property_name = 'Port';
To return the number of databases for each category version, enter the following query:
SELECT property_value, COUNT(*) FROM mgmt$target_properties WHERE target_type = 'oracle_database' AND property_name = 'VersionCategory' GROUP BY property_value;
To return the number of databases for each category version and CPU count, enter the following query:
SELECT p1.property_value "Version", p2.property_value "CPU Count", COUNT(*) "Total" FROM mgmt$target_properties p1, mgmt$target_properties p2 WHERE p1.target_type = 'oracle_database' AND p1.target_guid = p2.target_guid AND p1.property_name = 'VersionCategory' AND p2.property_name = 'CPUCount' GROUP BY p1.property_value, p2.property_value ORDER BY p1.property_value, p2.property_value;
To return the number of databases for each category version and OS platform, enter the following query:
SELECT p3.property_value "Platform", p1.property_value "Version", COUNT(*) "Total" FROM mgmt$target_properties p1, mgmt$target_properties p2, mgmt$target_properties p3 WHERE p1.target_type = 'oracle_database' AND p1.target_guid = p2.target_guid AND p3.target_name = p2.property_value AND p3.target_type = 'host' AND p1.property_name = 'VersionCategory' AND p2.property_name = 'MachineName' AND p3.property_name = 'OS' GROUP BY p3.property_value, p1.property_value ORDER BY p3.property_value, p1.property_value;
The MGMT$TARGETS file contains all targets defined in Oracle Enterprise Manager Cloud Control. To find the number of hosts grouped by operating system, enter the following query:
SELECT type_qualifier1, COUNT(*) cnt FROM mgmt$target WHERE target_type = 'host' GROUP BY type_qualifier1;
The MGMT$TARGET_COMPOSITE file lists all members of every group target defined in Oracle Enterprise Manager Control. To view a list of targets used in the Oracle Enterprise Manager Cloud Control website definition, enter the following query:
mgmt$target_composite'Grid Control'SELECT member_target_name, member_target_type FROM mgmt$target_composite WHERE composite_name = 'Grid Control' AND composite_type = 'website';
The MGMT$TARGET_MEMBERS file lists all members of all container targets defined in Oracle Enterprise Manager Control. To find the number of targets grouped for each type for the Cloud Control Infrastructure group enter the following query:
gmt$target_composite'GC Infrastructure'SELECT member_target_type, COUNT(*) cnt FROM mgmt$target_members WHERE aggregate_target_name = 'GC Infrastructure' AND aggregate_target_type = 'composite' GROUP BY member_target_type;
The MGMT$TARGET_PROPERTIES file contains the monitoring properties for every target. To find the number of Management Agents grouped for each version, enter the following query:
SELECT property_value, COUNT(*) cnt FROM mgmt$target_properties WHERE property_name = 'Version' AND target_type = 'oracle_emd' GROUP BY property_value;
The MGMT$TARGET_TYPE file contains all metrics collected for each target. To view a list of all metrics for the Management Agent on the system 'oms.test.com', enter the following query:
SELECT metric_label, column_label FROM mgmt$target_type WHERE target_type = 'oracle_emd' AND target_name = 'oms.test.com:3872' AND TRIM(metric_column) IS NOT NULL;
The MGMT$TARGET_TYPE_PROPERTIES file contains all internal monitoring properties for all targets. To view a list of all clustered targets in the Management Repository, enter the following query:
SELECT target_name, target_type FROM mgmt$target_type_properties WHERE property_name = 'is_cluster' AND property_value = 1;
How do I view the number of notifications sent for failed jobs for each job owner?
How do I view a list of all jobs that have the Management Repository itself as a target?
The MGMT$JOBS file contains definitions of all of the jobs defined in the system. To view a list of all running repeating jobs, enter the following query:
SELECT job_name, job_owner, job_type, start_time,schedule_type FROM mgmt$jobs WHERE NVL(end_time,SYSDATE+1) > SYSDATE AND is_library = 0 AND schedule_type != 'One Time';
The MGMT$JOB_ANNOTATIONS file contains an overview of all of the notifications sent out for job state changes. To view the number of notifications sent for failed jobs for each job owner, enter the following query:
SELECT job_owner, COUNT(*) cnt FROM mgmt$job_annotations WHERE job_status = 'FAILED' AND occurrence_timestamp > SYSDATE-30 GROUP BY job_owner;
The MGMT$JOB_TARGETS file contains a list of all targets used in the defined jobs. To view a list of all jobs that have the Management Repository itself as a target, enter the following query:
SELECT job_name, job_owner, job_type FROM mgmt$job_targets WHERE target_type = 'oracle_emrep';
How do I view a list of all public templates?
The MGMT$TEMPLATES file contains all templates defined in Oracle Enterprise Manager Cloud Control. To view a list of all public templates, enter the following query:
SELECT target_type, template_name, owner, created_date FROM mgmt$templates WHERE is_public = 1;
How do I return the current thresholds for the alertlog metric?
How do I view a list of all metric errors for metrics on Management Agents?
How do I find the number of UDM metric errors on host targets in the last 30 days?
To return the current thresholds for the alertlog metric, enter the following query:
SELECT target_name, metric_column, warning_operator, warning_threshold, critical_operator, critical_threshold FROM mgmt$metric_collection WHERE target_type = 'oracle_database' AND metric_name = 'alertLog' ORDER BY target_name, metric_column;
The MGMT$METRIC_ERROR_CURRENT file lists all outstanding metric errors. To view a list of all metric errors for metrics on Management Agents, enter the following query:
SELECT target_name, metric_name, collection_timestamp, error_message FROM mgmt$metric_error_current WHERE target_type = 'oracle_emd';
The MGMT$METRIC_ERROR_HISTORY file contains an overview of all resolved metric errors. To find the number of UDM metric errors on host targets in the last 30 days, enter the following query:
SELECT target_name, COUNT(*) cnt FROM mgmt$metric_error_history WHERE target_type = 'host' AND metric_name = 'UDM' AND error_message IS NOT NULL AND collection_timestamp > SYSDATE-30 GROUP BY target_name;
How do I get a list of all disabled metrics on Management Agents?
How do I get the availability information for the Enterprise Manager website?
How do I get the number of alertlog severities for the database in the last 24 hours?
How do I get a list of all the collected user-defined metrics (UDMs)?
How do I get the first byte response for the Enterprise Manager website at a specific time?
How do I obtain the average number of connections for a listener for a specific period?
How do I find the reasons for host outages lasting longer than one day?
How do I generate a list of all targets currently blacked out?
How do I find all hosts with more than 90 percent CPU utilization?
How do I find the loader throughput of the OMS on the last day?
How do I view a list of all targets with the Response metric disabled?
To return database metrics with outstanding severities, enter the following query:
SELECT target_name, metric_name, COUNT(*), TO_CHAR(MAX(collection_timestamp),'DD-MON-YYYY HH24:MI:SS') FROM mgmt$alert_current WHERE target_type = 'oracle_database' GROUP BY target_name, metric_name;
To return a list of all disabled metrics on Management Agents, enter the following query:
SELECT collection_name, COUNT(*) nr_agents FROM mgmt$target_metric_collections WHERE is_enabled = 0 GROUP BY collection_name ORDER BY collection_name;
To return the number of down targets, enter the following query:
SELECT COUNT(*) FROM mgmt$availability_current WHERE availability_status='Target Down';
To return the availability information for the Enterprise Manager website, enter the following query:
SELECT status, ROUND(duration,2) duration, ROUND((RATIO_TO_REPORT(duration) OVER ())*100,2) AS total FROM (SELECT NVL(availability_status,'-unknown-') status, SUM(NVL(end_timestamp,SYSDATE)-start_timestamp) duration FROM mgmt$availability_history WHERE target_name = 'Enterprise Manager' AND target_type = 'website' GROUP BY availability_status);
To view the number of alertlog severities for the database in the last 24 hours, enter the following query:
ORA-00904: "COLLECTION__TIMESTAMP": invalid identifierSELECT target_name, COUNT (*) FROM mgmt$alert_history WHERE target_type = 'oracle_database' AND metric_name = 'alertlog' AND collection__timestamp > SYSDATE-1 GROUP BY target_name;
To return the current CPU utilization of a host, enter the following query:
SELECT column_label, value FROM mgmt$metric_current WHERE metric_name = 'Load' AND metric_column = 'cpuUtil' AND target_name = 'my.acme.com';
To return a list of all the collected user-defined metrics (UDMs), enter the following query:
sysman.mgmt$metric_current mgmt$metric_currentSELECT key_value udm_name, target_name, target_type, collection_timestamp, value FROM sysman.mgmt$metric_current WHERE metric_label = 'User Defined Metrics' ORDER BY udm_name, target_type, target_name, collection_timestamp DESC;
To return the first byte response for the Enterprise Manager Web site at 11.00 am yesterday, enter the following query:
SELECT target_name, AVG(average) FROM mgmt$metric_hourly WHERE target_name = 'EM Website' AND metric_name = 'http_response' AND metric_column = 'avg_first_byte_time' AND rollup_timestamp = TO_DATE(TO_CHAR(TRUNC(sysdate-1),'DD-MON-YYYY')||' 11:00:00','DD-MON-YYYY HH24:MI:SS') GROUP BY target_name;
To return the average number of connections for a listener for the last seven days, enter the following query:
SELECT target_name, average FROM mgmt$metric_daily WHERE target_type = 'oracle_listener' AND metric_name = 'Load' AND metric_column = 'estConns' AND rollup_timestamp = TRUNC(sysdate-7);
The MGMT$AVAIL_ALERT_HISTORY file contains a list of all available outages. To view the reasons for host outages lasting longer than one day, enter the following query:
mgmt$avail_alert_historySELECT target_name, target_type, collection_timestamp, message FROM mgmt$avail_alert_history WHERE violation_level IN (20,25,125,325) AND alert_duration > 1 AND target_type = 'host' ;
The MGMT$AVAILABILITY_CURRENT file contains an overview of the current state of all targets. To generate a list of all targets currently blacked out, enter the following query:
SELECT target_name, target_type, start_timestamp FROM mgmt$availability_current WHERE availability_status = 'Blackout';
The MGMT$AVAILABILITY_HISTORY file contains the history of all availability state changes for all targets in the repository. To view a list of availability state changes made to targets in the repository in the last 30 days, enter the following query:
SELECT target_name, target_type, collection_timestamp, start_timestamp, end_timestamp, availability_status FROM mgmt$availability_history WHERE target_type = 'oracle_emrep' AND end_timestamp > SYSDATE-30 ORDER BY start_timestamp;
The MGMT$METRIC_CURRENT file contains the last known data points for all metrics on all targets. To find all hosts with more than 90 percent CPU utilization, enter the following query:
SELECT target_name, collection_timestamp, value FROM mgmt$metric_current WHERE target_type = 'host' AND metric_name = 'Load' AND metric_column = 'cpuUtil' AND value > 90;
The MGMT$METRIC_DAILY file contains the daily rollup data of all metrics for all targets. To find the minimum and maximum number of sessions for all Oracle Management Server (OMS) applications in the last 30 days, enter the following query:
SELECT target_name, MIN(MINIMUM) min_val, MAX(maximum) max_val FROM mgmt$metric_daily WHERE target_type = 'oc4j' AND target_name LIKE '%OC4J_EM' AND metric_name = 'oc4j_instance_rollup' AND metric_column = 'session.active' AND rollup_timestamp > SYSDATE-30 GROUP BY target_name;
The MGMT$METRIC_DETAILS file contains metric data points uploaded by the Agent. To find the loader throughput of the OMS on the last day, enter the following query:
SELECT key_value, ROUND(MIN(value),2) min_val, ROUND(MAX(value),2) max_val FROM mgmt$metric_details WHERE target_type = 'oracle_emrep' AND metric_name = 'Management_Loader_Status' AND metric_column = 'load_processing' AND collection_timestamp BETWEEN SYSDATE-2 AND SYSDATE-1 GROUP BY key_value;
The MGMT$METRIC_HOURLY file contains the hourly rollup data of all metrics for all targets. To find the minimum and maximum number from the last full day for the performance of Oracle Enterprise Manager Cloud Control, enter the following query:
'Grid Control'SELECT MIN(MINIMUM) min_val, MAX(maximum) max_val FROM mgmt$metric_hourly WHERE rollup_timestamp BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE) AND target_name = 'Grid Control' AND target_type = 'website' AND metric_name = 'Performance' AND metric_column = 'PerformanceValue' AND key_value = 'Perceived Time per Page (ms)';
The MGMT$TARGET_METRIC_COLLECTIONS file contains all metric collection settings for all targets in the Management Repository. To view a list of all targets with the Response metric disabled, enter the following query:
SELECT target_name, target_type, collection_frequency FROM mgmt$target_metric_collections WHERE is_enabled = 0 AND metric_name = 'Response';
The MGMT$TARGET_METRIC_SETTINGS file contains all metric threshold settings for all targets in the repository. To view a list of all database or Real Application Clusters (RAC) targets that have the tablespace thresholds set to less than 85 for warning and 95 for critical, enter the following query:
SELECT target_name, target_type, warning_threshold, critical_threshold FROM mgmt$target_metric_settings WHERE target_type IN ('oracle_database','rac_database') AND metric_name = 'problemTbsp' AND metric_column = 'pctUsed' AND NVL(TRIM(warning_threshold),0) < 85 AND NVL(TRIM(critical_threshold),0) < 95;
How do I find information about every Management Agent installation?
How do I get a list of the installed Management Agent bundle patches?
How do I get a list of Management Agents that don't have a specific patch installed?
To view information about every Management Agent installation, (for example, where the home name contains the text agent12c), enter the following query:
SELECT host_name, home_location, oh_owner, oh_group FROM mgmt$oh_home_info WHERE oui_home_name LIKE 'agent12c%' ;
To view a list of the installed Management Agent bundle patches, enter the following query:
SELECT host_name, home_location ohome, install_time, patch_id, description FROM mgmt$oh_patch WHERE description LIKE 'EM-AGENT BUNDLE%' ORDER BY host_name ;
For example, to get a list of Management Agents that don't have Enterprise Manager 12c bundle patch 9 installed, enter the following query:
SELECT inst_target_name agent_name, home_location ohome FROM mgmt$oh_installed_targets t WHERE inst_target_type = 'oracle_emd' -- Management Agent AND home_type = 'O' -- Oracle software AND NOT EXISTS (SELECT 1 FROM mgmt$oh_patch p WHERE p.patch_id = '20950034' -- Bundle 9 patch number AND p.home_location = t.home_location AND p.target_guid = t.oh_target_guid) ORDER BY inst_target_name ;
The following query retrieves information from MGMT$OS_SUMMARY for a specific host from the Management Repository:
SELECT * FROM MGMT$OS_SUMMARY
WHERE target_name = 'target_name' and target_type = 'host';
If you know a host name, you can use a similar query to access any of the views to retrieve information at the metric level for a particular host.
Aggregate queries can be written to provide counts of OS as follows:
SELECT name,base_version,count(*) FROM mgmt$os_summary group by name,base_version ;