@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Exadata Cell Configuration
*  Parameters : None
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------------
*  05-Mar-14  Vishal Gupta  Added server mode to exadata model translation
*  20-Feb-13  Vishal Gupta  Created
*
*/


PROMPT 
PROMPT **************************************************
PROMPT *  E X A D A T A   C E L L    C O N F I G
PROMPT **************************************************


COLUMN cell_path                   HEADING "CellPath"                 FORMAT a15
COLUMN cellname                    HEADING "CellName"                 FORMAT a18
COLUMN exa_model                   HEADING "Exa|Model"                FORMAT a5
COLUMN makeModel                   HEADING "MakeModel"                FORMAT a30
COLUMN upTime                      HEADING "UpTime"                   FORMAT a15
COLUMN cellVersion                 HEADING "CellVersion"              FORMAT a35
COLUMN kernelVersion               HEADING "KernelVersion"            FORMAT a35
COLUMN releaseVersion              HEADING "Release|Version"          FORMAT a20
COLUMN releaseTrackingBug          HEADING "Release|Tracking|Bug"     FORMAT a20
COLUMN flashCacheMode              HEADING "FlashCache|Mode"          FORMAT a12
COLUMN flashCacheCompress          HEADING "FlashCache|Compression"   FORMAT a12


SELECT cell.name cellname
     , CASE 
         WHEN cell.makeModel LIKE '%X4275%'    THEN 'V2'
         WHEN cell.makeModel LIKE '%X4270 M2%' THEN 'X2-2'
         WHEN cell.makeModel LIKE '%X4270 M3%' THEN 'X3-2'
         WHEN cell.makeModel LIKE '%X4270 M4%' THEN 'X4-2'
         WHEN cell.makeModel LIKE '%X5-2%'     THEN 'X5-2'
         ELSE ''
       END exa_model  
     , REPLACE(REPLACE(REPLACE(REPLACE(cell.makeModel
               ,'Oracle Corporation SUN FIRE ')
               ,'SUN MICROSYSTEMS SUN FIRE ')
               ,'Oracle Corporation ') 
               ,'ORACLE SERVER ')               makeModel
     , cell.cellVersion
     , cell.kernelVersion
     , cell.releaseVersion
     , cell.releaseTrackingBug
     , cell.flashCacheMode
 FROM v$cell_config cc
    , XMLTable('/cli-output/cell' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        name                VARCHAR2(100) PATH 'name'
                      , makeModel           VARCHAR2(100) PATH 'makeModel'
                      , cellVersion         VARCHAR2(100) PATH 'cellVersion'
                      , kernelVersion       VARCHAR2(100) PATH 'kernelVersion'
                      , releaseVersion      VARCHAR2(100) PATH 'releaseVersion'
                      , releaseTrackingBug  VARCHAR2(100) PATH 'releaseTrackingBug'
                      , flashCacheMode      VARCHAR2(100) PATH 'flashCacheMode'
                      , flashCacheCompress  VARCHAR2(100) PATH 'flashCacheCompress'
                 ) cell
WHERE cc.conftype = 'CELL'
ORDER BY cellname
;


--------------------------------
-- IORM Details 
--------------------------------
COLUMN name          HEADING "IORM Name"      FORMAT a25
COLUMN catPlan       HEADING "Cat Plan"       FORMAT a15
COLUMN dbPlan        HEADING "dbPlan"         FORMAT a15
COLUMN objective     HEADING "Objective"      FORMAT a10
COLUMN status        HEADING "IORM Status"    FORMAT a12

SELECT clioutput.cellname
     , iorm.name
     , iorm.status
     , iorm.catPlan
     , iorm.dbPlan
     , iorm.objective
 FROM  v$cell_config cc
     , XMLTable('/cli-output' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        cellname            VARCHAR2(100) PATH 'context/@cell'
                      , interdatabaseplan   XMLTYPE       PATH 'interdatabaseplan'
                 ) clioutput
     , XMLTable('/interdatabaseplan' PASSING clioutput.interdatabaseplan
                   COLUMNS 
                        name            VARCHAR2(100) PATH 'name'
                      , catPlan         VARCHAR2(100) PATH 'catPlan'
                      , dbPlan          VARCHAR2(100) PATH 'dbPlan'
                      , objective       VARCHAR2(100) PATH 'objective'
                      , status          VARCHAR2(100) PATH 'status'
                 ) iorm
WHERE cc.conftype = 'IORM'
ORDER BY clioutput.cellname
;


--------------------------------
-- Hardware Details
--------------------------------
COLUMN id                          HEADING "System Id"                FORMAT a12
COLUMN cpuCount                    HEADING "CPU|(#)"                  FORMAT a5
COLUMN memoryGB                    HEADING "Mem(GB)"                  FORMAT a3
COLUMN fanCount                    HEADING "Fan|(#)"                  FORMAT a5
COLUMN powerCount                  HEADING "Power|Supply|(#)"         FORMAT a6
COLUMN bbuTempThreshold            HEADING "Battery|Temp|Thresh"      FORMAT a7
COLUMN bbuChargeThreshold          HEADING "Battery|Charge|Thresh"    FORMAT a7
COLUMN bmcType                     HEADING "Battery|Type"             FORMAT a5
COLUMN interconnectCount           HEADING "Inter|Connect|(#)"        FORMAT a5
COLUMN interconnect1               HEADING "Inter|Connect1"           FORMAT a8
COLUMN ipaddress1                  HEADING "IPAddress1"               FORMAT a17
COLUMN interconnect2               HEADING "Inter|Connect2"           FORMAT a8
COLUMN ipaddress2                  HEADING "IPAddress2"               FORMAT a17
COLUMN diagHistoryDays             HEADING "Diag|History|Days"        FORMAT a7
COLUMN metricHistoryDays           HEADING "Metric|History|Days"      FORMAT a7
COLUMN notificationMethod          HEADING "Notif|Method"             FORMAT a10
COLUMN notificationPolicy          HEADING "Notif|Policy"             FORMAT a30



SELECT cell.name cellname
     , cell.id
     , cell.cpuCount
     , cell.memoryGB
     , cell.fanCount
     , cell.powerCount
     , cell.bbuTempThreshold
     , cell.bbuChargeThreshold
     , cell.bmcType
     , cell.interconnectCount
     , cell.interconnect1
     , cell.ipaddress1
     , cell.interconnect2
     , cell.ipaddress2
     , cell.diagHistoryDays
     , cell.metricHistoryDays
 FROM v$cell_config cc
    , XMLTable('/cli-output/cell' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        name                VARCHAR2(100) PATH 'name'
                      , id                  VARCHAR2(100) PATH 'id'
                      , cpuCount            VARCHAR2(100) PATH 'cpuCount'
                      , memoryGB            VARCHAR2(100) PATH 'memoryGB'
                      , fanCount            VARCHAR2(100) PATH 'fanCount'
                      , powerCount          VARCHAR2(100) PATH 'powerCount'
                      , bbuTempThreshold    VARCHAR2(100) PATH 'bbuTempThreshold'
                      , bbuChargeThreshold  VARCHAR2(100) PATH 'bbuChargeThreshold'
                      , bmcType             VARCHAR2(100) PATH 'bmcType'
                      , interconnectCount   VARCHAR2(100) PATH 'interconnectCount'
                      , interconnect1       VARCHAR2(100) PATH 'interconnect1'
                      , ipaddress1          VARCHAR2(100) PATH 'ipaddress1'
                      , interconnect2       VARCHAR2(100) PATH 'interconnect2'
                      , ipaddress2          VARCHAR2(100) PATH 'ipaddress2'
                      , diagHistoryDays     VARCHAR2(100) PATH 'diagHistoryDays'
                      , metricHistoryDays   VARCHAR2(100) PATH 'metricHistoryDays'
                 ) cell
WHERE cc.conftype = 'CELL'
ORDER BY cellname
;

--------------------------------
-- Alert Notification Config
--------------------------------
COLUMN smtpUseSSL      HEADING "smtpUseSSL"         FORMAT a10
COLUMN smtpServer      HEADING "smtpServer"         FORMAT a10
COLUMN smtpPort        HEADING "SMTP|Port"          FORMAT a5
COLUMN smtpFrom        HEADING "SMTP From"          FORMAT a25
COLUMN smtpFromAddr    HEADING "smtpFromAddr"       FORMAT a35


SELECT cell.name cellname
     , cell.smtpUseSSL
     , cell.smtpServer
     , cell.smtpPort
     , cell.notificationMethod
     , cell.notificationPolicy
     , cell.smtpFrom
     , cell.smtpFromAddr
 FROM v$cell_config cc
    , XMLTable('/cli-output/cell' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        name                VARCHAR2(100) PATH 'name'
                      , notificationMethod  VARCHAR2(100) PATH 'notificationMethod'
                      , notificationPolicy  VARCHAR2(100) PATH 'notificationPolicy'
                      , smtpFrom            VARCHAR2(100) PATH 'smtpFrom'
                      , smtpFromAddr        VARCHAR2(100) PATH 'smtpFromAddr'
                      , smtpPort            VARCHAR2(100) PATH 'smtpPort'
                      , smtpServer          VARCHAR2(100) PATH 'smtpServer'
                      , smtpUseSSL          VARCHAR2(100) PATH 'smtpUseSSL'
                 ) cell
WHERE cc.conftype = 'CELL'
ORDER BY cellname
;


--------------------------------
-- Alert Notification Receiver
--------------------------------
COLUMN smtpToAddr      HEADING "smtpToAddr"         FORMAT a50
COLUMN snmpSubscriber  HEADING "snmpSubscriber"     FORMAT a90

SELECT cell.name cellname
     , REPLACE(cell.smtpToAddr,',',     ',' || chr(10))      smtpToAddr
     , snmpSubscriber                                        snmpSubscriber
 FROM v$cell_config cc
    , XMLTable('/cli-output/cell' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        name                VARCHAR2(100) PATH 'name'
                      , smtpToAddr          VARCHAR2(500) PATH 'smtpToAddr'
                      , snmpSubscriber      VARCHAR2(500) PATH 'snmpSubscriber'
                 ) cell
WHERE cc.conftype = 'CELL'
ORDER BY cellname
;


@@footer