Symptom

When you try to connect to Centreon with an LDAP user, loading takes a long time (more than 10 seconds for example). This could be because there are a lot of contact groups in the database, which are checked during authentication. Let’s say that more than 1000 is too many.

mysql centreon -e "SELECT COUNT(*) FROM contactgroup WHERE cg_type!= 'local' AND cg_id NOT IN (SELECT cg_cg_id FROM acl_group_contactgroups_relations) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_host_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_hostgroup_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_service_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_servicegroup_relation)"

Solution

This operation allows you to optimize the loading time during authentication, by removing contact groups that are no longer in use. The goal is to keep only useful groups at the end of the procedure, following these steps:

• Separate the contact groups you want to keep by associating them with an ACL. 
• Delete contact groups (no longer relevant) that are not associated with an ACL.

Step 1 - Display all the contact groups

mysql centreon -e "SELECT cg_id,cg_name FROM contactgroup WHERE cg_type!= 'local' AND cg_id NOT IN (SELECT cg_cg_id FROM acl_group_contactgroups_relations) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_host_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_hostgroup_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_service_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_servicegroup_relation)"

Step 2 - Identify the contact groups to keep

If some contact groups are no longer used or shouldn't exist, you can separate them from the contact groups you want to keep (associated with an ACL).

1. Go to Administration > ACL > Access Groups.
2. Create a temporary group (temp in this example).
3. Add the group (LDAP_group in this example) you want to keep to it.
4. Save your changes.

The contact group (LDAP_group in this example) is now isolated by being linked to an ACL.

Step 3 - Display the contact groups to delete

This command allows you to display the contact groups that are not linked to an ACL. This will return the groups to be deleted:

mysql centreon -e "SELECT cg_id,cg_name FROM contactgroup WHERE cg_type!= 'local' AND cg_id NOT IN (SELECT cg_cg_id FROM acl_group_contactgroups_relations) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_host_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_hostgroup_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_service_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_servicegroup_relation)"

1. Make sure that only the contact groups that can be deleted are returned.
2. Make a mysqldump of Centreon.
3. Delete the contact groups:

   mysql centreon -e "DELETE FROM contactgroup WHERE cg_type!= 'local' AND cg_id NOT IN (SELECT cg_cg_id FROM acl_group_contactgroups_relations) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_host_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_hostgroup_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_service_relation) AND cg_id NOT IN (SELECT contactgroup_cg_id FROM contactgroup_servicegroup_relation)"