Notification

[noble]

Hi,
I’m trying to add a new contact to 20 or more hosts under the "Host Alerts tab" so a specific contact or contact group will receive email notification.
Currently I been adding it to my hosts one at time is there an eraser way to accomplish this task.

[tonyyarusso]

You can create Host Groups of hosts with similar configurations, and add the contact to the host group.

[noble]

I already have a host group with the 20 plus hosts created. As I try to configure that host group my only option are to add members or host group members. It would be nice to have a contact and contact group drop down list for host group.

[tonyyarusso]

Hmm, never mind. Apparently you can't. I'll tell Mike to add that when he rewrites the Core Config Manager. For 20 hosts, I guess you'll have to just walk through them manually. If you had to do a very large number, a custom SQL query could be written to accomplish it.

[noble]

Can you please provide the SQL statement.

[tonyyarusso]

(Note: This is all stored in the 'nagiosql' MySQL database on your XI server.)

First, here are what I have for hosts, hostgroups, and contacts on a test system for demonstration purposes:

Code: Select all

mysql> select id, host_name, address from tbl_host;
+----+----------------+----------------+
| id | host_name      | address        |
+----+----------------+----------------+
|  1 | localhost      | 127.0.0.1      | 
| 11 | Tony Desktop   | 192.168.5.9    | 
| 28 | Cube Switch    | 192.168.5.42   | 
| 22 | Server Room    | 192.168.5.254  | 
| 15 | Ubuntu Desktop | 192.168.5.52   | 
| 25 | ESX Server     | 192.168.5.32   | 
| 29 | vs1.nagios.org | vs1.nagios.org | 
| 30 | 192.168.5.42   | 192.168.5.42   | 
+----+----------------+----------------+
8 rows in set (0.00 sec)

mysql> select id, hostgroup_name, alias from tbl_hostgroup;
+----+-----------------------------------------+------------------------------------------+
| id | hostgroup_name                          | alias                                    |
+----+-----------------------------------------+------------------------------------------+
|  1 | linux-servers                           | Linux Servers                            | 
|  2 | windows-servers                         |                                          | 
|  3 | all_vmwareesxvspherevcenterserver_hosts | All VMware ESX - vSphere - vCenter Hosts | 
|  4 | public-sites                            | Public Web Sites                         | 
+----+-----------------------------------------+------------------------------------------+
4 rows in set (0.00 sec)

mysql> select id, contact_name from tbl_contact;
+----+--------------+
| id | contact_name |
+----+--------------+
|  1 | nagiosadmin  | 
|  3 | tony         | 
+----+--------------+
2 rows in set (0.00 sec)

There are two different places where host to hostgroup definitions can be found, as you'll see below. You'll need to address both cases.

Code: Select all

mysql> select * from tbl_lnkHostToHostgroup;
+----------+---------+
| idMaster | idSlave |
+----------+---------+
|       29 |       1 | 
+----------+---------+
1 row in set (0.02 sec)

mysql> select * from tbl_lnkHostgroupToHost;
+----------+---------+
| idMaster | idSlave |
+----------+---------+
|        1 |       1 | 
|        1 |      15 | 
+----------+---------+
2 rows in set (0.00 sec)

mysql> SELECT tbl_host.id, host_name, address \
    -> FROM tbl_host \
    -> LEFT JOIN tbl_lnkHostToHostgroup ON tbl_host.id=tbl_lnkHostToHostgroup.idMaster \
    -> LEFT JOIN tbl_lnkHostgroupToHost ON tbl_host.id=tbl_lnkHostgroupToHost.idSlave \
    -> LEFT OUTER JOIN tbl_hostgroup ON tbl_hostgroup.id IN(tbl_lnkHostToHostgroup.idSlave, tbl_lnkHostgroupToHost.idMaster) \
    -> LEFT JOIN tbl_lnkHostToContact ON tbl_lnkHostToContact.idMaster=tbl_host.id
    -> WHERE tbl_hostgroup.hostgroup_name='linux-servers' \
    -> GROUP BY tbl_host.id;
+----+----------------+----------------+
| id | host_name      | address        |
+----+----------------+----------------+
|  1 | localhost      | 127.0.0.1      | 
| 15 | Ubuntu Desktop | 192.168.5.52   | 
| 29 | vs1.nagios.org | vs1.nagios.org | 
+----+----------------+----------------+
3 rows in set (0.00 sec)

The following will add the contact 'tony' (id: 3) to every host in the 'linux-servers' hostgroup:

Code: Select all

mysql> UPDATE tbl_host \
    -> LEFT JOIN tbl_lnkHostToHostgroup ON tbl_host.id=tbl_lnkHostToHostgroup.idMaster \
    -> LEFT JOIN tbl_lnkHostgroupToHost ON tbl_host.id=tbl_lnkHostgroupToHost.idSlave \
    -> LEFT OUTER JOIN tbl_hostgroup ON tbl_hostgroup.id IN(tbl_lnkHostToHostgroup.idSlave, tbl_lnkHostgroupToHost.idMaster) \
    -> SET contacts=1 \
    -> WHERE tbl_hostgroup.hostgroup_name='linux-servers';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> INSERT IGNORE INTO tbl_lnkHostToContact (idMaster, idSlave) \
    -> SELECT tbl_host.id, 3 \
    -> FROM tbl_host \
    -> LEFT JOIN tbl_lnkHostToHostgroup ON tbl_host.id=tbl_lnkHostToHostgroup.idMaster \
    -> LEFT JOIN tbl_lnkHostgroupToHost ON tbl_host.id=tbl_lnkHostgroupToHost.idSlave \
    -> LEFT OUTER JOIN tbl_hostgroup ON tbl_hostgroup.id IN(tbl_lnkHostToHostgroup.idSlave, tbl_lnkHostgroupToHost.idMaster) \
    -> LEFT JOIN tbl_lnkHostToContact ON tbl_lnkHostToContact.idMaster=tbl_host.id
    -> WHERE tbl_hostgroup.hostgroup_name='linux-servers' \
    -> GROUP BY tbl_host.id;
Query OK, 0 rows affected (0.00 sec)
Records: 3  Duplicates: 1  Warnings: 0

These changes will not take effect until you click "Apply Config" in the Core Configuration Manager.

[noble]

Hi,

I don't have tables that you mentioned. I thnk I'm using different version. Here is the table list.

Code: Select all

nagios_acknowledgements                | 
| nagios_commands                        | 
| nagios_commenthistory                  | 
| nagios_comments                        | 
| nagios_configfiles                     | 
| nagios_configfilevariables             | 
| nagios_conninfo                        | 
| nagios_contact_addresses               | 
| nagios_contact_notificationcommands    | 
| nagios_contactgroup_members            | 
| nagios_contactgroups                   | 
| nagios_contactnotificationmethods      | 
| nagios_contactnotifications            | 
| nagios_contacts                        | 
| nagios_contactstatus                   | 
| nagios_customvariables                 | 
| nagios_customvariablestatus            | 
| nagios_dbversion                       | 
| nagios_downtimehistory                 | 
| nagios_eventhandlers                   | 
| nagios_externalcommands                | 
| nagios_flappinghistory                 | 
| nagios_host_contactgroups              | 
| nagios_host_contacts                   | 
| nagios_host_parenthosts                | 
| nagios_hostchecks                      | 
| nagios_hostdependencies                | 
| nagios_hostescalation_contactgroups    | 
| nagios_hostescalation_contacts         | 
| nagios_hostescalations                 | 
| nagios_hostgroup_members               | 
| nagios_hostgroups                      | 
| nagios_hosts                           | 
| nagios_hoststatus                      | 
| nagios_instances                       | 
| nagios_logentries                      | 
| nagios_notifications                   | 
| nagios_objects                         | 
| nagios_processevents                   | 
| nagios_programstatus                   | 
| nagios_runtimevariables                | 
| nagios_scheduleddowntime               | 
| nagios_service_contactgroups           | 
| nagios_service_contacts                | 
| nagios_servicechecks                   | 
| nagios_servicedependencies             | 
| nagios_serviceescalation_contactgroups | 
| nagios_serviceescalation_contacts      | 
| nagios_serviceescalations              | 
| nagios_servicegroup_members            | 
| nagios_servicegroups                   | 
| nagios_services                        | 
| nagios_servicestatus                   | 
| nagios_statehistory                    | 
| nagios_systemcommands                  | 
| nagios_timedeventqueue                 | 
| nagios_timedevents                     | 
| nagios_timeperiod_timeranges           | 
| nagios_timeperiods       

Here is the new contact I would like to add to host group.

Code: Select all

mysql> select contact_id, instance_id, config_type, contact_object_id, alias from nagios_contacts
    -> where alias = 'Nagios Network Alerts';
+------------+-------------+-------------+-------------------+-----------------------+
| contact_id | instance_id | config_type | contact_object_id | alias                 |
+------------+-------------+-------------+-------------------+-----------------------+
|       4361 |           1 |           1 |              1076 | Nagios Network Alerts | 
+------------+-------------+-------------+-------------------+-----------------------+

Here is the host group.

Code: Select all

mysql> select hostgroup_id, instance_id, config_type, hostgroup_object_id, alias from nagios_hostgroups
    -> where alias = 'Internal Network';
+--------------+-------------+-------------+---------------------+------------------+
| hostgroup_id | instance_id | config_type | hostgroup_object_id | alias            |
+--------------+-------------+-------------+---------------------+------------------+
|        28945 |           1 |           1 |                 322 | Internal Network | 
+--------------+-------------+-------------+---------------------+------------------+

[mguthrie]

I think you're querying the wrong database. You need the nagiosql database in mysql. Ndoutils also uses mysql and has a database in called either nagios or nagiosxi, and I think that's the DB that you queried.