CA eHealth Oracle Queries for Systemedge Agents

In our site we wanted to export some CA eHealth data daily to use in another system. These queries use direct oracle query to export data using the regularised tables in the database for systemedge agents.

Here are some ehealth sql queries that that use the aggregate hourly sample tables. e.g 

These queries have &1 and &2 as variables for epoc timestamp of the data but you can use hardcode values.

system edge agent host key stats - NHV_ST_H_SYSEDGEUNIXSYSTEM 

COLUMN AVGCPUUTILIZATION FORMAT 999.9;

COLUMN CPUSYSTEMUTILIZATION FORMAT 999.9;

COLUMN CPUUSERUTILIZATION FORMAT 999.9;

COLUMN CPUWAITUTILIZATION FORMAT 999.9;

COLUMN LOADAVERAGE FORMAT 9999.99;

COLUMN PHYSICALMEMORYUTILIZATION FORMAT 999.9;

COLUMN TOTALCPUUTILIZATION FORMAT 9999.9;

COLUMN VIRTUALMEMORYUTILIZATION FORMAT 999.9;

COLUMN PAGESCANRATE FORMAT 999999999;

COLUMN PROCESSES FORMAT 99999;

COLUMN RUNQUEUELENGTH FORMAT 99999;

COLUMN ACTIVEVIRTMEMORY_MB FORMAT 999999999;

COLUMN PHYSICALMEMORYFREE_MB FORMAT 999999999;

COLUMN PHYSICALMEMORYUSED_MB FORMAT 9999999999;

COLUMN TOTALBYTES_MB FORMAT 999999999;

COLUMN TOTALPHYSICALMEMORY_MB FORMAT 999999999;

COLUMN TOTALVIRTUALMEMORY_MB FORMAT 999999999;

COLUMN VIRTUALMEMORYFREE_MB FORMAT 999999999;

COLUMN VIRTUALMEMORYUSED_MB FORMAT 999999999;

COLUMN CPUIMBALANCE FORMAT 999999999.9;

COLUMN NUMSWITCHES FORMAT 999999999;

Select el.NAME, data.* from

(select distinct ELEMENT_ID, replace(NAME,'-1691-SH','') AS NAME

from NH_ELEMENT

where ELEMENT_TYPE = 104108) el, 

(select ELEMENT_ID,SAMPLE_TIMESTAMP,ACTIVEVIRTMEMORY/1048576 AS ACTIVEVIRTMEMORY_MB,AVGCPUUTILIZATION,CPUIMBALANCE,CPUSYSTEMUTILIZATION,

CPUUSERUTILIZATION,CPUWAITUTILIZATION,LOADAVERAGE,NUMSWITCHES,PAGESCANRATE,

PHYSICALMEMORYFREE/1048576 AS PHYSICALMEMORYFREE_MB,PHYSICALMEMORYUSED/1048576 AS PHYSICALMEMORYUSED_MB,PHYSICALMEMORYUTILIZATION,PROCESSES,

RUNQUEUELENGTH,TOTALBYTES/1048576 AS TOTALBYTES_MB,TOTALCPUUTILIZATION,TOTALPHYSICALMEMORY/1048576 AS TOTALPHYSICALMEMORY_MB,

TOTALVIRTUALMEMORY/1048576 AS TOTALVIRTUALMEMORY_MB,

VIRTUALMEMORYFREE/1048576 AS VIRTUALMEMORYFREE_MB,VIRTUALMEMORYUSED/1048576 AS VIRTUALMEMORYUSED_MB,VIRTUALMEMORYUTILIZATION

from NHV_ST_H_SYSEDGEUNIXSYSTEM 

--where SAMPLE_TIME > 1333050481 and SAMPLE_TIME < 1333057681) data

where SAMPLE_TIME > '&1' and SAMPLE_TIME < '&2') data

where 

el.ELEMENT_ID = data.ELEMENT_ID;

Disk partition for Systemedge - NHV_ST_H_DISKPARTITION

COLUMN INODEUTILIZATION FORMAT 999.9;

COLUMN PARTITIONUTILIZATION FORMAT 999.9;

COLUMN PCTPARTITIONFREE FORMAT 999.9;

COLUMN PARTITIONSTORAGECAPACITY_MB FORMAT 999999999;

COLUMN PARTITIONSTORAGEFREE_MB FORMAT 999999999;

COLUMN PARTITIONSTORAGEUSED_MB FORMAT 999999999;

Select el.NAME, data.* from

(select distinct ELEMENT_ID, replace(NAME,'-1691-SH','') AS NAME

from NH_ELEMENT

where ELEMENT_TYPE = 104045 or ELEMENT_TYPE =104039) el, 

(SELECT SAMPLE_TIME,ELEMENT_ID,SAMPLE_TIMESTAMP,INODEUTILIZATION,

PARTITIONSTORAGECAPACITY/1048576 AS PARTITIONSTORAGECAPACITY_MB,

PARTITIONSTORAGEFREE/1048576 AS PARTITIONSTORAGEFREE_MB,

PARTITIONSTORAGEUSED/1048576 AS PARTITIONSTORAGEUSED_MB,

PARTITIONUTILIZATION,PCTPARTITIONFREE

From NHV_ST_H_DISKPARTITION

where -- ELEMENT_ID>=1000013 and ELEMENT_ID <=1000020

-- SAMPLE_TIME > 1333050481 and SAMPLE_TIME < 1333057681

SAMPLE_TIME > '&1' and SAMPLE_TIME < '&2') data

where 

el.ELEMENT_ID = data.ELEMENT_ID;