search

Icinga / icinga-reports

Icinga Reports 1.x MySQL (EOL 31.12.2018)
GNU General Public License v2.0
14 stars 13 forks source link

[dev.icinga.com #3051] jasperserver 4.7.0 not working properly #29

Closed icinga-migration closed 12 years ago

icinga-migration commented 12 years ago

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

Created by mfriedrich on 2012-09-03 13:27:04 +00:00

Assignee: berk Status: Resolved (closed on 2012-10-08 16:10:24 +00:00) Target Version: 1.8 Last Update: 2012-10-08 16:10:23 +00:00 (in Redmine)

Icinga Version: 1.7.2
DB Type: MySQL
DB Version: 1.7.0
Jasper Version: 4.7.0

there's been a thread on the portal about that topic, and the final user report tells - 4.7.0 breaks reporting, 4.5.0 works.

http://www.monitoring-portal.org/wbb/index.php?page=Thread&postID=176771#post176771

this should be tested before 1.8 release date, it might be the case that jaspersoft changed various things.

it seems that various datatypes are interpreted as null values, as this query indicates.

select name1,address from icinga_objects,  icinga_hosts where icinga_objects.object_id = icinga_hosts.host_object_id and object_id                  = null

Attachments

Relations:

icinga-migration commented 12 years ago

Updated by mfriedrich on 2012-09-26 15:46:08 +00:00

possible problem from the release notes:

Upgrading Input Controls to 4.7.0
---------------------------------

Prior to release 4.7, query-based input controls of type string
were allowed for queries that returned dates. As of release 4.7,
the type of the input control must match exactly the type of
the field returned by the query. You must manually update your
query-based input controls to use only controls of type date for
date values.

Query-based input controls also support the $X{} syntax now to
handle equality comparisons with null values as input. The
following query will fail when the value of  is null:

  select * where  = $P{} 

Instead, use the following syntax:

  select * where $X{EQUAL, , }

The $X{EQUAL...} syntax will generate the correct SQL to compare
the value of the parameter when it is null. For any other types
of comparison, such as greater than, use the $P{} syntax.
icinga-migration commented 12 years ago

Updated by mfriedrich on 2012-09-26 18:13:13 +00:00

https://wiki.icinga.org/display/howtos/Setting+up+Icinga+with+Reporting#SettingupIcingawithReporting-Logging

2012-09-26 20:07:40,163 DEBUG JRJdbcQueryExecuter,pool-1-thread-1:260 - SQL query string: select name1,
  address
from icinga_objects,
  icinga_hosts
where icinga_objects.object_id = icinga_hosts.host_object_id
and icinga_hosts.config_type = 1
and object_id                  = ?
2012-09-26 20:07:40,195 DEBUG JRJdbcQueryExecuter,pool-1-thread-1:362 - Parameter #1 (p_host_object_id of type java.lang.String): null
icinga-migration commented 12 years ago

Updated by mfriedrich on 2012-09-26 18:26:25 +00:00

like proposed - does not help either.

diff --git a/jasperreports/templates/main/host/availabilityReportInGivenTime.jrxml b/jasperreports/templates/main/host/availabilityReportInGivenTime.jrxml
index 056b50b..0da900c 100644
--- a/jasperreports/templates/main/host/availabilityReportInGivenTime.jrxml
+++ b/jasperreports/templates/main/host/availabilityReportInGivenTime.jrxml
@@ -29,7 +29,8 @@ from icinga_objects,
   icinga_hosts
 where icinga_objects.object_id = icinga_hosts.host_object_id
 and icinga_hosts.config_type = 1
-and object_id                  = $P{p_host_object_id}]]>
+and $X{EQUAL, object_id, p_host_object_id}
+]]>

as well as i suspect that host_object_id is the wrong type (string versus integer).

icinga-migration commented 12 years ago

Updated by mfriedrich on 2012-09-26 19:55:36 +00:00

ok. now that i know that 'make install' does not invoke an install of all the edited jrxml files, but only calls the buildomatic import of the existing zip file laying around, i can stop to wonder why my git changes where not reflected in any report i tried to do.

i also figured, that the only way of importing new jrxml seems to be the webinterface itsself, where you'll upload the changed jrxmls, test around, and once done, you'll export it to safety.

the general cause for 4.7.0 not working is the string vs integer problem - i have no idea, why input *object_ids are treated as String but not Integer, but jasperserver 4.7.0 interpretes such als null values - which creates the funny queries, and empty reports.

the first query happening, when you get to see the host, timeperiod, start/endtime inputs popup, looks like this

2012-09-26 21:01:10,080 DEBUG JRJdbcQueryExecuter,http-8081-3:260 - SQL query string: select host_object_id, display_name from icinga_hosts

so saving this as Integer, rather than String makes sense either way (otherwise some strange casting would possibly happen, which is not working anymore with jasperreports 4.7.0)

my idea is to first grep all host_object_id and service_object_id locations, and then sed all over the place, replacing the String with Integer.

find . -type f -print0 | xargs -0 sed -i 's/name="host_object_id" class="java.lang.String/name="host_object_id" class="java.lang.Integer/g' 
find . -type f -print0 | xargs -0 sed -i 's/name="p_host_object_id" class="java.lang.String/name="p_host_object_id" class="java.lang.Integer/g' 

find . -type f -print0 | xargs -0 sed -i 's/name="service_object_id" class="java.lang.String/name="service_object_id" class="java.lang.Integer/g'
find . -type f -print0 | xargs -0 sed -i 's/name="p_service_object_id" class="java.lang.String/name="p_service_object_id" class="java.lang.Integer/g'

after that, manually import all files via web browser to jasperserver.

to finally export a working tree from jasperserver:

/opt/jasperreports-server-cp-4.7.0/buildomatic/js-export.sh --uris /icinga --output-zip /home/michi/coding/icinga/icinga-reports/jasperreports/package/js-icinga-reports.zip

with 4.7.0, i've tested availibity report in given time for now.

icinga_reporting_1.8_4.7.0_availreport_host.png

in order to stay sane, another check with jasperserver 4.5.0

sol /opt/jasperreports-server-cp-4.7.0 # ./ctlscript.sh stop
sol /opt/jasperreports-server-cp-4.7.0 # cd ../jasperreports-server-cp-4.5.0/
sol /opt/jasperreports-server-cp-4.5.0 # ./ctlscript.sh start

works as well!

icinga_reporting_1.8_4.5.0_availreport_host.png

so my theory is - passing numbers as strings around is not safe anymore (wasn't safe at all).

@Bernd This applies to "p_object_id", "p_hostgroup_object_id", "p_servicegroup_object_id" as well, but this is a bit inconsistent over the place. A sed here as well helps too.

$ find . grep -r _object_id * | grep String

so basically the superduper fix will be

find . -type f -print0 | xargs -0 sed -i 's/name="host_object_id" class="java.lang.String/name="host_object_id" class="java.lang.Integer/g' 
find . -type f -print0 | xargs -0 sed -i 's/name="p_host_object_id" class="java.lang.String/name="p_host_object_id" class="java.lang.Integer/g' 

find . -type f -print0 | xargs -0 sed -i 's/name="service_object_id" class="java.lang.String/name="service_object_id" class="java.lang.Integer/g'
find . -type f -print0 | xargs -0 sed -i 's/name="p_service_object_id" class="java.lang.String/name="p_service_object_id" class="java.lang.Integer/g' 

find . -type f -print0 | xargs -0 sed -i 's/name="p_object_id" class="java.lang.String/name="p_object_id" class="java.lang.Integer/g' 

find . -type f -print0 | xargs -0 sed -i 's/name="p_hostgroup_object_id" class="java.lang.String/name="p_hostgroup_object_id" class="java.lang.Integer/g'
find . -type f -print0 | xargs -0 sed -i 's/name="p_servicegroup_object_id" class="java.lang.String/name="p_hostgroup_object_id" class="java.lang.Integer/g'

;-)

icinga-migration commented 12 years ago

Updated by mfriedrich on 2012-09-26 20:09:48 +00:00

icinga-migration commented 12 years ago

Updated by mfriedrich on 2012-09-26 20:09:56 +00:00

icinga-migration commented 12 years ago

Updated by mfriedrich on 2012-09-27 10:43:30 +00:00

attached is a patch, though i dunno how to bulk import that into jasperserver, to generate a new js-icinga-reports.zip package for make install.

icinga-migration commented 12 years ago

Updated by mfriedrich on 2012-10-08 16:10:24 +00:00

https://git.icinga.org/?p=icinga-reports.git;a=commit;h=f3744451497acbc80e3571d7e75a74e489fa64e9