Ndoutils数据库浅谈（一）

时间：2010-10-19 来源：jayamge

Ndoutils有一张很有特点的nagios_objects表，它把所有的nagios元素都当做一个对象存储在表中，包括hostgroup、service等等，通过objecttype_id加以区分，比如objecttype_id为11的字段，表示它是一个联系人组对象。这张表有个不好的地方，比如我想通过nagios_services表的service_id，查到这个service的联系人组里的所有联系人，我得走两步：从nagios_objects表一进一出。
根据service_id取得联系人组object_id

select no.object_id
from
nagios_objects no,
nagios_services ns,
nagios_service_contactgroups nscg
where
ns.service_id=53865295
and nscg.service_id= ns.service_id
and no.object_id=nscg.contactgroup_object_id
and no.objecttype_id=11
and no.name1 not like "monitor_admin%";

再根据联系人组object_id取得联系人名称

select nc.alias
from nagios_objects no,
nagios_contactgroup_members ncm,
nagios_contactgroups ncg,
nagios_contacts nc
where
ncg.contactgroup_object_id=82411
and ncm.contactgroup_id=ncg.contactgroup_id
and no.object_id=ncm.contact_object_id
and no.objecttype_id =10
and nc.contact_object_id = no.object_id

一进一出，真的绕了很多弯路，而在centreon的数据库中，则对object表进行了拆分，比如一个与上述语句功能类似的查询：

select d.contact_alias
from
host a,
contactgroup_host_relation b,
contactgroup_contact_relation c,
contact d ,
contactgroup e
where
a.host_name = 'cache161.cn22'
and b.host_host_id = a.host_id
and c.contactgroup_cg_id = b.contactgroup_cg_id
and c.contactgroup_cg_id = e.cg_id
and d.contact_id = c.contact_contact_id
and e.cg_alias not like "monitor_admin%"
and d.contact_alias not like '%2'
and d.contact_alias not like '%3' group by contact_alias