@@header

/*
*
*  Author  : Vishal Gupta
*  Purpose : Display Exadata Cell Status
*  Parameters : None
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  20-Feb-13  Vishal Gupta  Created
*
*/



PROMPT 
PROMPT **************************************************
PROMPT *  E X A D A T A   C E L L   S T A T U S
PROMPT **************************************************


COLUMN cell_path                   HEADING "CellPath"                 FORMAT a15
COLUMN cellname                    HEADING "CellName"                 FORMAT a18
COLUMN upTime                      HEADING "UpTime"                   FORMAT a15
COLUMN status                      HEADING "Status"                   FORMAT a8
COLUMN fanStatus                   HEADING "Fan|Status"               FORMAT a6
COLUMN powerStatus                 HEADING "Power|Status"             FORMAT a6
COLUMN temperatureReading          HEADING "Temp|(C)"                 FORMAT a6
COLUMN temperatureStatus           HEADING "Temp|Status"              FORMAT a6
COLUMN locatorLEDStatus            HEADING "Locator|LED"              FORMAT a7
COLUMN offloadEfficiency           HEADING "OffLoad|Efficiency"       FORMAT 99999.99
COLUMN iormBoost                   HEADING "IORM|Boost"               FORMAT 99999.99

/*
SELECT MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/name') ,NULL))                cellname
     --, cc.cellname cell_path
     , LPAD(MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/upTime') ,NULL)),15)     upTime
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/status') ,NULL))              status
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/fanStatus') ,NULL))           fanStatus
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/powerStatus') ,NULL))         powerStatus
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/temperatureStatus') ,NULL))   temperatureStatus
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/temperatureReading') ,NULL))  temperatureReading
     , MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/locatorLEDStatus') ,NULL))    locatorLEDStatus
     , ROUND(MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/offloadEfficiency') ,NULL)),2)   offloadEfficiency
     , ROUND(MAX(DECODE(cc.conftype,'CELL',EXTRACTVALUE(XMLTYPE(confval),'/cli-output/cell/iormBoost') ,NULL)),2)  iormBoost
 
FROM v$cell_config cc
WHERE cc.conftype = 'CELL'
GROUP BY cc.cellname
ORDER BY cellname
;

*/

SELECT cell.name cellname
     , cell.upTime
     , cell.status
     , cell.fanStatus
     , cell.powerStatus
     , cell.temperatureStatus
     , cell.temperatureReading
     , cell.locatorLEDStatus
     , cell.offloadEfficiency
     , cell.iormBoost
 FROM v$cell_config cc
    , XMLTable('/cli-output/cell' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        name                 VARCHAR2(100) PATH 'name'
                      , upTime               VARCHAR2(100) PATH 'upTime'
                      , status               VARCHAR2(100) PATH 'status'
                      , fanStatus            VARCHAR2(100) PATH 'fanStatus'
                      , powerStatus          VARCHAR2(100) PATH 'powerStatus'
                      , temperatureStatus    VARCHAR2(100) PATH 'temperatureStatus'
                      , temperatureReading   VARCHAR2(100) PATH 'temperatureReading'
                      , locatorLEDStatus     VARCHAR2(100) PATH 'locatorLEDStatus'
                      , offloadEfficiency    NUMBER        PATH 'offloadEfficiency'
                      , iormBoost            NUMBER        PATH 'iormBoost'
                 ) cell
WHERE cc.conftype = 'CELL'
ORDER BY cellname
;


@@footer