@@header

/*
*
*  Author  : Vishal Gupta
*  Purpose : Display Exadata PhysicalDisks
*  Parameters : 1 - Cell Name          (Use % for wildcard)
*               2 - DiskType           (FlashDisk or HardDisk,Use % for wildcard )
*               3 - PhysicalDisk Name  (Use % for wildcard)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  20-Feb-13  Vishal Gupta  Created
*
*/


/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE cell_name
UNDEFINE diskType
UNDEFINE name

DEFINE cell_name="&&1"  
DEFINE diskType="&&2"  
DEFINE name="&&3"  

set term off
COLUMN  _cell_name         NEW_VALUE  cell_name             NOPRINT
COLUMN  _diskType          NEW_VALUE  diskType              NOPRINT
COLUMN  _name              NEW_VALUE  name                  NOPRINT

SELECT DECODE('&&cell_name','','%','&&cell_name')     "_cell_name"
     , DECODE('&&diskType','','%','&&diskType')       "_diskType"
     , DECODE('&&name','','%','&&name')               "_name"
FROM DUAL;
set term on

PROMPT 
PROMPT ****************************************************
PROMPT *  E X A D A T A    P H Y S I C A L    D I S K S
PROMPT *
PROMPT *  Input Parameters
PROMPT *      CellName       = '&&cell_name'
PROMPT *      DiskType       = '&&diskType'
PROMPT *      PhyDiskName    = '&&name'
PROMPT ****************************************************

COLUMN cellname                HEADING "CellName"                         FORMAT a18 
COLUMN diskType                HEADING "Disk|Type"                        FORMAT a10
COLUMN name                    HEADING "PhyDisk|Name"                     FORMAT a10
COLUMN status                  HEADING "Status"                           FORMAT a8
COLUMN physicalSize            HEADING "Size|(GB)"                        FORMAT 999,999
COLUMN foreignState            HEADING "Foreign|State"                    FORMAT a7
COLUMN physicalFirmware        HEADING "Firm|Ware"                        FORMAT a8
COLUMN physicalInsertTime      HEADING "InsertTime"                       FORMAT a19 TRUNC
COLUMN physicalInterface       HEADING "Inter|face"                       FORMAT a5
COLUMN physicalSerial          HEADING "Serial No"                        FORMAT a18
COLUMN slotNumber              HEADING "slotNumber"                       FORMAT a10
COLUMN id                      HEADING "id"                               FORMAT a10
COLUMN key_500                 HEADING "key_500"                          FORMAT a10
COLUMN predfailStatus          HEADING "Pred|Fail|Status"                 FORMAT a6
COLUMN poorPerfStatus          HEADING "Poor|Perf|Status"                 FORMAT a6
COLUMN wtCachingStatus         HEADING "Write|Through|Caching|Status"     FORMAT a7
COLUMN peerFailStatus          HEADING "Peer|Fail|Status"                 FORMAT a6
COLUMN criticalStatus          HEADING "Critical|Status"                  FORMAT a6
COLUMN physicalDatedSerial     HEADING "Physical|Dated|Serial"            FORMAT a12
COLUMN errMediaCount           HEADING "Media|Error|Count"                FORMAT 9999999
COLUMN errorCount              HEADING "|Error|Count"                     FORMAT 9999999
COLUMN errHardReadCount        HEADING "HardRead|Error|Count"             FORMAT 9999999
COLUMN errHardWriteCount       HEADING "HardWrite|Error|Count"            FORMAT 9999999
COLUMN errorCount              HEADING "Error|Count"                      FORMAT 9999999
COLUMN errOtherCount           HEADING "Other|Error|Count"                FORMAT 9999999
 
BREAK ON REPORT
COMPUTE SUM LABEL 'Total' OF physicalSize FORMAT 99,999,999,999  ON REPORT 
 
SELECT clioutput.cellname
     , pd.diskType
     , pd.name
     , pd.status
     , ROUND(pd.physicalSize/power(1000,3)) physicalSize
     --, pd.deviceId
     --, pd.devicePartition
     --, pd.enclosureDeviceId
     , pd.foreignState
     --, pd.luns
     --, pd.makeModel
     , pd.physicalFirmware
     , pd.physicalInsertTime
     , pd.physicalInterface
     , pd.physicalSerial
     --, pd.slotNumber
     --, pd.id
     --, pd.key_500
     , pd.predfailStatus
     , pd.poorPerfStatus
     , pd.wtCachingStatus
     , pd.peerFailStatus
     , pd.criticalStatus
     , pd.physicalDatedSerial
     , pd.errCmdTimeoutCount
     , pd.errMediaCount
     , pd.errHardReadCount
     , pd.errHardWriteCount
     , pd.errorCount
     , pd.errOtherCount
 FROM  v$cell_config cc
     , XMLTable('/cli-output' PASSING XMLTYPE(confval) 
                   COLUMNS 
                        cellname       VARCHAR2(100) PATH 'context/@cell'
                      , physicaldisk   XMLTYPE       PATH 'physicaldisk'
                 ) clioutput
     , XMLTable('/physicaldisk' PASSING clioutput.physicaldisk
                   COLUMNS 
                        NAME                VARCHAR2(100) PATH 'name'
                      , deviceId            VARCHAR2(100) PATH 'deviceId'
                      , diskType            VARCHAR2(100) PATH 'diskType'
                      , devicePartition     VARCHAR2(100) PATH 'devicePartition'
                      , enclosureDeviceId   VARCHAR2(100) PATH 'enclosureDeviceId'
                      , errCmdTimeoutCount  INTEGER       PATH 'errCmdTimeoutCount'
                      , errHardReadCount    INTEGER       PATH 'errHardReadCount'
                      , errHardWriteCount   INTEGER       PATH 'errHardWriteCount'
                      , errMediaCount       INTEGER       PATH 'errMediaCount'
                      , errorCount          INTEGER       PATH 'errorCount'
                      , errOtherCount       INTEGER       PATH 'errOtherCount'
                      , foreignState        VARCHAR2(100) PATH 'foreignState'
                      , luns                VARCHAR2(100) PATH 'luns'
                      , makeModel           VARCHAR2(100) PATH 'makeModel'
                      , physicalFirmware    VARCHAR2(100) PATH 'physicalFirmware'
                      , physicalInsertTime  VARCHAR2(100) PATH 'physicalInsertTime'
                      , physicalInterface   VARCHAR2(100) PATH 'physicalInterface'
                      , physicalSerial      VARCHAR2(100) PATH 'physicalSerial'
                      , physicalSize        INTEGER       PATH 'physicalSize'
                      , slotNumber          VARCHAR2(100) PATH 'slotNumber'
                      , status              VARCHAR2(100) PATH 'status'
                      , id                  VARCHAR2(100) PATH 'id'
                      , key_500             VARCHAR2(100) PATH 'key_500'
                      , predfailStatus      VARCHAR2(100) PATH 'predfailStatus'
                      , poorPerfStatus      VARCHAR2(100) PATH 'poorPerfStatus'
                      , wtCachingStatus     VARCHAR2(100) PATH 'wtCachingStatus'
                      , peerFailStatus      VARCHAR2(100) PATH 'peerFailStatus'
                      , criticalStatus      VARCHAR2(100) PATH 'criticalStatus'
                      , physicalDatedSerial VARCHAR2(100) PATH 'physicalDatedSerial'
                 ) pd
WHERE cc.conftype = 'PHYSICALDISKS'
  AND UPPER(clioutput.cellname) LIKE UPPER('&&cell_name')
  AND UPPER(pd.diskType)        LIKE UPPER('&&diskType')
  AND UPPER(pd.name)            LIKE UPPER('&&name')
ORDER BY clioutput.cellname
       , pd.diskType
       , pd.slotNumber
;


@@footer