Icinga2 IDO pgsql database / Icinga Reporting Questions-Issues

This forum was archived to /woltlab and is now in read-only mode.
  • Hello All,

    i hope you are all happy and well.

    So, i don't know if i had mentioned this again, but i am running now an icinga2 installation with 521 hosts and 13482 Services. I have integrated with pnp and grafana and now i am trying to have some basic reporting functionality. I know i know, this is not yet a fully functional feature but i got quite well with the installation of the jasperserver and icinga-reports 1.10. As I expected this does not work for all the reports, and i was wondering if you have heard something about fixing some queries for postgres integration, especially for host availability reporting.

    The weird error message i am getting from icinga-reports is:

    Furthermore, while i was digging around i noticed that although table icinga_hoststatus had updated data, their number rows was quite low and not more than 1 year.

    1. select max(status_update_time),min(status_update_time),count(1) from icinga_hoststatus;
    2. max | min | count
    3. ---------------------+---------------------+-------
    4. 2017-05-16 15:33:20 | 2016-06-05 17:06:51 | 582

    Then my digging got me to the table icinga_logentries.

    1. select max(logentry_time),min(logentry_time),count(1) from public.icinga_logentries ;
    2. max | min | count
    3. ---------------------+---------------------+--------
    4. 2016-09-02 04:05:23 | 2016-06-05 11:29:08 | 906097

    As you can see the table is not updated with latest data, and i was wondering if there is something wrong with my installation that i am not aware.

    You comments hints or anything really you could provide me would be greatly appreciated as i have already spend so much time searching and digging in. I am sure i have done something wrong but i need to find out where, since my logging stopped at 2016-09-02 04:05:23



    My ido_pgsql file is :

    my enabled features:

    1. icinga2 feature list
    2. Disabled features: debuglog gelf influxdb opentsdb syslog
    3. Enabled features: api checker command compatlog graphite ido-pgsql livestatus mainlog notification perfdata statusdata

    my icinga2 version:

  • There are plans to create a better reporting integration in the future, but that also requires changes in the backend and data storage. PostgreSQL and MySQL support are requirements for such a design. icinga-reports is unfortunately not ready for PostgreSQL, mainly for the magic availability sql function. The other reports and their SQL should work.

  • Thank you again for you reply dnsmichi. You are always there for all of us and i appreciate that.

    Do you know if there is any feasible way to migrate all my data from postgres to mysql and use mysql features? Unfortunatelly it seems it did not do my research well when i started building this system into production.

  • I appreciate your kind feedback :) No worries, we're not perfect in designing things either. PostgreSQL should be a first class citizen, and we learned from the past.

    The current IDO schema basically has three different table structures:

    • config (updated on each restart)
    • status (updated on restart and runtime)
    • history (updated on events)

    If you decide to switch from PostgreSQL to MySQL, and you keep the configuration objects the exact same, you'll certainly do care about history.

    The main tables are statehistory, downtimehistory, notifications, acknowledgements iirc. You should double check the source code of icinga-reports for that. Migrating that data over to MySQL shouldn't be an issue with an export and import. I needed to google that - http://stackoverflow.com/a/5746348 - but I'm sure you'll find a way to generate plain insert statements from such dumps.

  • To save some time on others who wish to migrate icinga from postgres to mysql, these are the steps that i followed.

    Dump your current icinga postgres db:

    1. pg_dump --data-only --column-inserts icinga > /tmp/icinga.sql

    Modify your dump to comment out all postgres specific lines

    1. sed -i 's/^SET/^--SET/g' /tmp/icinga.sql
    2. sed -i 's/^SELECT\ pg/--SELECT\ pg/g' /tmp/icinga.sql
    3. sed -i 's/^INSERT INTO icinga_dbversion/--INSERT INTO icinga_dbversion/g' /tmp/icinga.sql

    On the top of the dump file insert the below lines:

    1. SET autocommit=0;
    2. SET unique_checks=0;
    3. SET foreign_key_checks=0;

    And the below at the bottom:

    1. COMMIT;
    2. SET autocommit=1;
    3. SET unique_checks=1;
    4. SET foreign_key_checks=1;

    These three lines will save you a lot of time since if you do not add them, each line of the dump file will be committed at the time of the statement which makes the import too slow. These will make all statements and finally COMMIT all the statements at once. Foreign key checks and unique checks will also save you some times too!

    Create the Icinga Database on Mysql/Mariadb

    1. mysql> create database icinga;

    Create your user icinga and grand the needed privileges

    1. mysql> GRANT SELECT, INSERT, UPDATE, DELETE, DROP, CREATE VIEW, INDEX, EXECUTE ON icinga.* TO 'icinga'@'localhost' IDENTIFIED BY 'yourpassword';

    Import the initial sql to create necessary tables

    1. mysql -u root -p icinga < /usr/share/icinga2-ido-mysql/schema/mysql.sql

    And finally import your dump file

    1. mysql -u root -p icinga < /tmp/icinga.sql
  • dnsmichi, final question for this subject

    Is the table icinga_logentries used any more? I find it a bit strange to have almost a million entries that stopped at 2/9/2016. It's like something was broken of disabled. Unfortunately i did not kept records of icinga upgrades.

  • The logentries table was never used by Icinga Web 2 or any other known addon. Although it was in the default value for "categories". This has been changed in 2.6 IIRC and therefore the IDO feature won't populate that table anymore.


    1. commit f87f20e9ec3c3ca2a9f16331a031fb4c283962b2
    2. Author: Michael Friedrich <michael.friedrich@netways.de>
    3. Date: Wed Jul 6 11:52:20 2016 +0200
    4. DB IDO: Do not populate logentries table by default
    5. Not required by Icinga Web 2 and therefore obsolete as default.
    6. fixes #12106