Using this text file to store useful SQLs to get status information
for hierarchy groups.

Contacts:

select name1,name2 from ndo_objects where objecttype_id=10;



Given hostgroup, tips from here onwards:

$hostgroup->leaves



List of all hosts in a hostgroup:

select hgm.host_object_id from ndo_hostgroup_members hgm, ndo_hostgroups hg, ndo_objects o
where o.name1="$hostgroup_name" and o.objecttype_id=3
and o.object_id = hg.hostgroup_object_id
and hg.hostgroup_id = hgm.hostgroup_id;



Status of all hosts in a hostgroup:

select hgm.host_object_id, hs.current_state from ndo_hostgroup_members hgm, ndo_hostgroups hg, ndo_objects o
where o.name1="$hostgroup_name" and o.objecttype_id=3
and o.object_id = hg.hostgroup_object_id
and hg.hostgroup_id = hgm.hostgroup_id
and hgm.host_object_id = hs.host_object_id



All hosts in a hostgroup, by status and unhandled:

select hs.current_state , (hs.current_state!=0 and hs.problem_has_been_acknowledged!=1 and hs.scheduled_downtime_depth=0) as unhandled
from ndo_hostgroup_members hgm, ndo_hostgroups hg, ndo_objects o, ndo_hoststatus hs 
where o.name1="Endeavour" 
and o.objecttype_id=3 
and o.object_id = hg.hostgroup_object_id 
and hg.hostgroup_id = hgm.hostgroup_id 
and hgm.host_object_id = hs.host_object_id 


All services belonging to a contact, with 'unhandledness'. Mimics Service Status screen:

select o2.name1 as host, hs.current_state as host_state, 
 (hs.current_state != 0 and hs.problem_has_been_acknowledged != 1 and hs.scheduled_downtime_depth = 0) as unhandled,
 o2.name2 as service, ss.current_state as state, ss.output,
 (ss.current_state != 0 and hs.current_state = 0 and ss.problem_has_been_acknowledged!=1 and hs.scheduled_downtime_depth=0) as unhandled
from ndo_objects o, ndo_objects o2, ndo_objects o3,
 ndo_contactgroup_members cgm, ndo_contactgroups cg, 
 ndo_service_contactgroups scg, ndo_services s, ndo_servicestatus ss, ndo_hoststatus hs
where o.name1='admin'
and o.objecttype_id=10
and o.object_id = cgm.contact_object_id
and cgm.contactgroup_id = cg.contactgroup_id
and cg.config_type = 1
and cg.contactgroup_object_id = scg.contactgroup_object_id
and scg.service_id = s.service_id
and s.service_object_id = ss.service_object_id
and s.config_type = 1
and o2.object_id = s.service_object_id
and o2.name1 = o3.name1
and o3.objecttype_id = 1
and o3.object_id = hs.host_object_id
order by o2.name1, o2.name2




As above, but restricted by hostgroup:

select o2.name1 as host, hs.current_state as host_state, 
 (hs.current_state != 0 and hs.problem_has_been_acknowledged != 1 and hs.scheduled_downtime_depth = 0) as unhandled,
 o2.name2 as service, ss.current_state as state, ss.output,
 (ss.current_state != 0 and hs.current_state = 0 and ss.problem_has_been_acknowledged!=1 and hs.scheduled_downtime_depth=0) as unhandled
from ndo_objects o, ndo_objects o2, ndo_objects o3, ndo_objects o4,
 ndo_contactgroup_members cgm, ndo_contactgroups cg, 
 ndo_service_contactgroups scg, ndo_services s, ndo_servicestatus ss, ndo_hoststatus hs,
 ndo_hostgroup_members hgm, ndo_hostgroups hg
where o.name1='admin'
and o.objecttype_id=10
and o.object_id = cgm.contact_object_id
and cgm.contactgroup_id = cg.contactgroup_id
and cg.config_type = 1
and cg.contactgroup_object_id = scg.contactgroup_object_id
and scg.service_id = s.service_id
and s.service_object_id = ss.service_object_id
and s.config_type = 1
and o2.object_id = s.service_object_id
and o2.name1 = o3.name1
and o3.objecttype_id = 1
and o3.object_id = hs.host_object_id
and hs.host_object_id = hgm.host_object_id
and hg.hostgroup_id = hgm.hostgroup_id 
and o4.name1 in ("Network Infrastructure" , "Endeavour")
and o4.objecttype_id=3
and o4.object_id = hg.hostgroup_object_id
order by o2.name1, o2.name2



Host status summarised for a list of hostgroups, grouped by status and unhandled
(no need to refer to contactgroup - hosts in hostgroup is sufficient):

select hs.current_state as host_status, 
 (hs.current_state != 0 and hs.problem_has_been_acknowledged != 1 and hs.scheduled_downtime_depth = 0) as unhandled,
 count(hs.current_state) as total
from ndo_objects o3, ndo_objects o4,
 ndo_hoststatus hs,
 ndo_hostgroup_members hgm, ndo_hostgroups hg
where o3.objecttype_id = 1
and o3.object_id = hs.host_object_id
and hs.host_object_id = hgm.host_object_id
and hg.hostgroup_id = hgm.hostgroup_id
and o4.name1 in ("Network Infrastructure" , "London", "Endeavour")
and o4.objecttype_id=3
and o4.object_id = hg.hostgroup_object_id
group by host_status, unhandled

Returns something like:
+-------------+-----------+-------------+
| host_status | unhandled | total       |
+-------------+-----------+-------------+
|           0 |         0 |           1 |
|           1 |         0 |           1 |
|           1 |         1 |           1 |
+-------------+-----------+-------------+





All services in list of hostgroups for a contact, by status and unhandled:

select 
 ss.current_state as state,
 (ss.current_state != 0 and hs.current_state = 0 and ss.problem_has_been_acknowledged!=1 and hs.scheduled_downtime_depth=0) as unhandled,
 count(*) as total
from ndo_objects o, ndo_objects o2, ndo_objects o3, ndo_objects o4,
 ndo_contactgroup_members cgm, ndo_contactgroups cg, 
 ndo_service_contactgroups scg, ndo_services s, ndo_servicestatus ss, ndo_hoststatus hs,
 ndo_hostgroup_members hgm, ndo_hostgroups hg
where o.name1='admin'
and o.objecttype_id=10
and o.object_id = cgm.contact_object_id
and cgm.contactgroup_id = cg.contactgroup_id
and cg.config_type = 1
and cg.contactgroup_object_id = scg.contactgroup_object_id
and scg.service_id = s.service_id
and s.service_object_id = ss.service_object_id
and s.config_type = 1
and o2.object_id = s.service_object_id
and o2.name1 = o3.name1
and o3.objecttype_id = 1
and o3.object_id = hs.host_object_id
and hs.host_object_id = hgm.host_object_id
and hg.hostgroup_id = hgm.hostgroup_id 
and o4.name1 in ("Network Infrastructure" , "Endeavour")
and o4.objecttype_id=3
and o4.object_id = hg.hostgroup_object_id
group by state, unhandled;

Returns something like:
+-------+-----------+-------+
| state | unhandled | total |
+-------+-----------+-------+
|     0 |         0 |     3 |
|     2 |         0 |     1 |
|     2 |         1 |     1 |
+-------+-----------+-------+