EM12c Server Report

Ever wanted to see the following type of information you see on the host target homepage in EM12c? Now you can using the select statement I created below.



Below is a sample report I have in my apex application op top of my EM12c repository.

This SQL statement will give you server information from the EM repository tables.

HOST_NAME- server name
HARDWARE- hardware type
OS- os version
FREQ_IN_MHZ- speed of CPU
IMPL- implementation
CPU_CORES- number of cores
CPU_THREADS- number of threads
TOTAL_MEMORY_SIZE_GB- total physical RAM
TOTAL_SWAP_SPACE_IN_GB- total swap space
ROOT_FILESYSTEM_TYPE- type of root file system

-----------SQL Statement-----------------------

SELECT DISTINCT
            a.target_guid,
            CASE
               WHEN host_name LIKE '%.%'
               THEN
                  LOWER (SUBSTR (host_name,
                                 1,
                                   INSTR (host_name,
                                          '.',
                                          2,
                                          1)
                                 - 1))
               ELSE
                  host_name
            END
               host_name,
            system_config hardware,
            OS_summary OS,
            c.freq_in_mhz,
            c.impl,
            cpu_count cpu_cores,
            logical_cpu_count cpu_threads,
            CEIL (mem / 1024) AS total_memory_size_in_gb,
            CEIL (max_swap_space_in_mb / 1024) AS total_swap_space_in_gb,
            d.TYPE AS root_filesystem_type
       FROM MGMT$OS_HW_SUMMARY a,
            MGMT$OS_SUMMARY b,
            (SELECT target_guid, freq_in_mhz, impl
               FROM (SELECT t1.target_guid,
                            T1.FREQ_IN_MHZ,
                            t1.impl,
                            ROW_NUMBER ()
                            OVER (PARTITION BY t1.target_guid ORDER BY t1.impl)
                               rn
                       FROM MGMT$HW_CPU_DETAILS T1)
              WHERE rn = 1) c,
            (SELECT cm_target_guid, TYPE
               FROM CM$MGMT_ECM_OS_FILESYSTEM
              WHERE mount_location IN ('c:\', '/', 'C:\')) d
      WHERE     a.target_guid = b.target_guid
            AND a.target_guid = c.target_guid
            AND a.target_guid = d.cm_target_guid
   ORDER BY 2 ASC;

Note: If you are going to use another account other than SYSMAN to select you will need to have the following privilege.

grant select on MGMT$OS_HW_SUMMARY to <username>;
grant select on MGMT$OS_SUMMARY to <username>;
grant select on MGMT$HW_CPU_DETAILS to <username>;
grant select on CM$MGMT_ECM_OS_FILESYSTEM to <username>;
grant exempt access policy to <username>;