Examples

20 Examples

This chapter provides examples of how to use the Management Repository views. It includes examples for the following views:

Blackout Views

How do I return all targets under a blackout?

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);

How can I view a list of future scheduled blackouts?

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';

How can I view the number of targets blacked out in the last 30 days?

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:

SELECT target_type, COUNT(*) cnt
FROM   mgmt$blackout_history
WHERE  start_time > SYSDATE-30
GROUP BY target_type
 (*) = 1;

Compliance Views

How do I view a list of all the compliance rules?

To view a list of all the compliance rules, enter the following query:

SELECT * 
FROM mgmt$compliance_standard_rule;

How do I view the monitoring compliance rules only?

To view the monitoring compliance rules only, enter the following query:

SELECT * 
FROM mgmt$compliance_standard_rule WHERE RULE_TYPE='Monitoring';

How do I view all the repository compliance rules for a specific author?

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';

How do I view a list of all the compliance standards?

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;

How do I view all compliance standards owned by a specific user

To view all compliance standards owned by John Smith, enter the following query:

SELECT * FROM mgmt$compliance_standard WHERE OWNER='John Smith';

How do I view a list of all the compliance standard groups?

To view a list of all the compliance standard groups, enter the following query:

SELECT * FROM mgmt$compliance_standard_group;

How do I view all compliance standard groups in production?

To view all compliance standard groups in production, enter the following query:

SELECT * FROM mgmt$compliance_standard_group WHERE LIFECYCLE_STATUS='Production';

How do I query results for compliance standards with no included standards

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;

How do I obtain the results for compliance standards with included standards?

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

How do I obtain the results for compliance standard rules in a compliance standard for a target?

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 = ?;

How do I obtain the results for compliance standard groups?

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;

How do I obtain association information for compliance standards and targets?

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;

How do I obtain the violation ID for an active violation of a compliance rule?

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?".

How do I obtain the violation column information?

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?".

How do I access the compliance rule violation context definition-related metadata?

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?".

How do I find all bundles that are in violation?

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';

How do I find all observations (all states) for all bundles in violation?

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';

How do I get a list of all the actions that occurred on all targets during a specific time range?

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;

How do I get a list of all actions that occurred on a single target during a specific time range?

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;

How do I get a list of all the file changes that occurred on a single target during a specific time range?

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'.

How do I get a list of all unauthorized actions that occurred during a specific time range?

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';

How do I get a list of all occurrences of sudo?

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.

Enterprise Configuration Management Views

How do I view a list of all the Enterprise Configuration Management snapshots visible to the current Enterprise Manager user, including both current and saved snapshots?

To view a list of all snapshots visible to the current Enterprise Manager user, including both current and saved snapshots, enter the following query:

SELECT * from mgmt$ecm_visible_snapshots 
WHERE target_type = 'oracle_database';

How do I get a list of all current configuration snapshots and limit the access to the snapshots based on the logged in user?

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'

How do I view the targets involved in a comparison?

To get targets involved in comparison, enter the following query:

SELECT target_name, target_type FROM mgmt$ecm_cmp_visible_configs;

How do I view the latest comparison job results for a first target, second target combination?

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='?'

How do I view all the comparison jobs that ran for a first target, second target combination?

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='?' ;

How do I view the number of comparison differences for each configuration item in a comparison?

To get the number of comparison differences for each configuration item in a comparison.

SELECT config_item , total_ci_diffs , first_target, second_target 
FROM mgmt$ecm_cmp_rpt_ci_diffs 
WHERE first_target='? 
  AND second_target='?' ;

How do I view the comparison CCS data source information, such as CCS file name and difference status?

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='?' ;

How do I view comparison CCS data source detail information?

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 ;

How do I retrieve the comparison CCS data?

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 ;

How do I get a list of all the CCS data sources or collected CCS files visible to the current Enterprise Manager user?

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';

How do I get a list of the current CCS data sources or collected CCS files visible to current Enterprise Manager user?

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;

How do I view the CCS-parsed data visible to the current Enterprise Manager user?

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';

How do I view the current CCS-parsed data?

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';

Hardware Views

How do I create a derived associations rule which establishes associations between a host and an Oracle VM Guest target on which it is deployed?

How do I create a derived associations rule which establishes associations between a host and an Oracle VM Guest target on which it is deployed?

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>;

Inventory Views

How do I get the number of targets for a metric?

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;

How do I get the number of Management Agents for a version?

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;

How do I get a list of all the Agent-side targets?

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
;

How do I get a list of the secure and unsecure Management Agents for each operating system?

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
;

How do I get a list of the Management Agents with the most discovered targets of a given target type?

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
;

How do I get a list of all the Management Agent-side targets that are discovered today?

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
;

How do I get the number of broken targets for each host?

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
;

How do I get the number of discovered systems for each operating system?

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
;

How do I get the maximum number of targets of the same type that are discovered on a single system?

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
;

How do I get the listener port for each database?

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';

How do I get the number of databases for each category version?

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;

How do I get the number of databases for each category version and CPU count?

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;

How do I get the number of databases for each category version and OS platform?

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;

How do I find the number of hosts grouped by operating system?

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;

How do I view a list of targets used in the Oracle Enterprise Manager Cloud Control website definition?

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:

SELECT member_target_name, member_target_type
FROM   mgmt$target_composite
WHERE  composite_name = 'Grid Control'
  AND  composite_type = 'website';

How do I find the number of targets grouped for each type for the Cloud Control Infrastructure group?

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:

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;

How do I find the number of Management Agents grouped for each version?

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;

How do I view a list of all metrics for the Management Agent on the oms.test.com system?

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;

How do I view a list of all clustered targets in the repository?

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;

Jobs Views

How can I view a list of all running repeating jobs?

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';

How do I view the number of notifications sent for failed jobs for each job owner?

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;

How do I view a list of all jobs that have the Management Repository itself as a target?

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';

Management Template Views

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;

Metric Views

How do I return the current thresholds for the alertlog metric?

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;

How do I view a list of all metric errors for metrics on Management Agents?

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';

How do I find the number of UDM metric errors on host targets in the last 30 days?

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;

Monitoring Views

How do I get database metrics with outstanding severities?

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;

How do I get a list of all disabled metrics on Management Agents?

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;

How do I get the number of down targets?

To return the number of down targets, enter the following query:

SELECT COUNT(*)
FROM   mgmt$availability_current
WHERE  availability_status='Target Down';

How do I get the availability information for the Enterprise Manager website?

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);

How do I get the number of alertlog severities for the database in the last 24 hours?

To view the number of alertlog severities for the database in the last 24 hours, enter the following query:

SELECT 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;

How do I get the current CPU utilization of a host?

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.example.com';

How do I get a list of all the collected user-defined metrics (UDMs)?

To return a list of all the collected user-defined metrics (UDMs), enter the following query:

SELECT 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;

How do I get the first byte response for the Enterprise Manager website at a specific time?

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;

How do I obtain the average number of connections for a listener for a specific period?

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);

How do I find the reasons for host outages lasting longer than one day?

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:

SELECT 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' ;

How do I generate a list of all targets currently blacked out?

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';

How do I view a list of availability state changes made to targets in the repository in the last 30 days?

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;

How do I find all hosts with more than 90 percent CPU utilization?

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;

How do I find the minimum and maximum number of sessions for all OMS applications in the last 30 days?

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;

How do I find the loader throughput of the OMS on the last day?

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;

How do I find the minimum and maximum number from the last full day for the performance of Oracle Enterprise Manager Cloud Control?

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:

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)';

How do I view a list of all targets with the Response metric disabled?

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';

How do I view a list of all database or RAC targets that have the tablespace thresholds set to less than 85 for warning and 95 for critical?

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;

Oracle Home Directory Views

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?

How do I find information about every Management Agent installation?

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%'
;

How do I get a list of the installed Management Agent bundle patches?

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
;

How do I get a list of Management Agents that don't have a specific patch installed?

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
;

Operating System Views

How do I retrieve information from MGMT$OS_SUMMARY for a specific host from the Management Repository?

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 ;