search

Icinga / icinga-web

Icinga Web 1.x, the old new web interface (EOL 31.12.2018)
22 stars 11 forks source link

[dev.icinga.com #1903] oracle: total count status cronk vs performance cronk is wrong #499

Closed icinga-migration closed 12 years ago

icinga-migration commented 13 years ago

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

Created by mfriedrich on 2011-09-15 14:27:56 +00:00

Assignee: jmosshammer Status: Closed (closed on 2011-11-30 15:33:13 +00:00) Target Version: 1.6 Last Update: 2011-12-03 11:30:15 +00:00 (in Redmine)

using icinga-web 1.5.2 r1.5 git, 7990db3b3e4372c3d1126dcf4c6ed0f114aa0a85

this is a combined error report, see the attached screenshot.

1) total counts for services in status cronk is different from performance cronk

2) instance status tells 822 minutes and being down too. calculating this will be 13,7 hours, and mostly 13:47 server time. so somehow hh:mm:ss are not calculated in this difference and therefore showing wrong output.

3) last check only shows the date correctly, but not the time (00:00:00). this could be the same as 2) just not getting the hh:mm:ss and just showing 0 instead. only happens with oracle, probably missing correct time formatting function(ality)

icinga_web_1.5.2_oracle_wrong_counts_hh_mm_ss_always_0_instance_status_data_calculated_wrong.png

Attachments

Changesets

2011-11-15 03:02:13 +00:00 by jmosshammer 5e32c8119c1b2203c87b42c6ccb5030f4c2eec3c

* Fixed tackle pressed issue, fixed performance data (shows active,passive,disabled like in the cgis), fixed active objects not being
shown (fixes #1589, refs #1903)

2011-11-25 15:36:17 +00:00 by jmosshammer e95efe984aa193ef6a2a41b64780a9e5a825602c

* Fixed different counts in status cronks when status entries are missing (refs #1903)
icinga-migration commented 13 years ago

Updated by jmosshammer on 2011-09-20 06:48:15 +00:00

What is the time_format in the database.xml?

icinga-migration commented 13 years ago

Updated by mfriedrich on 2011-09-20 06:52:14 +00:00

the default. i don't change anything on test installs in order to proof the "5 minutes install" right.

icinga-migration commented 13 years ago

Updated by jmosshammer on 2011-09-20 07:20:39 +00:00

what does icinga-web/web/api/host/columns[HOST_NAME|HOST_LAST_CHECK]/limit[ 5 ]/xml (remove the spaces in the braces of limit, just for avoiding wiki formatting issues) return?

icinga-migration commented 13 years ago

Updated by mfriedrich on 2011-09-20 14:32:03 +00:00

example queries how the web does it would be better - i can only build those from my personal understandings. (wiki article - how to test icinga web)

total counts

> select count(*) from hoststatus join objects on hoststatus.host_object_id=objects.id where objects.is_active=1;

             209

> select count(*) from servicestatus join objects on servicestatus.service_object_id=objects.id where objects.is_active=1;

            4011

so the service counter on the performance cronk is wrong (or differently selected). because even the configs do reflect the 4011 count.

> select count(*) from services join objects on services.service_object_id=objects.id where objects.is_active=1;

            4011

if pending is really the has_been_checked=0 directive, this is also correct.

> select count(*) from servicestatus join objects on servicestatus.service_object_id=objects.id where has_been_checked=0 and objects.is_active=1;

               0

> select count(*) from hoststatus join objects on hoststatus.host_object_id=objects.id where has_been_checked=0 and objects.is_active=1; 
               2

and

select count(*) from hoststatus join objects on hoststatus.host_object_id=objects.id where hoststatus.current_state=1 and objects.is_active=1;

and further also reflect the correct counts in the status cronk.

on the timestamp written into the database. the database works with utc while the rdbms server systime is gmt+2 currently.

190 332 2   4213    20-Sep-2011 13:51:19    test_host_044 (checked by icinga-dev) OK: ok hostcheck      (CLOB)  0   1   1   1   5   20-Sep-2011 13:51:18    20-Sep-2011 15:51:19    0   20-Sep-2011 13:36:02    20-Sep-2011 4:40:13 0   20-Sep-2011 13:51:19    5-Aug-2011 4:39:23  20-Sep-2011 12:51:18    1   1-Jän-1970  1-Jän-1970  0   1   0   0   0   1   1   1   1   1   27,03947    0,138   0,04862 0   1   1   1   0       check-host-alive-parent!up!$HOSTSTATE:test_router_2$    120 60  5   4213    2   1   test_host_044       1

the presentation host uses also gmt+2, which is presented in the screenshot.

so the data in the database is utc, while the servers time is gmt+2 - funny ;-)

this would require a config option to be changed, right?

the locale is

dbserver locale LANG=de_AT.UTF-8

$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P15

returned value

test_host_18820-SEP-11test_host_18920-SEP-11test_host_19020-SEP-11test_host_19120-SEP-11test_host_19220-SEP-11

/etc/icinga-web/databases.xml

        icingaOracle://bla:foo@foo.com:1521/ICINGADB

        utf8
         true

            CONSERVATIVE

        %core.module_dir%/Api/lib/database/models/generated
        %core.module_dir%/Api/lib/database/models                          

            false
            apc
            true
icinga-migration commented 13 years ago

Updated by mfriedrich on 2011-09-20 15:24:14 +00:00

adding to databases.xml

YYYY-MM-DD HH24:MI:SS

now resolves the instances cronk issue. furthermore the cronk data is also shown correctly.

icinga_web_1.5.2_datetime_issues_RESOLVED.png

the counts on the performance cronk would possibly be a wrong query ...

icinga-migration commented 13 years ago

Updated by mhein on 2011-09-27 11:57:45 +00:00

icinga-migration commented 13 years ago

Updated by pdeneu on 2011-11-10 17:22:53 +00:00

michi is right, it resolves the date / time problem but not the wrong count...

icinga-migration commented 12 years ago

Updated by jmosshammer on 2011-11-15 14:00:21 +00:00

icinga-migration commented 12 years ago

Updated by jmosshammer on 2011-11-18 12:26:52 +00:00

I updated the queries for the status summary (added is_active).

Does this issue still occur in the current master?

icinga-migration commented 12 years ago

Updated by mfriedrich on 2011-11-18 12:34:28 +00:00

i need to test that next week, i'll keep you posted, thanks.

icinga-migration commented 12 years ago

Updated by mfriedrich on 2011-11-23 16:09:16 +00:00

hm, given the query for e.g. all active hosts ...

SQL idotest@icingadb> select count(*) from hosts join objects on hosts.host_object_id=objects.id where is_active=1 group by hosts.instance_id;

             213

Elapsed: 00:00:00.01

icinga_web_1.5.2_wrong_counts_also_with_is_active.png

icinga-migration commented 12 years ago

Updated by jmosshammer on 2011-11-23 17:49:50 +00:00

Could you run this test query and tell me the results, please?

SELECT DISTINCT i.current_state AS STATE, i.current_state AS STATE, COUNT (DISTINCT i.host_object_id) AS HOST_COUNT, (i.has_been_checked-i.should_be_scheduled)*-1 AS IS_PENDING, i.scheduled_downtime_depth AS IN_DOWNTIME, i.problem_has_been_acknowledged AS IS_ACKED FROM hoststatus i INNER JOIN icinga_objects i2 ON i.host_object_id = i2.id AND (i2.is_active = 1) INNER JOIN icinga_hosts i3 ON i2.id = i3.host_object_id WHERE (i3.config_type = 1) GROUP BY (i.has_been_checked-i.should_be_scheduled)*-1, i.current_state, i.scheduled_downtime_depth, i.problem_has_been_acknowledged

thx!

icinga-migration commented 12 years ago

Updated by mfriedrich on 2011-11-23 17:55:34 +00:00

SQL idotest@icingadb> set lin 1600;
SELECT  DISTINCT i.current_state AS STATE,
 i.current_state AS STATE,
 COUNT(DISTINCT i.host_object_id) AS HOST_COUNT,
 (i.has_been_checked-i.should_be_scheduled)*-1 AS IS_PENDING,
 i.scheduled_downtime_depth AS IN_DOWNTIME,
 i.problem_has_been_acknowledged AS IS_ACKED
 FROM hoststatus i 
 INNER JOIN objects i2 ON 2 ON 
i.host_object_id = i2.id AND (i2.is_active = 1) 
 INNER JOIN hosts i3 ON 3 ON 
i2.id = i3.host_object_id WHERE (i3.config_type = 1) 
 GROUP BY (i.has_been_checked-i.should_be_scheduled)*-1,
 i.current_state,
 i.scheduled_downtime_depth,
 15   i.problem_has_been_acknowledged;

               2                2                2                0                0                1
               1                1                1                0                0                1
               0                0                2                1                0                0
               0                0              173                0                0                0
               2                2               31                0                0                0

Elapsed: 00:00:00.00
icinga-migration commented 12 years ago

Updated by mfriedrich on 2011-11-23 18:24:59 +00:00

reordering the grouping by moving the current_state above, tells the following

SELECT  DISTINCT i.current_state AS STATE,
 i.current_state AS STATE,
 COUNT(DISTINCT i.host_object_id) AS HOST_COUNT,
 (i.has_been_checked-i.should_be_scheduled)*-1 AS IS_PENDING,
 i.scheduled_downtime_depth AS IN_DOWNTIME,
 i.problem_has_been_acknowledged AS IS_ACKED
 FROM hoststatus i 
 INNER JOIN objects i2 ON 2 ON 
i.host_object_id = i2.id AND (i2.is_active = 1) 
 INNER JOIN hosts i3 ON 3 ON 
i2.id = i3.host_object_id WHERE (i3.config_type = 1) 
 GROUP BY  i.current_state,
(i.has_been_checked-i.should_be_scheduled)*-1,
 i.scheduled_downtime_depth,
 15   i.problem_has_been_acknowledged;

               2                2                2                0                0                1
               1                1                1                0                0                1
               1                1                2                0                0                0
               0                0              139                0                0                0
               0                0                2                1                0                0
               2                2               63                0                0                0

6 rows selected.

Elapsed: 00:00:00.00
icinga-migration commented 12 years ago

Updated by mfriedrich on 2011-11-23 18:25:11 +00:00

icinga-migration commented 12 years ago

Updated by mfriedrich on 2011-11-23 18:27:44 +00:00

changing the INNER JOIN to JOIN, LEFT JOIN, RIGHT JOIN has no effect. so yes, somehow the grouping in that query.

icinga-migration commented 12 years ago

Updated by mhein on 2011-11-30 15:33:13 +00:00

Fixed by JM, I'll resolve that issue.

icinga-migration commented 12 years ago

Updated by mfriedrich on 2011-12-03 11:30:15 +00:00