NASA needs Postgres - Nagios help

We are implementing Nagios on Space Station and want to use PostgreSQL to store the data on orbit and then replicate that db on the ground. The problem is, most people use MySQL with Nagios. We need an addon to ingest Nagios data into PostgreSQL. It looks like the most reasonable implementation is to update the NDOUtils addon to support PostgreSQL. Does anyone have such an addon, or want to write one?

I'm the NASA project manager for the set of computers on Space Station and we plan to deploy this capability this year. If have to write our own addon, we will, but I'd rather use something already out there.

Duncavage, Daniel P. (JSC-OD211) wrote:
> We are implementing Nagios on Space Station and want to use PostgreSQL
> to store the data on orbit and then replicate that db on the ground.
> The problem is, most people use MySQL with Nagios. We need an addon to
> ingest Nagios data into PostgreSQL. It looks like the most reasonable
> implementation is to update the NDOUtils addon to support PostgreSQL.
> Does anyone have such an addon, or want to write one?

Cool project :) I once did some work on adding proper PostgreSQL support
to NDOutils but the problem is that the current code is really not too
well structured for a "real" RDBMS(prepared statements, transactions,...)
However the http://www.icinga.org/ fork of NDOutils (IDOutils) does have
some basic PostgreSQL support - maybe that will get you started.
>
>
>
> I'm the NASA project manager for the set of computers on Space Station
> and we plan to deploy this capability this year. If have to write our
> own addon, we will, but I'd rather use something already out there.

Yeah reusing code is always easier and you also don't have to maintain
it one your own as well :)

Stefan

On 13 July 2010 17:14, Duncavage, Daniel P. (JSC-OD211)
<daniel(dot)p(dot)duncavage(at)nasa(dot)gov> wrote:
> We are implementing Nagios on Space Station and want to use PostgreSQL to
> store the data on orbit and then replicate that db on the ground.  The
> problem is, most people use MySQL with Nagios.  We need an addon to ingest
> Nagios data into PostgreSQL.  It looks like the most reasonable
> implementation is to update the NDOUtils addon to support PostgreSQL.  Does
> anyone have such an addon, or want to write one?
>
>
>
> I'm the NASA project manager for the set of computers on Space Station and
> we plan to deploy this capability this year.  If have to write our own
> addon, we will, but I'd rather use something already out there.

This looks like it hasn't been worked on in a while, but is this any
use?: http://nagiosplugins.projects.postgresql.org/

Thom

On Tue, Jul 13, 2010 at 20:10, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> On 13 July 2010 17:14, Duncavage, Daniel P. (JSC-OD211)
> <daniel(dot)p(dot)duncavage(at)nasa(dot)gov> wrote:
>> We are implementing Nagios on Space Station and want to use PostgreSQL to
>> store the data on orbit and then replicate that db on the ground.  The
>> problem is, most people use MySQL with Nagios.  We need an addon to ingest
>> Nagios data into PostgreSQL.  It looks like the most reasonable
>> implementation is to update the NDOUtils addon to support PostgreSQL.  Does
>> anyone have such an addon, or want to write one?
>>
>>
>>
>> I'm the NASA project manager for the set of computers on Space Station and
>> we plan to deploy this capability this year.  If have to write our own
>> addon, we will, but I'd rather use something already out there.
>
> This looks like it hasn't been worked on in a while, but is this any
> use?: http://nagiosplugins.projects.postgresql.org/

Those are plugins to monitor postgresql using nagios. For that, you
should realy be looking at check_postgres. I think what the OP is
looking for is a way to store Nagios metadata in postgres, which is
something else.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 13 July 2010 21:25, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Tue, Jul 13, 2010 at 20:10, Thom Brown <thombrown(at)gmail(dot)com> wrote:
>> On 13 July 2010 17:14, Duncavage, Daniel P. (JSC-OD211)
>> <daniel(dot)p(dot)duncavage(at)nasa(dot)gov> wrote:
>>> We are implementing Nagios on Space Station and want to use PostgreSQL to
>>> store the data on orbit and then replicate that db on the ground.  The
>>> problem is, most people use MySQL with Nagios.  We need an addon to ingest
>>> Nagios data into PostgreSQL.  It looks like the most reasonable
>>> implementation is to update the NDOUtils addon to support PostgreSQL.  Does
>>> anyone have such an addon, or want to write one?
>>>
>>>
>>>
>>> I'm the NASA project manager for the set of computers on Space Station and
>>> we plan to deploy this capability this year.  If have to write our own
>>> addon, we will, but I'd rather use something already out there.
>>
>> This looks like it hasn't been worked on in a while, but is this any
>> use?: http://nagiosplugins.projects.postgresql.org/
>
> Those are plugins to monitor postgresql using nagios. For that, you
> should realy be looking at check_postgres. I think what the OP is
> looking for is a way to store Nagios metadata in postgres, which is
> something else.
>

Ah yes, I see. The documentation suggests PostgreSQL is supported in
version 1.0 under the "Database Support" section:
http://nagios.sourceforge.net/docs/1_0/xdata-db.html

Is that no longer the case then? They actually *removed* support? :(

Thom

safest path is to get the source code and build it..if you are unable to get the source..ping the author..if the author doesnt respond..implement that plugin or feature

in another language..(perl/java/php would be my recommendation)

keep us apprised,
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Tue, 13 Jul 2010 22:25:42 +0200
> Subject: Re: [GENERAL] NASA needs Postgres - Nagios help
> From: magnus(at)hagander(dot)net
> To: thombrown(at)gmail(dot)com
> CC: daniel(dot)p(dot)duncavage(at)nasa(dot)gov; pgsql-general(at)postgresql(dot)org
>
> On Tue, Jul 13, 2010 at 20:10, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> > On 13 July 2010 17:14, Duncavage, Daniel P. (JSC-OD211)
> > <daniel(dot)p(dot)duncavage(at)nasa(dot)gov> wrote:
> >> We are implementing Nagios on Space Station and want to use PostgreSQL to
> >> store the data on orbit and then replicate that db on the ground. The
> >> problem is, most people use MySQL with Nagios. We need an addon to ingest
> >> Nagios data into PostgreSQL. It looks like the most reasonable
> >> implementation is to update the NDOUtils addon to support PostgreSQL. Does
> >> anyone have such an addon, or want to write one?
> >>
> >>
> >>
> >> I'm the NASA project manager for the set of computers on Space Station and
> >> we plan to deploy this capability this year. If have to write our own
> >> addon, we will, but I'd rather use something already out there.
> >
> > This looks like it hasn't been worked on in a while, but is this any
> > use?: http://nagiosplugins.projects.postgresql.org/
>
> Those are plugins to monitor postgresql using nagios. For that, you
> should realy be looking at check_postgres. I think what the OP is
> looking for is a way to store Nagios metadata in postgres, which is
> something else.
>
>
> --
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
The New Busy is not the too busy. Combine all your e-mail accounts with Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4

Correct. We are looking to use Nagios to monitor various parameters on our network, then store them in postgresql, which we will then synch to the ground and distribute as a quasi realtime telemetry system.

-----Original Message-----
From: Magnus Hagander [mailto:magnus(at)hagander(dot)net]
Sent: Tuesday, July 13, 2010 3:26 PM
To: Thom Brown
Cc: Duncavage, Daniel P. (JSC-OD211); pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] NASA needs Postgres - Nagios help

On Tue, Jul 13, 2010 at 20:10, Thom Brown <thombrown(at)gmail(dot)com> wrote:
> On 13 July 2010 17:14, Duncavage, Daniel P. (JSC-OD211)
> <daniel(dot)p(dot)duncavage(at)nasa(dot)gov> wrote:
>> We are implementing Nagios on Space Station and want to use
>> PostgreSQL to store the data on orbit and then replicate that db on
>> the ground.  The problem is, most people use MySQL with Nagios.  We
>> need an addon to ingest Nagios data into PostgreSQL.  It looks like
>> the most reasonable implementation is to update the NDOUtils addon to
>> support PostgreSQL.  Does anyone have such an addon, or want to write one?
>>
>>
>>
>> I'm the NASA project manager for the set of computers on Space
>> Station and we plan to deploy this capability this year.  If have to
>> write our own addon, we will, but I'd rather use something already out there.
>
> This looks like it hasn't been worked on in a while, but is this any
> use?: http://nagiosplugins.projects.postgresql.org/

Those are plugins to monitor postgresql using nagios. For that, you should realy be looking at check_postgres. I think what the OP is looking for is a way to store Nagios metadata in postgres, which is something else.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 07/13/2010 10:44 PM, Thom Brown wrote:
> On 13 July 2010 21:25, Magnus Hagander<magnus(at)hagander(dot)net> wrote:
>> On Tue, Jul 13, 2010 at 20:10, Thom Brown<thombrown(at)gmail(dot)com> wrote:
>>> On 13 July 2010 17:14, Duncavage, Daniel P. (JSC-OD211)
>>> <daniel(dot)p(dot)duncavage(at)nasa(dot)gov> wrote:
>>>> We are implementing Nagios on Space Station and want to use PostgreSQL to
>>>> store the data on orbit and then replicate that db on the ground. The
>>>> problem is, most people use MySQL with Nagios. We need an addon to ingest
>>>> Nagios data into PostgreSQL. It looks like the most reasonable
>>>> implementation is to update the NDOUtils addon to support PostgreSQL. Does
>>>> anyone have such an addon, or want to write one?
>>>>
>>>>
>>>>
>>>> I'm the NASA project manager for the set of computers on Space Station and
>>>> we plan to deploy this capability this year. If have to write our own
>>>> addon, we will, but I'd rather use something already out there.
>>>
>>> This looks like it hasn't been worked on in a while, but is this any
>>> use?: http://nagiosplugins.projects.postgresql.org/
>>
>> Those are plugins to monitor postgresql using nagios. For that, you
>> should realy be looking at check_postgres. I think what the OP is
>> looking for is a way to store Nagios metadata in postgres, which is
>> something else.
>>
>
> Ah yes, I see. The documentation suggests PostgreSQL is supported in
> version 1.0 under the "Database Support" section:
> http://nagios.sourceforge.net/docs/1_0/xdata-db.html
>
> Is that no longer the case then? They actually *removed* support? :(

well - there was direct database support in nagios ages ago(nagios 1.x
is ancient) and replaced with a plugin based approach based on their
eventbroker architecture called NDOutils. Based on tracking internal
state it can be used to export current and historical monitoring data
from nagios for later postprocessing (or for usin a GUI or whatever).
NODutils however has no real working support for PostgreSQL, IDOutils
(which I mentioned elsewhere in the thread) from the icinga fork does
have basic support.

Stefan

Hi there,

On 2010-07-15 07:06, Stefan Kaltenbrunner wrote:
> well - there was direct database support in nagios ages ago(nagios 1.x
> is ancient) and replaced with a plugin based approach based on their
> eventbroker architecture called NDOutils. Based on tracking internal
> state it can be used to export current and historical monitoring data
> from nagios for later postprocessing (or for usin a GUI or whatever).
> NODutils however has no real working support for PostgreSQL, IDOutils
> (which I mentioned elsewhere in the thread) from the icinga fork does
> have basic support.

The SQL queries used in NDOUtils are highly MySQL specific, mostly the
ON DUPLICATE KEY functionality based on unique constraints is a bunch of
work to be resolved. Next to that, the "normal" insert statements are
not normalized (insert into ... set foo=bar instead of insert into ...
() values ()), some missing time conversion procedures and naturally the
last insert id on MySQL, which needs an adaption on sequences in
Postgresql and Oracle.

Which means, just by changing the .sql files and the column attributes,
this won't work. Not even the connection will happen since there is no C
source code for that available via #ifdef.

Some of those mentioned things have been resolved in Icinga IDOUtils,
but not all since I had to focus on 1/ make IDOUtils more stable, less
blocking and 2/ provide initial improved Oracle support. THe Postgresql
support is quite basic, but based on libdbi it still works. In regard of
bigger monitoring environments it will lack of performance for sure.

Main reason is that the current query implementation first tries and
update, and then inserts - which basically forms the on duplicate key
insert or update from MySQL, but it's not really good causing two
queries instead of one procedure in the worst situation. An UPSERT or
MERGE procedure should replace that - sth like this:
http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
(a far more better approach would be a common rewrite with a better db
schema but that's future sound for existing database setups).

If you are planning to use NDOUtils as basis for re-implementation for
Postgresql, please be advised that the current 1.4b9 consists of some
major bugs, next to mentioned performance issues with concurrent data
inserts and housekeeping during startup and running. IDOUtils provides
an extended housekeeping thread not to interfere with the insertions.

Some blogposts on Icinga's improvements, especially on IDOUtils:

http://www.icinga.org/2009/09/01/playing-with-idoutils-and-postgresql/
http://www.icinga.org/2009/10/20/icinga-idoutils-will-support-oracle-rdbm-in-1-0-rc/
http://www.icinga.org/2010/02/17/icinga-idoutils-more-improvements-part-ii/
http://www.icinga.org/2010/06/16/news-from-core-cgis-idoutils-part-i/

Our plans are to improve Postgresql support of Icinga IDOUtils within
the next months mainly regarding the upsert procedure, but also by
dropping the current db abstraction layer (libdbi) in order to use
direct prepared statements and binded params (which is not possible with
libdbi).
This will be done right after some bigger core changes are finished,
imho not in 1.0.3 but 1.0.4 in October would be possible.

Postgresql is next to MySQL and Oracle part of RDBMS section of the
unified Icinga API (written in PHP), and provides the current Icinga
Core data source for the newly developed Icinga Web.

For more information:
http://www.icinga.org/architecture/
http://www.icinga.org/faq/icinga-vs-nagios-whats-the-difference/

That's the thing in Icinga's perspective - it's still a fork of Nagios,
but as you can see a lot of things happened lately. If Icinga can be of
help for you getting better Postgresql support with Icinga IDOUtils,
please get in touch. We'd love to work together on a satisfying solution
for you and the community :)

Kind regards,
Michael

(Icinga Core & IDOUtils Developer)

--
DI (FH) Michael Friedrich
michael(dot)friedrich(at)univie(dot)ac(dot)at
Tel: +43 1 4277 14359

Vienna University Computer Center
Universitaetsstrasse 7 A-1010 Vienna, Austria

Thank you for the time and thought.

I've added Brian Martin, who is my project lead for this effort. He's a better person to converse with than I am.

-----Original Message-----
From: Michael Friedrich [mailto:michael(dot)friedrich(at)univie(dot)ac(dot)at]
Sent: Sunday, July 18, 2010 4:35 PM
To: Duncavage, Daniel P. (JSC-OD211)
Cc: pgsql-general(at)postgresql(dot)org; Stefan Kaltenbrunner
Subject: Re: [GENERAL] NASA needs Postgres - Nagios help

Hi there,

On 2010-07-15 07:06, Stefan Kaltenbrunner wrote:
> well - there was direct database support in nagios ages ago(nagios 1.x
> is ancient) and replaced with a plugin based approach based on their
> eventbroker architecture called NDOutils. Based on tracking internal
> state it can be used to export current and historical monitoring data
> from nagios for later postprocessing (or for usin a GUI or whatever).
> NODutils however has no real working support for PostgreSQL, IDOutils
> (which I mentioned elsewhere in the thread) from the icinga fork does
> have basic support.

The SQL queries used in NDOUtils are highly MySQL specific, mostly the ON DUPLICATE KEY functionality based on unique constraints is a bunch of work to be resolved. Next to that, the "normal" insert statements are not normalized (insert into ... set foo=bar instead of insert into ...
() values ()), some missing time conversion procedures and naturally the last insert id on MySQL, which needs an adaption on sequences in Postgresql and Oracle.

Which means, just by changing the .sql files and the column attributes, this won't work. Not even the connection will happen since there is no C source code for that available via #ifdef.

Some of those mentioned things have been resolved in Icinga IDOUtils, but not all since I had to focus on 1/ make IDOUtils more stable, less blocking and 2/ provide initial improved Oracle support. THe Postgresql support is quite basic, but based on libdbi it still works. In regard of bigger monitoring environments it will lack of performance for sure.

Main reason is that the current query implementation first tries and update, and then inserts - which basically forms the on duplicate key insert or update from MySQL, but it's not really good causing two queries instead of one procedure in the worst situation. An UPSERT or MERGE procedure should replace that - sth like this:
http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
(a far more better approach would be a common rewrite with a better db schema but that's future sound for existing database setups).

If you are planning to use NDOUtils as basis for re-implementation for Postgresql, please be advised that the current 1.4b9 consists of some major bugs, next to mentioned performance issues with concurrent data inserts and housekeeping during startup and running. IDOUtils provides an extended housekeeping thread not to interfere with the insertions.

Some blogposts on Icinga's improvements, especially on IDOUtils:

http://www.icinga.org/2009/09/01/playing-with-idoutils-and-postgresql/
http://www.icinga.org/2009/10/20/icinga-idoutils-will-support-oracle-rdbm-in-1-0-rc/
http://www.icinga.org/2010/02/17/icinga-idoutils-more-improvements-part-ii/
http://www.icinga.org/2010/06/16/news-from-core-cgis-idoutils-part-i/

Our plans are to improve Postgresql support of Icinga IDOUtils within the next months mainly regarding the upsert procedure, but also by dropping the current db abstraction layer (libdbi) in order to use direct prepared statements and binded params (which is not possible with libdbi).
This will be done right after some bigger core changes are finished, imho not in 1.0.3 but 1.0.4 in October would be possible.

Postgresql is next to MySQL and Oracle part of RDBMS section of the unified Icinga API (written in PHP), and provides the current Icinga Core data source for the newly developed Icinga Web.

For more information:
http://www.icinga.org/architecture/
http://www.icinga.org/faq/icinga-vs-nagios-whats-the-difference/

That's the thing in Icinga's perspective - it's still a fork of Nagios, but as you can see a lot of things happened lately. If Icinga can be of help for you getting better Postgresql support with Icinga IDOUtils, please get in touch. We'd love to work together on a satisfying solution for you and the community :)

Kind regards,
Michael

(Icinga Core & IDOUtils Developer)

--
DI (FH) Michael Friedrich
michael(dot)friedrich(at)univie(dot)ac(dot)at
Tel: +43 1 4277 14359

Vienna University Computer Center
Universitaetsstrasse 7 A-1010 Vienna, Austria

On Jul 13, 12:10 pm, ste(dot)(dot)(dot)(at)kaltenbrunner(dot)cc (Stefan Kaltenbrunner)
wrote:
> Duncavage, Daniel P. (JSC-OD211) wrote:
>
> > We are implementingNagioson Space Station and want to use PostgreSQL
> > to store the data on orbit and then replicate that db on the ground.  
> > The problem is, most people use MySQL withNagios.  We need an addon to
> > ingestNagiosdata into PostgreSQL.  It looks like the most reasonable
> > implementation is to update the NDOUtils addon to support PostgreSQL.  
> > Does anyone have such an addon, or want to write one?
>
> Cool project :) I once did some work on adding proper PostgreSQL support
> to NDOutils but the problem is that the current code is really not too
> well structured for a "real" RDBMS(prepared statements, transactions,...)
> However thehttp://www.icinga.org/fork of NDOutils (IDOutils) does have
> some basic PostgreSQL support - maybe that will get you started.
>
>
>
> > I'm theNASAproject manager for the set of computers on Space Station
> > and we plan to deploy this capability this year.  If have to write our
> > own addon, we will, but I'd rather use something already out there.
>
> Yeah reusing code is always easier and you also don't have to maintain
> it one your own as well :)
>
> Stefan
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Try ZenOSS - http://community.zenoss.org/docs/DOC-3389

Ignore the previous link.

> NODutils however has no real working support for PostgreSQL, IDOutils (which I
>mentioned elsewhere in the thread) from the icinga fork does have basic support.

>The SQL queries used in NDOUtils are highly MySQL specific, mostly the ON
>DUPLICATE KEY functionality based on unique constraints is a bunch of work to be
>resolved. Next to that, the "normal" insert statements are not normalized
>(insert into ... set >foo=bar instead of insert into ... () values ()), some
>missing time conversion procedures and naturally the last insert id on MySQL,
>which needs an adaption on sequences in Postgresql and Oracle.

Fine, so there will be a lot of boring modifying of the src and associated
scripts (if the license permits), but "Not Supported" doesn't mean it can't be
done. It all depends on how much hacking one wants to do.

>Which means, just by changing the .sql files and the column attributes, this
>won't work. Not even the connection will happen since there is no C source code
>for that available via #ifdef.

Maybe I am reading it wrong, but nagios/ndoutils-1.4b9/src/db.c is loaded with
#ifdef USE_PGSQL connection functions. Some of the PGSQL specific functions in
ndo2db.c are commented out, but are at least there.

Sean

-------- Original Message --------
Subject: Re: [GENERAL] NASA needs Postgres - Nagios help
From: Sean E. Connolly <connollys1(at)yahoo(dot)com>
To: Michael Friedrich <michael(dot)friedrich(at)univie(dot)ac(dot)at>,
daniel(dot)p(dot)duncavage(at)nasa(dot)gov, brian(dot)d(dot)martin(at)nasa(dot)gov
Date: 2010-07-19 21:23
>
> Fine, so there will be a lot of boring modifying of the src and
> associated scripts (if the license permits), but "Not Supported"
> doesn't mean it can't be done. It all depends on how much hacking one
> wants to do.

Well depends if boring or not - more refreshing than libdbi it will be,
just like ocilib was on Oracle. I am familiar with the code, so let's
see. I've started a little research today on libpq and also prepared the
IDOUtils source for usage with libpq.

https://git.icinga.org/?p=icinga-core.git;a=shortlog;h=refs/heads/mfriedrich/pgsql

Licensing problems shouldn't happen in case of GPL on *DOUtils.

>
> Maybe I am reading it wrong, but nagios/ndoutils-1.4b9/src/db.c is
> loaded with #ifdef USE_PGSQL connection functions. Some of the PGSQL
> specific functions in ndo2db.c are commented out, but are at least there.

Yep you are right. I remembered a commit where this has been completely
dropped, but in that case it was just the configure detection and
AC_DEFINE routines. In IDOUtils this was gone, but as mentioned above,
I've re-added that and prepared the code for libpq in order to bring
this todo a bit more to reality.

Kind regards,
Michael

--
DI (FH) Michael Friedrich

Vienna University Computer Center
Universitaetsstrasse 7 A-1010 Vienna, Austria

email: michael(dot)friedrich(at)univie(dot)ac(dot)at
phone: +43 1 4277 14359
fax: +43 1 4277 14279
web: http://www.univie.ac.at/zid

Icinga Core& IDOUtils Developer
http://www.icinga.org

-------- Original Message --------
Subject: Re: [GENERAL] NASA needs Postgres - Nagios help
From: Duncavage, Daniel P. (JSC-OD211) <daniel(dot)p(dot)duncavage(at)nasa(dot)gov>
To: Michael Friedrich <michael(dot)friedrich(at)univie(dot)ac(dot)at>, Martin, Brian D.
(JSC-OD)[UNITED SPACE ALLIANCE LLC] <brian(dot)d(dot)martin(at)nasa(dot)gov>
Date: 2010-07-19 19:35
> Thank you for the time and thought.
>
> I've added Brian Martin, who is my project lead for this effort. He's a better person to converse with than I am.
>

Ok, fine. If you need anything special (e.g. on Icinga development), you
can also drop me an email offlist.

Kind regards,
Michael

> -----Original Message-----
> From: Michael Friedrich [mailto:michael(dot)friedrich(at)univie(dot)ac(dot)at]
> Sent: Sunday, July 18, 2010 4:35 PM
> To: Duncavage, Daniel P. (JSC-OD211)
> Cc: pgsql-general(at)postgresql(dot)org; Stefan Kaltenbrunner
> Subject: Re: [GENERAL] NASA needs Postgres - Nagios help
>
> Hi there,
>
> On 2010-07-15 07:06, Stefan Kaltenbrunner wrote:
>
>> well - there was direct database support in nagios ages ago(nagios 1.x
>> is ancient) and replaced with a plugin based approach based on their
>> eventbroker architecture called NDOutils. Based on tracking internal
>> state it can be used to export current and historical monitoring data
>> from nagios for later postprocessing (or for usin a GUI or whatever).
>> NODutils however has no real working support for PostgreSQL, IDOutils
>> (which I mentioned elsewhere in the thread) from the icinga fork does
>> have basic support.
>>
> The SQL queries used in NDOUtils are highly MySQL specific, mostly the ON DUPLICATE KEY functionality based on unique constraints is a bunch of work to be resolved. Next to that, the "normal" insert statements are not normalized (insert into ... set foo=bar instead of insert into ...
> () values ()), some missing time conversion procedures and naturally the last insert id on MySQL, which needs an adaption on sequences in Postgresql and Oracle.
>
> Which means, just by changing the .sql files and the column attributes, this won't work. Not even the connection will happen since there is no C source code for that available via #ifdef.
>
>
> Some of those mentioned things have been resolved in Icinga IDOUtils, but not all since I had to focus on 1/ make IDOUtils more stable, less blocking and 2/ provide initial improved Oracle support. THe Postgresql support is quite basic, but based on libdbi it still works. In regard of bigger monitoring environments it will lack of performance for sure.
>
> Main reason is that the current query implementation first tries and update, and then inserts - which basically forms the on duplicate key insert or update from MySQL, but it's not really good causing two queries instead of one procedure in the worst situation. An UPSERT or MERGE procedure should replace that - sth like this:
> http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql
> (a far more better approach would be a common rewrite with a better db schema but that's future sound for existing database setups).
>
>
> If you are planning to use NDOUtils as basis for re-implementation for Postgresql, please be advised that the current 1.4b9 consists of some major bugs, next to mentioned performance issues with concurrent data inserts and housekeeping during startup and running. IDOUtils provides an extended housekeeping thread not to interfere with the insertions.
>
>
> Some blogposts on Icinga's improvements, especially on IDOUtils:
>
> http://www.icinga.org/2009/09/01/playing-with-idoutils-and-postgresql/
> http://www.icinga.org/2009/10/20/icinga-idoutils-will-support-oracle-rdbm-in-1-0-rc/
> http://www.icinga.org/2010/02/17/icinga-idoutils-more-improvements-part-ii/
> http://www.icinga.org/2010/06/16/news-from-core-cgis-idoutils-part-i/
>
>
> Our plans are to improve Postgresql support of Icinga IDOUtils within the next months mainly regarding the upsert procedure, but also by dropping the current db abstraction layer (libdbi) in order to use direct prepared statements and binded params (which is not possible with libdbi).
> This will be done right after some bigger core changes are finished, imho not in 1.0.3 but 1.0.4 in October would be possible.
>
> Postgresql is next to MySQL and Oracle part of RDBMS section of the unified Icinga API (written in PHP), and provides the current Icinga Core data source for the newly developed Icinga Web.
>
> For more information:
> http://www.icinga.org/architecture/
> http://www.icinga.org/faq/icinga-vs-nagios-whats-the-difference/
>
>
> That's the thing in Icinga's perspective - it's still a fork of Nagios, but as you can see a lot of things happened lately. If Icinga can be of help for you getting better Postgresql support with Icinga IDOUtils, please get in touch. We'd love to work together on a satisfying solution for you and the community :)
>
> Kind regards,
> Michael
>
> (Icinga Core& IDOUtils Developer)
>
> --
> DI (FH) Michael Friedrich
> michael(dot)friedrich(at)univie(dot)ac(dot)at
> Tel: +43 1 4277 14359
>
> Vienna University Computer Center
> Universitaetsstrasse 7 A-1010 Vienna, Austria
>
>
>
>
>

--
DI (FH) Michael Friedrich

Vienna University Computer Center
Universitaetsstrasse 7 A-1010 Vienna, Austria

email: michael(dot)friedrich(at)univie(dot)ac(dot)at
phone: +43 1 4277 14359
fax: +43 1 4277 14279
web: http://www.univie.ac.at/zid

Icinga Core& IDOUtils Developer
http://www.icinga.org

From the roll-your-own side, have you looked at an alternative Nagios
event broker called livestatus? It's written by Matthias Kettner as part
of his client-centric mk-check Nagios plugin suite.

At the moment it only brokers live data (hence livestatus), but it is
intended to replace NDO as the general event broker. You can read from
the socket and do whatever you want with the data...

http://mathias-kettner.de/checkmk_livestatus.html
http://nagios.larsmichelsen.com/mklivestatus-and-nagvis-making-the-ndo-needless/

On 2010-07-19 12:23:41PM -0700, Sean E. Connolly wrote:
>
>
> > NODutils however has no real working support for PostgreSQL, IDOutils (which I
> >mentioned elsewhere in the thread) from the icinga fork does have basic support.
>
> >The SQL queries used in NDOUtils are highly MySQL specific, mostly the ON
> >DUPLICATE KEY functionality based on unique constraints is a bunch of work to be
> >resolved. Next to that, the "normal" insert statements are not normalized
> >(insert into ... set >foo=bar instead of insert into ... () values ()), some
> >missing time conversion procedures and naturally the last insert id on MySQL,
> >which needs an adaption on sequences in Postgresql and Oracle.
>
> Fine, so there will be a lot of boring modifying of the src and associated
> scripts (if the license permits), but "Not Supported" doesn't mean it can't be
> done. It all depends on how much hacking one wants to do.
>
>
> >Which means, just by changing the .sql files and the column attributes, this
> >won't work. Not even the connection will happen since there is no C source code
> >for that available via #ifdef.
>
> Maybe I am reading it wrong, but nagios/ndoutils-1.4b9/src/db.c is loaded with
> #ifdef USE_PGSQL connection functions. Some of the PGSQL specific functions in
> ndo2db.c are commented out, but are at least there.
>
> Sean
>
>
>
>

--
===========================================================
Peter C. Lai | Bard College at Simon's Rock
Systems Administrator | 84 Alford Rd.
Information Technology Svcs. | Gt. Barrington, MA 01230 USA
peter AT simons-rock.edu | (413) 528-7428
===========================================================

Peter C. Lai wrote:
> From the roll-your-own side, have you looked at an alternative Nagios
> event broker called livestatus? It's written by Matthias Kettner as part
> of his client-centric mk-check Nagios plugin suite.
>

Regarding this in reflection of this email livestatus won't make that
much sense. Earth is asking Space for some livedata, Space answers?

Duncavage, Daniel P. (JSC-OD211) wrote:
> Correct. We are looking to use Nagios to monitor various parameters on our network, then store them in postgresql, which we will then synch to the ground and distribute as a quasi realtime telemetry system.
>
>

But anyhow...

Peter C. Lai wrote:
> At the moment it only brokers live data (hence livestatus), but it is
> intended to replace NDO as the general event broker. You can read from
> the socket and do whatever you want with the data...
>

Depends on the use case. If you want something that continuously spits
out data, and stores that elsewhere, without the need of initiating the
output, you'd better use IDO (compared to NDO it has ~35% performance
increase).
If you prefer to demand data by a client application (like a web ui
e.g.), livestatus fits best and performs better. You might use
livestatus as a data poller too, but that implies bidirectional
communication and can lead into performance issues and problems.

Regarding this situation, and basically the amount of data being
generated and reworked, I would consider that NASA chose Postgresql
wisely as RDBMS - maybe even the monitoring backend depends on unified
APIs for alerting and reporting and so on. It would be interesting how
many hosts/services will be monitored and how this relates to the check
rates.

Kind regards,
Michael

--
DI (FH) Michael Friedrich

Vienna University Computer Center
Universitaetsstrasse 7 A-1010 Vienna, Austria

email: michael(dot)friedrich(at)univie(dot)ac(dot)at
phone: +43 1 4277 14359
fax: +43 1 4277 14279
web: http://www.univie.ac.at/zid

Icinga Core& IDOUtils Developer
http://www.icinga.org