Rem
Rem $Header: csa_oob_config_report.sql 20-apr-2005.07:42:31 niramach Exp $
Rem
Rem csa_oob_config_report.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      csa_oob_config_report.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Out of the box CSA config report
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem       nirama 09/02/05 - Moved the delta between beta->production to v102020.(bug 4582321) 
Rem       nirama 04/20/05 - niramach_repos_upgrade
Rem       agor   03/30/05 - fix 4257600 
Rem       agor   02/14/05 - agor_csa_reports
Rem       agor   02/14/05 - change details 
Rem       agor   11/19/04 - Creation
Rem

SET DEFINE OFF

BEGIN

  DECLARE 
    l_param_values  MGMT_IP_PARAM_VALUE_LIST;
    l_target_types  MGMT_IP_TARGET_TYPES;    
    l_report_guid   RAW(16);
    l_element_guid  RAW(16);

  BEGIN
    l_report_guid := mgmt_ip.create_report_definition (
      p_title_nlsid                   => 'CSA_CONFIG_TITLE',
      p_description_nlsid             => 'CSA_CONFIG_DESC',
      p_owner                         => 'SYSMAN',
      p_category_nlsid                => 'CONFIG_CAT',
      p_sub_category_nlsid            => 'CLIENT_CONFIG_SUBCAT',
      p_late_binding_target_types     =>  null,
      p_late_binding_multi_targets    =>  0,
      p_show_table_of_contents        =>  0,
      p_system_report                 =>  1
        );
 
 -- Chart 1: Browser
 
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(4);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.height', '160');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.width', '160');

    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels',
            'true');

    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT browser as LBL_BROWSER , count(distinct snapshot_id) as LBL_SNAPSHOTID 
        FROM MGMT$CSA_COLLECTIONS
        GROUP BY browser');
      
    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       => 'BROWSER',
      p_element_order       => 1,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );
    
    commit;

-- chart 2: 
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(4);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.height', '160');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.width', '160');

    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels',
            'true');

    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT OPERATINGSYSTEM as LBL_OS,count(distinct snapshot_id) as LBL_SNAPSHOTID
        FROM MGMT$CSA_COLLECTIONS
        GROUP BY OPERATINGSYSTEM');
      
    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       => 'OS',
      p_element_order       => 2,
      p_element_row         => 1,
      p_parameters          => l_param_values,
      p_targets             => null
        );
    
    commit;

-- chart 3:  Memory
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(6);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 
                                   'oracle.sysman.eml.ecm.csa.CSAResourceBundle');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 
                                   'true');

    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.height', '160');

    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.width', '160');

    l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels',
            'true');
    
    l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'select mem_size_range,count(distinct id)
        from
      (
        select snapshot_id id, 
         ( CASE WHEN MEMORY_SIZE_IN_MB < 512 THEN ''RAM_RANGE_0''
               WHEN MEMORY_SIZE_IN_MB < 1024 THEN ''RAM_RANGE_1''
               WHEN MEMORY_SIZE_IN_MB < 2048 THEN ''RAM_RANGE_2''
               WHEN MEMORY_SIZE_IN_MB < 2560 THEN ''RAM_RANGE_3''
               WHEN MEMORY_SIZE_IN_MB < 2560 THEN ''RAM_RANGE_4''
	       ELSE ''RAM_RANGE_5''
          END) as mem_size_range
      FROM MGMT$CSA_COLLECTIONS
      ) csacoll
      GROUP BY csacoll.mem_size_range');
      
    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       => 'MEMORY',
      p_element_order       => 3,
      p_element_row         => 2,
      p_parameters          => l_param_values,
      p_targets             => null
        );
    
    commit;
    
-- chart 4:   CPU

    l_param_values := MGMT_IP_PARAM_VALUE_LIST();


    l_param_values.extend(4);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.height', '160');
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.ChartParamController.width', '160');

    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels',
            'true');

    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT  CPU as LBL_CPU,count(distinct snapshot_id) as LBL_SNAPSHOTID
        FROM MGMT$CSA_COLLECTIONS
        GROUP BY CPU');
      
    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       => 'CPU',
      p_element_order       => 4,
      p_element_row         => 2,
      p_parameters          => l_param_values,
      p_targets             => null
        );
    
    commit;   
    
 --table : Config details
    l_param_values := MGMT_IP_PARAM_VALUE_LIST();

    l_param_values.extend(4);

    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.sqlStatement',
      'SELECT 
          APPID AS CSA_APPID ,
          CSACLIENT AS LBL_CSACLIENT , 
          browser AS LBL_BROWSER,
          OPERATINGSYSTEM AS  LBL_OS,
          decode(CONNECTION_TYPE, ''1'', ''OP_DIALUP'', ''2'', ''OP_CABLE'', ''3'', ''OP_LAN'') AS LBL_CONNECTIONTYPE, 
          CPU AS LBL_CPU, 
          MEMORY_SIZE_IN_MB AS LBL_TOTALMEMORY 
        FROM MGMT$CSA_COLLECTIONS');

    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.eml.ecm.csa.CSAResourceBundle');
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD('oracle.sysman.eml.ip.render.elem.TableRender.tableSplitColumn', '0');

    l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TableRender.translateColumnValueColumns',   '4');

    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       => 'COLLTAG',
      p_element_order       => 5,
      p_element_row         => 3,
      p_parameters          => l_param_values,
      p_targets             => null
        );

    COMMIT;  
    
  END;

END;

/

SET DEFINE ON