[dev.icinga.com #4630] SQL-Statement in Sub-Template "Contactgroups" doesn't work

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/4630

Created by jschanz on 2013-08-23 10:08:06 +00:00

Assignee: (none)
Status: New
Target Version: Backlog
Last Update: 2015-05-18 12:17:48 +00:00 (in Redmine)

Icinga Version: 1.9.2
DB Type: MySQL
DB Version: 1.9.0
Jasper Version: 4.7.0

---

SQL-Statement from reporting package 1.9 (contactgroups.jrxml)

select c.alias,
  a.name1,
  b.alias,
  b.email_address
from icinga_objects a,
  icinga_contacts b,
  icinga_contactgroups c,
  icinga_contactgroup_members d,
  icinga_host_contactgroups e,
  icinga_hosts f
where a.object_id            = b.contact_object_id
and b.contact_object_id      = d.contact_object_id
and d.contactgroup_id        = c.contactgroup_id
and c.contactgroup_object_id = e.contactgroup_object_id
and e.host_id                = f.host_id
and f.host_object_id         = $P{p_host_object_id}

Join on icinga_objects and icinga_contacts doesn't work.

Fixed statement is here

select c.alias, a.display_name, e.alias, e.email_address
   from
       icinga_hosts a,
       icinga_host_contactgroups b,
       icinga_contactgroups c,
       icinga_contactgroup_members d,
       icinga_contacts e
    where a.host_id = b.host_id
      and b.contactgroup_object_id = c.contactgroup_object_id
      and c.contactgroup_id = d.contactgroup_id
      and d.contact_object_id = e.contact_object_id
      and a.host_object_id = $P{p_host_object_id}

Attachments

• contactgroups.jrxml jschanz - 2013-08-23 10:07:55 +00:00

[icinga-migration]

Updated by mfriedrich on 2013-08-23 13:33:17 +00:00

works for me using the test configuration from https://git.icinga.org/?p=icinga-core.git;a=tree;f=tests/etc;hb=refs/heads/next

any details on the expected output?

[icinga-migration]

Updated by jschanz on 2013-08-26 05:58:50 +00:00

The contact groups sub report should deliver the contact groups for a host. If no contact, but a contactgroup, is definied for a host, the join between a.object_id and b.contact_object_id doesn't work.
So it's better to do a join with the tables hosts and host_contactgroups.

[icinga-migration]

Updated by berk on 2015-05-18 12:17:48 +00:00

• Target Version set to Backlog