I'm working on provisioning Icinga2 Web2 with IDO PostgreSQL and IDO MySQL using Ansible on CentOS 7.
I base my work on the great https://github.com/Icinga/icinga2-ansible roles. I have forked the project (mickael-ange/icinga2-ansible) to added more automation and support such as IDO PostgreSQL. I'm using PostgreSQL server 9.4.
Well, I ends up with a version of icinga2-ansible-web2-ui role which installs Icinga2 Web2 with IDO PostgreSQL or IDO MySQL automatically without wizard.
The server seems to work properly at the exception that I cannot figure out how to get data in Icinga2 Web2 interface such as:
Comments and Downtimes from Overview menuEvent Grid, Event Overview, Notifications, Timeline from History menuI can create Comments and Schedule Downtimes, but they don't get listed in the UI. Downtimes are processed correctly.
I can send Notifications but they don't get listed in the UI as well. Notifications are sent out when SELinux is in permissive mode.
My questions is: is there any IDO or Icinga2 Web2 configuration to get the database populate with Comments, Downtimes, etc..?
My /etc/icinga2/features-enabled/ido-pgsql.conf
library "db_ido_pgsql"
object IdoPgsqlConnection "ido-pgsql" {
user = "icinga"
password = "icinga"
host = "localhost"
database = "icinga"
table_prefix = "icinga_"
instance_name = "icinga2"
instance_description = "icinga2 instance"
cleanup = {
downtimehistory_age = 48h
logentries_age = 31d
}
categories = DbCatConfig | DbCatState
}
I have already searched in most of the documentation of Icinga2 project and forums I could find but no cigar. I'm surprise none gets this problem before so it is probably a problem on my side.
After chatting on Icinga IRC channel and doing more tests I figure out that my problem only happens with PostgreSQL backend. It is working as expected with MySQL backend.
I have gathered more info/logs about my environments.
I checked my PostgreSQL permissions for icinga user:
TABLE permissions are set to SELECT,INSERT,UPDATE,DELETESEQUENCE permissions are set to USAGEI even tried configuring postgres as user to access to the backend (icinga_ido and icingaweb_ido resources) but I'm still not getting any Comments listed in the UI (after having created a comment).
Here are the PosgreSQL permissions listed by \dp command:
icinga=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-----------------------------------------------------------------+----------+---------------------------+--------------------------
public | icinga_acknowledgements | table | postgres=arwdDxt/postgres+|
| | | icinga=arwd/postgres |
public | icinga_acknowledgements_acknowledgement_id_seq | sequence | postgres=rwU/postgres +|
| | | icinga=U/postgres |
public | icinga_commands | table | postgres=arwdDxt/postgres+|
| | | icinga=arwd/postgres |
public | icinga_commands_command_id_seq | sequence | postgres=rwU/postgres +|
| | | icinga=U/postgres |
public | icinga_commenthistory | table | postgres=arwdDxt/postgres+|
| | | icinga=arwd/postgres |
public | icinga_commenthistory_commenthistory_id_seq | sequence | postgres=rwU/postgres +|
| | | icinga=U/postgres |
<truncated>
Furthermore, I have no error in PostgreSQL logs (e.g. /var/lib/pgsql/9.4/data/pg_log/postgresql-Thu.log) when creatinh Comment from Icinga UI.
However, I figured out there are errors when trying to browse SEQUENCE from pgAdmin UI using icinga user (no error with postgres user):
< 2016-01-21 03:55:52.436 GMT >STATEMENT: SELECT last_value, min_value, max_value, cache_value, is_cycled, increment_by, is_called
FROM icinga_acknowledgements_acknowledgement_id_seq
But I'm not sure if it makes sense or not as icinga user has only USAGE permission on SEQUENCE.
So I ends up with comparing Icinga debug logs on both sides (MySQL IDO and PostgreSQL IDO) when I creates a comment. I can see that only with MySQL backend there are the SQL statements I was looking for, whom insert data in icinga_externalcommands, icinga_comments, and icinga_commenthistory tables.
I don't have enough reputation to provide more than 2 links (e.g. pastebin). So here are the logs.
Adding a Comment with PostgreSQL backend:
[2016-01-21 04:14:00 +0000] information/ExternalCommandListener: Executing external command: [1453349640] ADD_HOST_COMMENT;icinga2-web2-postgres;1;icingaadmin;dededewdwwewdew
[2016-01-21 04:14:00 +0000] debug/DbEvents: add external command history
[2016-01-21 04:14:00 +0000] notice/ExternalCommandProcessor: Creating comment for host icinga2-web2-postgres
[2016-01-21 04:14:00 +0000] information/ConfigCompiler: Compiling config file: /var/lib/icinga2/api/packages/_api/icinga2-web2-postgres-1453347670-1/conf.d/comments/icinga2-web2-postgres!icinga2-web2-postgres-1453349640-1.conf
[2016-01-21 04:14:00 +0000] information/ConfigItem: Committing config items
[2016-01-21 04:14:00 +0000] warning/ApplyRule: Apply rule 'satellite-host' (in /etc/icinga2/conf.d/satellite.conf: 29:1-29:41) for type 'Dependency' does not match anywhere!
[2016-01-21 04:14:00 +0000] warning/ApplyRule: Apply rule '' (in /etc/icinga2/conf.d/services.conf: 57:1-57:65) for type 'Service' does not match anywhere!
[2016-01-21 04:14:00 +0000] warning/ApplyRule: Apply rule '' (in /etc/icinga2/conf.d/services.conf: 65:1-65:53) for type 'Service' does not match anywhere!
[2016-01-21 04:14:00 +0000] information/ConfigItem: Instantiated 1 Comment.
[2016-01-21 04:14:00 +0000] information/ConfigItem: Triggering Start signal for config items
[2016-01-21 04:14:00 +0000] information/ConfigItem: Activated all objects.
[2016-01-21 04:14:00 +0000] notice/Comment: Added comment 'icinga2-web2-postgres!icinga2-web2-postgres-1453349640-1'.
[2016-01-21 04:14:01 +0000] debug/IdoPgsqlConnection: Query: COMMIT
[2016-01-21 04:14:01 +0000] debug/IdoPgsqlConnection: Query: BEGIN
Adding a Comment with MySQL backend:
[2016-01-21 04:12:49 +0000] information/ExternalCommandListener: Executing external command: [1453349569] ADD_HOST_COMMENT;icinga2-web2-mysql;1;icingaadmin;yggygyyj
[2016-01-21 04:12:49 +0000] debug/DbEvents: add external command history
[2016-01-21 04:12:49 +0000] notice/ExternalCommandProcessor: Creating comment for host icinga2-web2-mysql
[2016-01-21 04:12:49 +0000] information/ConfigCompiler: Compiling config file: /var/lib/icinga2/api/packages/_api/icinga2-web2-mysql-1453347256-1/conf.d/comments/icinga2-web2-mysql!icinga2-web2-mysql-1453349569-0.conf
[2016-01-21 04:12:49 +0000] information/ConfigItem: Committing config items
[2016-01-21 04:12:49 +0000] warning/ApplyRule: Apply rule 'satellite-host' (in /etc/icinga2/conf.d/satellite.conf: 29:1-29:41) for type 'Dependency' does not match anywhere!
[2016-01-21 04:12:49 +0000] warning/ApplyRule: Apply rule '' (in /etc/icinga2/conf.d/services.conf: 57:1-57:65) for type 'Service' does not match anywhere!
[2016-01-21 04:12:49 +0000] warning/ApplyRule: Apply rule '' (in /etc/icinga2/conf.d/services.conf: 65:1-65:53) for type 'Service' does not match anywhere!
[2016-01-21 04:12:49 +0000] information/ConfigItem: Instantiated 1 Comment.
[2016-01-21 04:12:49 +0000] information/ConfigItem: Triggering Start signal for config items
[2016-01-21 04:12:49 +0000] information/ConfigItem: Activated all objects.
[2016-01-21 04:12:49 +0000] notice/Comment: Added comment 'icinga2-web2-mysql!icinga2-web2-mysql-1453349569-0'.
[2016-01-21 04:12:50 +0000] debug/IdoMysqlConnection: Query: INSERT INTO icinga_externalcommands (command_args, command_name, command_type, endpoint_object_id, entry_time, instance_id) VALUES ('icinga2-web2-mysql;1;icingaadmin;yggygyyj', 'ADD_HOST_COMMENT', '1', 1, FROM_UNIXTIME(1453349569), 1)
[2016-01-21 04:12:50 +0000] debug/IdoMysqlConnection: Query: INSERT INTO icinga_comments (author_name, comment_data, comment_source, comment_time, comment_type, endpoint_object_id, entry_time, entry_time_usec, entry_type, expires, instance_id, internal_comment_id, is_persistent, name, object_id) VALUES ('icingaadmin', 'yggygyyj', '1', FROM_UNIXTIME(1453349569), '2', 1, FROM_UNIXTIME(1453349569), '947563', '1', '0', 1, '1', '1', 'icinga2-web2-mysql!icinga2-web2-mysql-1453349569-0', 68)
[2016-01-21 04:12:50 +0000] debug/IdoMysqlConnection: Query: INSERT INTO icinga_commenthistory (author_name, comment_data, comment_source, comment_time, comment_type, endpoint_object_id, entry_time, entry_time_usec, entry_type, expires, instance_id, internal_comment_id, is_persistent, name, object_id) VALUES ('icingaadmin', 'yggygyyj', '1', FROM_UNIXTIME(1453349569), '2', 1, FROM_UNIXTIME(1453349569), '947563', '1', '0', 1, '1', '1', 'icinga2-web2-mysql!icinga2-web2-mysql-1453349569-0', 68)
[2016-01-21 04:12:50 +0000] debug/IdoMysqlConnection: Query: COMMIT
[2016-01-21 04:12:50 +0000] debug/IdoMysqlConnection: Query: BEGIN
Restarting Icinga2 with PostgreSQL and MySQL Backends
When restarting Icinga2 with both PostgreSQL and MySQL Backends, I have pretty much the same logs which sounds good!
# Icinga2 restart with PostgreSQL
[2016-01-21 05:02:11 +0000] information/Application: Received request to shut down.
[2016-01-21 05:02:11 +0000] information/Application: Shutting down...
[2016-01-21 05:02:11 +0000] information/CheckerComponent: Checker stopped.
[2016-01-21 05:02:11 +0000] information/DbConnection: Resuming IDO connection: ido-pgsql
[2016-01-21 05:02:11 +0000] information/ConfigItem: Activated all objects.
[2016-01-21 05:02:11 +0000] information/ConfigCompiler: Compiling config file: /var/lib/icinga2/modified-attributes.conf
[2016-01-21 05:02:11 +0000] information/IdoPgsqlConnection: pgSQL IDO instance id: 1 (schema version: '1.14.0')
# Icinga2 restart with MySQL
[2016-01-21 05:03:20 +0000] information/Application: Received request to shut down.
[2016-01-21 05:03:20 +0000] information/Application: Shutting down...
[2016-01-21 05:03:20 +0000] information/CheckerComponent: Checker stopped.
[2016-01-21 05:03:20 +0000] information/DbConnection: Resuming IDO connection: ido-mysql
[2016-01-21 05:03:20 +0000] information/ConfigItem: Activated all objects.
[2016-01-21 05:03:20 +0000] information/ConfigCompiler: Compiling config file: /var/lib/icinga2/modified-attributes.conf
[2016-01-21 05:03:20 +0000] information/IdoMysqlConnection: MySQL IDO instance id: 1 (schema version: '1.14.0')
Packages Version
For those who has the energy to reproduce the problem you can clone mickael-ange/icinga2-ansible Github repo (branch: Issue-No-Historical-Data-in-Icinga2-Web2-and-DB) and follow instructions at icinga2-we2-vagrant.md. You need to install the required tools if it is not already the case. Then install role dependencies with librarian-ansible. Finally, I wrote a section (#ido-no-historical-data-in-icinga2-web2-and-db) to reproduce the problem.
Thank you in advance to take the time read until here! Mickael
Finally, I figured out why historical data were not inserted into the DB, so I'm here responding to myself.
As explained in the documentation:
categories Optional. The types of information that should be written to the database.
So when I removed categories from my ido-pgsql.conf configuration. I restart Icinga2 then the data are written in the DB as expected. Comments, Downtimes, well everything I wanted is listed the UI now.
I also removed the cleanup section as it is probably not what I want too.
The final ido-pgsql.conf configuration:
library "db_ido_pgsql"
object IdoPgsqlConnection "ido-pgsql" {
user = "icinga"
password = "icinga"
host = "localhost"
database = "icinga"
table_prefix = "icinga_"
instance_name = "icinga2"
instance_description = "icinga2 instance"
}