e.heinemann

CA eHealth SQL-Queries .. very simple ones

Blog Post created by e.heinemann on Oct 22, 2015

After enabling the ehealth DBApi it is a good way to get out some data directly via SQL

 

Years ago I had prepared some SQL-trainings for customers .. specially for ehealth.

 

For all, who would get out some data via the SQL-API, I could provide these old SQL-Examples. .. Without any garantie!

If You use these scripts in Your environment, - it is your own risk!

 

I have not tested these queries with the current eHealth-version!

 

Polled Elements by Model:

select

count (*) Anzahl

, t.label

from nh_element e

, nh_element_type t

where t.element_type = e.element_type

and e.polling_enabled = 1

group by t.label

order by Anzahl desc;


Polled Elements by Model 2:

select

count(*) Anzahl

, t.label  Element_Type

, max(e.name) Beispiel_Element

 

from nh_element e

, nh_element_type t

where t.element_type = e.element_type

and e.polling_enabled = 1

group by t.label

 

order by count(*) desc

 

Unixtimestamp:

select nh_dbapi.nh_date_to_num (sysdate) from dual;

 

Get all the Elements and the specified View to get the Stats:

select e.element_id

      ,e.name

      ,e.alias

      ,e.element_type

      , e.formatted_ip_address

      , c.view_name

from nh_element e

  ,  nh_dbapi_catalog c

where e.ELEMENT_TYPE = c.TYPE_ID

order by e.alias;

 


Get all elements which are associated with more than one group:

select name

     , count(name)

from

(select

  avg  ( ((AVAILABLE_TIME * 100.0))/DECODE(total_time,0,1,total_time)) availability

,  e.element_id

, e.name

, e.element_type

, e.formatted_ip_address

, c.view_name

, c.description

, g.name groupname

, g.description  groupdescription

from

  nh_element e

, nhv_stats v

, nh_dbapi_catalog c

, nh_group g

, nh_group_members gm

where v.sample_time >  nh_dbapi.nh_date_to_num(sysdate -5)

and v.element_id      = e.element_id

and e.polling_enabled = 1

and c.type_id         = e.element_type

and gm.group_id       = g.group_id

and gm.element_id     = e.element_id

group by  e.element_id

, e.name

, e.element_type

, e.formatted_ip_address

, c.view_name

, c.description

, g.name

, g.description

) x

group by name

order by count(name) desc;

 

Get all Groups:

select g.name groupname

     , g.description  groupdescription

     , gm.element_id

from nh_group g

   , nh_group_members gm

  where gm.group_id = g.group_id;

 

Availability by Group:

select

  avg  ( ((AVAILABLE_TIME * 100.0))/DECODE(total_time,0,1,total_time)) availability

, g.name groupname

, g.description  groupdescription

from

  nh_element e

, nhv_stats v

, nh_dbapi_catalog c

, nh_group g

, nh_group_members gm

where v.sample_time >  nh_dbapi.nh_date_to_num(sysdate -5)

and v.element_id      = e.element_id

and e.polling_enabled = 1

and c.type_id         = e.element_type

and gm.group_id       = g.group_id

and gm.element_id     = e.element_id

group by

g.name

, g.description

order by avg (((AVAILABLE_TIME * 100.0))/DECODE(total_time,0,1,total_time)) ;

Outcomes