[dev.icinga.com #1880] Create sql availability function and sql queries for reporting when postgresql used for ido2db

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/1880

Created by smolnar on 2011-09-08 16:54:24 +00:00

Assignee: (none)
Status: New
Target Version: (none)
Last Update: 2015-05-01 12:50:41 +00:00 (in Redmine)

---

I'm running a full postgresql icinga deployment, and would like to start using the icinga-reporting functionality with jasperserver. Since postgres is a supported rdbm, can we get support for this in the near future? I'm also assuming that people who are running oracle would like this as well.

Attachments

• icinga_availability.sql JohnNilsson - 2013-06-26 06:06:07 +00:00
• 0001-SQL-fixes-in-templates-for-PostgreSQL.patch mbanck - 2013-06-26 10:20:30 +00:00

Changesets

2013-10-14 22:44:13 +00:00 by mbanck d7ee2e9

SQL fixes in templates for PostgreSQL

Signed-off-by: Michael Friedrich <Michael.Friedrich@netways.de>

refs #1880

2013-10-17 10:07:37 +00:00 by root b34f7c3

Added new templates to jasper package. refs #1880

---

Relations:

• relates #1880
• relates #1880
• blocks #1880

[icinga-migration]

Updated by formorer on 2012-01-06 22:15:43 +00:00

• Priority changed from Normal to Urgent

https://www.icinga.org/about/icinga-reporting/icinga-reporting-workflow/ lists MySQL, Oracle, DB2, PostgreSQL and SQLite as supported DBMs, but in fact the querys are mysql agnostic. So this needs really a fix.

[icinga-migration]

Updated by mfriedrich on 2012-02-07 00:38:20 +00:00

• Status changed from New to Assigned
• Assigned to set to berk
• Target Version set to 1.8

the webpage is fixed, now for the code.

[icinga-migration]

Updated by berk on 2012-08-15 10:12:23 +00:00

• Category set to Queries

[icinga-migration]

Updated by k0nstantin on 2012-09-25 09:32:14 +00:00

• File added activityReportInGivenTime.patch

I've tried to fix some report templates to add postgresql support and there aren't many changes to do ... basically change field type from String to Integer and so on.

For example a patch for activityReportInGivenTime report.

Are any developers working at this feature?

[icinga-migration]

Updated by berk on 2012-09-25 17:52:34 +00:00

I am working on that. The challenge is the right way for mysql and postgresql. But i am on a good way.

Best

Bernd

[icinga-migration]

Updated by mfriedrich on 2012-10-11 18:34:38 +00:00

from the beta package, i can see the icinga_availability.sql missing for pgsql translation (fails on import) and therefore most of the reports remain empty. though, need to test this on the long run the next days.

[icinga-migration]

Updated by berk on 2012-10-12 07:19:18 +00:00

• Target Version changed from 1.8 to 1.9

Hi Michi,

you are right, we do not have a function for postgresql. Working on that with Thomas but will be not available for 1.8 :-(

Best

Bernd

[icinga-migration]

Updated by mfriedrich on 2012-10-12 10:07:21 +00:00

• Priority changed from Urgent to High

better a stable reporting release 1.8 afterall. still, it's a lot of progress, so if there's more resources needed, probably community members wanting this can also help out translating the mysql function into valid postgresql.

[icinga-migration]

Updated by mfriedrich on 2013-02-14 14:42:30 +00:00

for future questions - the porting of the icinga_availibility.sql to postgresql is still required, so if anyone got a patch, this may be integrated into upstream.

[icinga-migration]

Updated by mfriedrich on 2013-03-15 09:38:23 +00:00

• Subject changed from Create sql queries for reporting when postgresql used for ido2db to Create sql availability function and sql queries for reporting when postgresql used for ido2db

[icinga-migration]

Updated by berk on 2013-04-08 14:58:29 +00:00

• Target Version changed from 1.9 to 1.10

[icinga-migration]

Updated by mfriedrich on 2013-04-29 20:48:07 +00:00

• File deleted activityReportInGivenTime.patch

[icinga-migration]

Updated by mfriedrich on 2013-04-29 20:49:18 +00:00

deleted the patch file, as it causes irritations on users asking for postgresql support.

again: https://git.icinga.org/?p=icinga-reports.git;a=blob;f=db/icinga/mysql/availability.sql;hb=refs/heads/next needs to be ported to postgresql. then there's 100% postgresql support in icinga reporting.

[icinga-migration]

Updated by JohnNilsson on 2013-06-26 06:30:34 +00:00

• File added icinga_availability.sql

I've implemented a version for PostgreSQL 8.4 (attached)

It's optimized for debuggability rather than speed. If that becomes an issue there are probably a few things that can be done.

Not being an expert in either PostgreSQL or Icinga I can't really claim that it does the right thing, it's based entierly on my interpretation of what the MySQL version tries to do. So if anyone more knowledgeable about the icinga datamodel and asumptions could chime in with an analysis that would be helpful.

Some implementation notes:

1. I'm assuming that icinga_downtimehistory takes presedense over icinga_statehistory so all statehistory events that could be in conflict is filtered out.

2. The original MySQL-script talks about a bug in icinga rendering last_hard_state unreliable, and thus forces an analysis of all soft_states. I've focused entierly on hard states, but instead try to take all history in account to avoid using either last_state or last_hard_state, nor sure if this is a viable approach, or even if the original bug still needs a workaround.

3. The original script falls back to current state if the state history for the period is empty. By using the entire history as input (not only for the period) I'm thinking that there shouldn't be a need to inspect current state.

Now just fixing this script turns out to not be enough, even with this function added I'm still having issues with the report. I'm assuming that it is the same issues mentioned by k0nstantin.

Error during report execution
net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Host availability sub report in given time
…
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Host availability sub report in given time
…
Caused by: org.postgresql.util.PSQLException: ERROR: column "second" does not exist

and

2013-03-23 00:50:57,119 ERROR JRQueryExecuterAdapter,http-8080-6:113 - Error while executing query
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : query_service_object_id
...
Caused by: org.postgresql.util.PSQLException: ERROR: function concat(text, unknown, text) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 27

[icinga-migration]

Updated by mbanck on 2013-06-26 10:22:00 +00:00

• File added 0001-SQL-fixes-in-templates-for-PostgreSQL.patch

Some time ago I fixed up a couple of queries for PostgreSQL and tried to verify that the queries are still working in MySQL.

I now checked that the changes still apply to last git master, so I am attaching them here.

[icinga-migration]

Updated by mfriedrich on 2013-09-16 18:07:40 +00:00

JohnNilsson wrote:

I've implemented a version for PostgreSQL 8.4 (attached)

It's optimized for debuggability rather than speed. If that becomes an issue there are probably a few things that can be done.

Not being an expert in either PostgreSQL or Icinga I can't really claim that it does the right thing, it's based entierly on my interpretation of what the MySQL version tries to do. So if anyone more knowledgeable about the icinga datamodel and asumptions could chime in with an analysis that would be helpful.

Some implementation notes:

i'm not the author of the availability.sql nor a mysql expert, so my questions my sound as dumb as they look. i'm interested in 2 things:

• keep the behaviour of both, mysql and postgresql the same
• maintain both upstream in case of updates/bugfixes

1. I'm assuming that icinga_downtimehistory takes presedense over icinga_statehistory so all statehistory events that could be in conflict is filtered out.

dumb question - is that the same behaviour in mysql version?

2. The original MySQL-script talks about a bug in icinga rendering last_hard_state unreliable, and thus forces an analysis of all soft_states. I've focused entierly on hard states, but instead try to take all history in account to avoid using either last_state or last_hard_state, nor sure if this is a viable approach, or even if the original bug still needs a workaround.

Hmmm I'd prefer the same behaviour as mysql does, but I am not sure which impact that might have here. I'll forward the question to Mr. Gelf too.

3. The original script falls back to current state if the state history for the period is empty. By using the entire history as input (not only for the period) I'm thinking that there shouldn't be a need to inspect current state.

I'd revert that to the mysql behaviour, and mark this as extra bug if possible.

mbanck wrote:

Some time ago I fixed up a couple of queries for PostgreSQL and tried to verify that the queries are still working in MySQL.

I now checked that the changes still apply to last git master, so I am attaching them here.

Thanks, so the patch contains basically 2 fixes

• the string vs timestamp issue still happening in some reports
• the group by clause must contain the times (postgresql only, afaik that bites icinga web in one of the last versions)

[icinga-migration]

Updated by JohnNilsson on 2013-09-16 19:16:18 +00:00

First of all, since writing this we haven't actually used it for anything, and I haven't been, and probably won't be, involved with anything related at all. So my memory and attention devoted to this is somewhat limited.

dnsmichi wrote:

* keep the behaviour of both, mysql and postgresql the same
* maintain both upstream in case of updates/bugfixes
I'm not sure that is even possible. The MySQL version relies heavliy on setting variables to various stated during the query execution. A feature not available to most SQL-implementations.

If this style of processing is desired I would suggest that it is implemented using some procedural, or event-/signal centric, mechanism instead of SQL. SQL isn't a great fit for eventprocessing at all. Could RRD handle it?

> 1. I'm assuming that icinga_downtimehistory takes presedense over icinga_statehistory so all statehistory events that could be in conflict is filtered out.

dumb question - is that the same behaviour in mysql version?

To be honest, I really don't remember. But I think not.

> 3. The original script falls back to current state if the state history for the period is empty. By using the entire history as input (not only for the period) I'm thinking that there shouldn't be a need to inspect current state.

I'd revert that to the mysql behaviour, and mark this as extra bug if possible.
Should be easy enough to add if desired.

BR,
John

[icinga-migration]

Updated by mfriedrich on 2013-10-14 22:43:12 +00:00

long story short - Thomas did have a look at it, and it unveiled that the function does stuff different than the intended behavior would be.

though, the mysql procedure has been refactored to allow easier porting. details on that in #4874

[icinga-migration]

Updated by mfriedrich on 2013-10-14 22:45:35 +00:00

i've applied the "small" fixes patch from https://dev.icinga.org/issues/1880#note-15 to git 'next' for testing.

[icinga-migration]

Updated by mfriedrich on 2013-10-16 14:50:13 +00:00

• Target Version changed from 1.10 to 1.11

so to summarize

• we need a new sla availability procedure for mysql and postgresql (first mysql in #4874 , then postgresql)
• the other fixes have been applied into the 1.10 tree
• if anyone is willing to help porting the new mysql sla procedure 1:1 to postgres then please either attach a patch here, or query us at info@icinga.org
• currently the team is low on reporting ressources / team members working on it. web recently required more attention.

being the release manager for icinga 1.10 again i'll reschedule it yet again (you may blame me, if you ever overrule my git commit count on icinga).

[icinga-migration]

Updated by mfriedrich on 2013-10-16 14:50:20 +00:00

• Done % changed from 0 to 20

[icinga-migration]

Updated by mfriedrich on 2014-03-13 09:22:38 +00:00

• Target Version deleted 1.11

[icinga-migration]

Updated by viggifusi on 2014-08-14 09:48:25 +00:00

Hi all,
is there any hope for this issue to be resolved, at least for Icinga 2 ?
Is there anything that I could do to help ?
Best regards

[icinga-migration]

Updated by berk on 2014-09-05 08:08:46 +00:00

• Assigned to changed from berk to mfrosch

[icinga-migration]

Updated by jszaszvari on 2015-04-02 02:50:59 +00:00

Hi All

Just following up on the comments above.

Is there any plans to get this working for icinga2?

Do we have working postgres sql files to import for this yet? The ones above are years old.

Thanks
John

[icinga-migration]

Updated by mfriedrich on 2015-04-03 16:31:56 +00:00

• Blocked set to 4874

[icinga-migration]

Updated by mfriedrich on 2015-05-01 12:50:41 +00:00

• Status changed from Assigned to New
• Assigned to deleted mfrosch

[kajla]

Any news?

Thanks!

[dnsmichi]

This won't happen for this project anytime soon.