In a short way: Im looking for the correlation between a contact_group to a single service in database:
icinga_services.service_id = 12345
icinga_objects.object_id = 67890
icinga_servicestatus.servicestatus_id = 9876
I can read, thx. But dont need a description of every field, i need the Correlation between two fields like service and contactgroups. I cant find this on the DB Scheme. I try to describe: In web2 there are a cobtactgroup defined for every service on a host. But how is the correlation between the service and the contactgroup? Ive got an object-id for the service and another one for contact-group. But where is the correlation between both objects? There are special Apply-Rules for services to contact_groups but i dont think, that the correlation is made by this.
If you’ve already read it without getting the information you wanted, just say it. No need to get to a harsh/arrogant tone.
Anyway, what you want is probably this:
SELECT so.name1, so.name2, s.display_name, ic.alias FROM icinga_services s
INNER JOIN icinga_objects so on so.object_id = s.service_object_id AND so.is_active = 1 and so.objecttype_id = 2
LEFT JOIN icinga_service_contactgroups isc on s.service_id = isc.service_id
LEFT JOIN icinga_objects scgo on isc.contactgroup_object_id = scgo.object_id AND scgo.is_active = 1 AND scgo.objecttype_id = 10
LEFT JOIN icinga_contactgroups ic on isc.contactgroup_object_id = ic.contactgroup_object_id
WHERE ic.contactgroup_id IS NOT NULL
The IDO uses the icinga_objects table to establish relations to most other tables.