just wanted to give you some updates regarding several fixes for Icinga IDOUtils. There were reports about doubled rows within several tables, where data only gets inserted and not updated. During my analysis it came up that there are several mistaken unique constraint definitions within the table creation for MySQL.

The unique constraint makes sure that if an INSERT will try to insert updated data, that this will create an internal exception which is caught within the ON DUPLICATE KEY clause. If caught, an UPDATE will be issued and everything is fine.

Regarding the table servicecheck, this was missing. So the start time of the servicecheck was inserted to the database, and when the servicecheck was complete, the end time also was inserted as own row into the database. Kind of useless 2 rows isn’t it? ;-)

Today i did some further investigations on that since this happened with systemcommands too. It came up that tables timedevents and timedeventqueue had that “feature” too.

This is really bad because there are lots of those queries issued and data will grow fast. Not this time because there has been another modification to idomod.cfg – the data processing options haven been modified to ignore timedevents by default. It will improve IDOUtils a bit, but your feedback is as always very welcome!

Back to topic – those missing unique constraints have been added to actual GIT Master (fixing #173 and #181 – check for analysis and comparison) so make sure you get the latest and the greatest! :-)

For those who are using Postgresql or Oracle – I have implemented and debugged them in deep. And they have own WHERE clauses for UPDATE – so no worries about that, everything is fine! =)

IDOUtils queries differ quite a lot – some of the are just executed during startup, while others happen all the time. By analyzing the performance on our Oracle database with grid  it came to the top queries just like for

• servicechecks, servicestatus
• hostchecks, hoststatus
• timedevents
• programstatus

But how to improve the performance of those queries when they are called all the time?

Well, the query as is is always the same, only the values happen to change. So the basic idea is to prepare the statements with value place holders and if it comes to the query, just to bind the paramaters (values) to the prepared statement and execute that. This is a real performance boost compared to putting the query within the rdbm cache all the time.

Generally speaking the query statements are prepared after database connection and the statement handle is stored within the global dbinfo object (where the connection handler resides too).

dbinfo.oci_statement_programstatus = OCI_StatementCreate(dbinfo.oci_connection);
OCI_Prepare(dbinfo.oci_statement_programstatus, MT("MERGE INTO table USING DUAL ON (v1=:X1) WHEN MATCHED THEN UPDATE SET v2=:X2 WHEN NOT MATCHED THEN INSERT (v1, v2) VALUES (:X1, :X2)"))

When a query should be executed, all values will be binded (X1, X2) to the statement.

OCI_BindUnsignedBigInt(dbinfo.oci_statement_programstatus, MT(":X1"), (big_uint *) value1)
OCI_BindString(dbinfo.oci_statement_programstatus, MT(":X2"), (char *) value2)

Then the query gets executed.

OCI_Execute(dbinfo.oci_statement_programstatus);

Well it sounds quite simple but regarding the architecture of *DOUtils it was a hard nut to crack. The most common problem was the query buffer building – each unixtimestamp conversion is done before query building and sending the query. That does not fit for prepared statements where the whole query is pushed into the database cache.

Within the code, there is an char* array which gets the SQL-code from ndo2db_db_timet_to_sql and this is then printed to the whole statements. Not very useful since you may paste that right within each query. For the prepared statements, I’ve added all plain unixtimestamps to the data[] array and then binding the values directly.

(SELECT unixts2date(:X3) FROM DUAL)

So the bind param task has been done for the initial steps, improved delete statements and other improvements need to be implemented.

Another thing which was quite nasty is that Oracle support was dependant on libdbi, but it was not even used. So I decided to split the code completely and change configure. If you use –enable-oracle it will only require ocilib to work, it does not complain about a missing libdbi. The other way around it also works fine just like it was.

Conclusion to that – you won’t need libdbi to get Oracle support for Icinga IDOUtils – just ocilib.

Those improvements have been pushed to actual GIT master und you are very welcome to test and report bugs! =)

Listed below are the configure options…

./configure --help
`configure' configures this package to adapt to many kinds of systems.

Usage: ./configure [OPTION]... [VAR=VALUE]...

To assign environment variables (e.g., CC, CFLAGS...), specify them as
VAR=VALUE.  See below for descriptions of some of the useful variables.

Defaults for the options are specified in brackets.

Configuration:
 -h, --help              display this help and exit/
 --help=short        display options specific to this package
 --help=recursive    display the short help of all the included packages
 -V, --version           display version information and exit
 -q, --quiet, --silent   do not print `checking...' messages
 --cache-file=FILE   cache test results in FILE [disabled]
 -C, --config-cache      alias for `--cache-file=config.cache'
 -n, --no-create         do not create output files
 --srcdir=DIR        find the sources in DIR [configure dir or `..']

Installation directories:
 --prefix=PREFIX         install architecture-independent files in PREFIX
 [/usr/local/icinga-web]
 --exec-prefix=EPREFIX   install architecture-dependent files in EPREFIX
 [PREFIX]

By default, `make install' will install all the files in
`/usr/local/icinga-web/bin', `/usr/local/icinga-web/lib' etc.  You can specify
an installation prefix other than `/usr/local/icinga-web' using `--prefix',
for instance `--prefix=$HOME'.

For better control, use the options below.

Fine tuning of the installation directories:
 --bindir=DIR            user executables [EPREFIX/bin]
 --sbindir=DIR           system admin executables [EPREFIX/sbin]
 --libexecdir=DIR        program executables [EPREFIX/libexec]
 --sysconfdir=DIR        read-only single-machine data [PREFIX/etc]
 --sharedstatedir=DIR    modifiable architecture-independent data [PREFIX/com]
 --localstatedir=DIR     modifiable single-machine data [PREFIX/var]
 --libdir=DIR            object code libraries [EPREFIX/lib]
 --includedir=DIR        C header files [PREFIX/include]
 --oldincludedir=DIR     C header files for non-gcc [/usr/include]
 --datarootdir=DIR       read-only arch.-independent data root [PREFIX/share]
 --datadir=DIR           read-only architecture-independent data [DATAROOTDIR]
 --infodir=DIR           info documentation [DATAROOTDIR/info]
 --localedir=DIR         locale-dependent data [DATAROOTDIR/locale]
 --mandir=DIR            man documentation [DATAROOTDIR/man]
 --docdir=DIR            documentation root [DATAROOTDIR/doc/PACKAGE]
 --htmldir=DIR           html documentation [DOCDIR]
 --dvidir=DIR            dvi documentation [DOCDIR]
 --pdfdir=DIR            pdf documentation [DOCDIR]
 --psdir=DIR             ps documentation [DOCDIR]

Optional Packages:
 --with-PACKAGE[=ARG]    use PACKAGE [ARG=yes]
 --without-PACKAGE       do not use PACKAGE (same as --with-PACKAGE=no)
--with-web-user=<user> sets user name to run icinga
--with-web-group=<group> sets group name to run icinga
--with-bin-user=<user> sets user name for common webfiles
--with-bin-group=<group> sets group for common webfiles

Some influential environment variables:
 CC          C compiler command
 CFLAGS      C compiler flags
 LDFLAGS     linker flags, e.g. -L<lib dir> if you have libraries in a
 nonstandard directory <lib dir>
 LIBS        libraries to pass to the linker, e.g. -l<library>
 CPPFLAGS    C/C++/Objective C preprocessor flags, e.g. -I<include dir> if
 you have headers in a nonstandard directory <include dir>

Use these variables to override the choices made by `configure' or to help
it to find libraries and programs with nonstandard names/locations.

Report bugs to the package provider.

So at this stage the installer is just placing the files and setting the permissions for the Icinga-web UI it is not setting up Apache nor checking the necessary prerequisites this still needs to be done manually.

Please report any bug/issues to the Icinga development team at icinga-devel@lists.sourceforge.net

With recent correspondence from Thomas Spycher of Genotec.ch, He has informed us that they (Genotec.ch) have chosen to utilise Icinga for the monitoring (appropriately 500 hosts!) of their server equipment.

Here are some extracts from a recent Blog from Thomas with his thoughts on Icinga, and how he will be implementing it throughout Genotec.ch

Original post from: netzwerk-und-server-monitoring-mit-icinga

… Icinga still is quite young but my first tests ran extremely stable. The text-based configuration is more complex but provides a lot of possibilities for automated solutions.

… First Tests were very successful and we are thinking about replacing the current productive monitoring system by icinga. Due to the great flexibility, extensions are set up extremely fast.

… That’s why Genotec doesn’t use just sms and e-mail for notification but also Prowl to push notifications directly to iphones. For documentation of outages, internal tracker items for the internal system are automatically generated. There are endless opportunities. It takes 5 minutes to set up notifications via twitter.

… Contact to the developers of Icinga is great! We and Icinga are looking forward to complete the go-live of our instance and to release the first Icinga stable. Furthermore we are confident about the teamwork with Icinga and we are sure Gentotec and Icinga can benefit from it.

… It looks like Gentoc is the first provider who relies on Icinga. I am confident this direction is the right one and I am looking forward to keep you informed about further steps of this project.

Also I have to add, Thomas informed us via twitter, that he had the released 1.0 RC1 up and running within 10 minutes of the release hitting SourceForge!

So thank you Thomas and Genotec.ch for both choosing Icinga for your monitoring solution & for making contact with us. Your feedback is encouraging for the hard working development team who have spent many hours to achieve (what  has been a remarkable job in 5 months!) the first Release Candidate of Icinga…

Here you can have a look on the presentation.

Demo for the alpha version will be available soon.

Nice weekend.

We decided to split the releases into Icinga Core and Icinga Web. Therefore you will find two several packages to download and install.

The Core contains the Icinga API and IDOUtils which are needed for a functional Web this time. Make sure to download Icinga 1.0 RC1 and install it with IDOUtils for MySQL, the API will be installed automatically in share/icinga-api/

The Icinga Web depends on the API – you have to point the config to the actual install path. There are also several prerequisites and dependencies to resolve, so please catch on the instructions in doc/install-fromscratch.txt and do not hesitate to ask questions on the mailinglists and/or report any issues/bugs on our dev tracker.

Please keep in mind that this is an alpha release and be patient while we are working on future versions! :-)

Here’s a list of resources to assist you in your journey…

http://docs.icinga.org/

http://www.icinga-portal.org/

http://www.monitoringexchange.org/

http://en.irc-monitor.com/Icinga

http://en.irc-monitor.com/Icinga-devel

https://lists.sourceforge.net/lists/listinfo/icinga-users

https://lists.sourceforge.net/lists/listinfo/icinga-devel

Remember that the Icinga-core is backward compatible with Nagios, so you will notice that some of the resources above have shared content.

New features/updates implemented in this release are… (see Changelog for more detail)

• Oracle support with oclilib
• SSL support
• idoutils: fix typecast bug – unsigned long is bigger then int
• idoutils: fix oracle notif_timeperiod_object_id (30 chars max)
• idoutils: fix oracle table serviceescalationcontactgroups (30 chars max)
• idoutils: fix hoststatus/servicestatus/servicecheck unique constraint updating mismatch
• idoutils: fix insert_or_update queries where update violates unique constraint
• idoutils: remove and ignore oracle table_prefix (30 chars max)
• idoutils: workaround to handle more than 4k Data which may exceed service outputs incl. long perf_data
• core init: delete ’su’ usage since all previously su touched files were created by the icinga user
• idoutils: fix unescape bug during tabstops
• idoutils: fix idomod/icinga segfault while try to use ssl on unix sockets
• core configure: set INIT_OPTS depending on detected host OS (no more root:root on FreeBSD?)
• idoutils: fix case insensitive comparison in MySQL using BINARY
• idoutils: change ido2db.cfg: ndo2db_[user|group] to ido2db_[user|group]
• core web: fix missing gif icon copy

Please Note: if you are upgrading from an earlier release, you will need to update the user config in ido2db.cfg from ndo2db_[user|group] to ido2db_[user|group]

As always your feedback is welcome! if you have suggestions or improvements please communicate these via the icinga-devel

mailing list and should you have any trouble with installing/compiling then please use the icinga-users mailing list.

More details to follow soon about the new Icinga-web UI…

From the Icinga Team … Enjoy!

This web ui will be available soon…

work was not getting better and getting Oracle to work was on hold. So I decided to push a night of coding after I had prepared the MERGE queries.

And yes, it was successful – initial support for Oracle is done!

Oracle driver requirements

You”ll catch the problems with libdbi and Oracle in older blog posts. The new driver proposed by myself was ocilib, developed by Vincent Rogier. Currently it’s kind of a break up, so you need to have the libdbi installed and then install ocilib as an add-on (and Oracle libs and includes i.e. the Oracle Instant Client). As far as I know ocilib is not in the repositories right now so get the latest version and compile them yourself (documentation is really good!).

Modifying Configure for Oracle

If you enable IDOUtils during configure you now have the opportunity to use the flag –enable-oracle – if you didn’t install ocilib to default path /usr/local/ [lib/include] you can use

--with-ocilib-lib=/path/to/ocilib/lib
--with-ocilib-inc=/path/to/ocilib/include

to point configure to ocilib. It will be linked at runtime so you do not need to tell configure where $ORACLE_HOME and $LD_LIBRARY_PATH relays. Configure will output the following

• export LD_LIBRARY_PATH in ido2db Initscript where ocilib resides (OCI_IMPORT_RUNTIME)
• enable #define USE_ORACLE and ocilib.h for the compiler
• create ido2db.cfg-sample with Oracle support

After that you can perform a normal install.

Whencompiling the code  it heavily depends on #define USE_ORACLE – if you plan to change back to another RDBM using libdbi you’ll have to issue
# make distclean
# make clean
# ./configure --enable-idoutils

Oracle Database Setup

In module/idoutils/db/ you will find oracle.sql and oracle-drop.sql. The first one creates the table definitions, a time conversion function and all the triggers and sequences needed for commonly used insert ids. oracle-drop.sql is just for testing purposes and cleans the database scheme.
Make sure you setup the Oracle DB with an appropriate scheme with username/password. Then copy oracle.sql to your $ORACLE_HOME at the db server and import it e.g. by using sqlplus
# su - oracle
$ sqlplus dbuser/dbpass
SQL> @oracle.sql

Then edit your ido2db.cfg for using Oracle. Please note that Oracle ignores the db_host, instead point db_name to //DBSERVER/DBNAME

db_servertype=oracle
db_port=1521
db_user=icinga
db_pass=icinga

That should do the trick. If you are experiencing problems turn the debug_level=-1 and debug_verbosity=2 and make sure the max_debug_file_size is set to at least 100 MB – the improved debug output will put a lot of output into that file.

Changes to the code

The biggest part has been done already getting Postgres to work – rewrite the INSERT OR UPDATE queries and extract the unique constraints for UPDATE conditions. Those queries have been adapted to use the MERGE trick. Some queries tried to issue an UPDATE where the unique constraint contained a value to be updated too. That failed heavily but introduces a really nice feature of ocilib.

By using OCI_Initialize it is possible to register an error handler function. This function simply gets the last OCI error and writes that to syslog and debug output. So it is really easy to find out why queries will fail and the nice thing is – the function doesn’t need to be called everywhere, just register it to ocilib.

Another heavy task was getting the insert id – MySQL supports last_insert_id but Postgres and Oracle don’t. In Postgres it’s rather easy defining the PK as SERIAL and getting the sequence id in order to get the last insert id. For Oracle, there are several ON INSERT TRIGGERs defined in oracle.sql which auto increment the id (primary key). Given that a specified function reads that values from the opened session.

Simply said, you do the following for an Oracle DB query in IDOUtils

• OCI_Initialize(ido2db_ocilib_err_handler, NULL, OCI_ENV_DEFAULT)
• oci_connection = OCI_ConnectionCreate(dbname,username, password, OCI_SESSION_DEFAULT);
• oci_statement = OCI_StatementCreate(oci_connection);
• OCI_ExecuteStmt(oci_statement, MT(”SELECT * FROM ….”));
• OCI_Commit(oci_connection);
• oci_resultset = OCI_GetResultset(oci_statement);
• instance_id = OCI_GetUnsignedInt(idi->dbinfo.oci_resultset, 1);
• OCI_ConnectionFree(oci_connection);
• OCI_Cleanup();

There were some other minor and major changes to the code…

• changed NOW() to SYSDATE
• modify table serviceescalation_contactgroups to serviceescalationcontactgroups (30 chars max in Oracle)
• primary key only is id anstead of [tablename]_id (30 chars max in Oracle)
• dropped table_prefix – ido2db.cfg setting will be ignored
• long_output uses CLOB (Character Large Object) since varchar2 supports 4000 bytes at maximum

I’ve also added a runtime version check for ocilib – if the library contains errors and does not export symbols correctly ido2db will quit correctly.

Conclusion

This is the initial version of Oracle support for Icinga IDOUtils. It was a bunch of work but there are many things to follow:

• rewrite heavily used queries (host/service/check/status, timedevents) to prepared statements and parameter bindings
• improve housekeeping DELETE queries with partitioned COMMITs
• improve getting the insert ids
• do not depend on libdbi if –enable-oracle is used

Oracle support for IDOUtils will be in Icinga 1.0 RC – watch out for the upcoming release and have fun testing! Please report any bugs or feature requests to the mailinglists and/or our dev tracker!

Many thanks to David Schmidt for implementing the first version of NDOUtils Oracle, many ideas have been improved within here. And also many many many thanks to Vincent Rogier for implementing such a great Oracle driver within the project ocilib. It is a pleasure coding based on ocilib, reading the documentation meanwhile and getting instant support for free! :-)

Since this is the third RDBM to be maintained by only 2 Core team members, please contact us if you like to participate and/or help us improving more RDBM support! :)