Host state time was not correctly calculated #2419
Comments
|
tbh, sorting by in state is almost pointless without sorting by state first. Otherwise, you'll get a mismatch of stats dependant on time. But I can see that you may want to see what device might be constantly changing state. |
|
Do You say that this (what I see in screenshot) is expected bahavior? |
|
Nope, i'm saying that column would not normally be sortable in my mind but I'll see if I can fix it over the weekend. |
|
Ok thanks. Now I get Your point. |
|
OK, so the problem isn't with the sorting but rather the display. SELECT host.id,host.hostname, graphs, data_sources, IF(status_event_count>0, status_event_count*300, IF(UNIX_TIMESTAMP(status_rec_date)>943916400,UNIX_TIMESTAMP()-UNIX_TIMESTAMP(status_rec_date), IF(snmp_sysUptimeInstance>0 AND snmp_version > 0, snmp_sysUptimeInstance,UNIX_TIMESTAMP()))) AS instate FROM host LEFT JOIN (SELECT host_id, COUNT(*) AS graphs FROM graph_local GROUP BY host_id) AS gl ON host.id=gl.host_id LEFT JOIN (SELECT host_id, COUNT(*) AS data_sources FROM data_local GROUP BY host_id) AS dl ON host.id=dl.host_id WHERE deleted = '' GROUP BY host.id ORDER BY `instate` ASC LIMIT 0,30;
+----+-------------+--------+--------------+-----------+
| id | hostname | graphs | data_sources | instate |
+----+-------------+--------+--------------+-----------+
| 2 | 10.0.0.250 | 17 | 17 | 6114900 |
| 3 | 10.0.0.18 | 8 | 15 | 9475516 |
| 4 | 127.0.0.1 | 1 | 1 | 257353877 |
+----+-------------+--------+--------------+-----------+
3 rows in set (0.00 sec)
When I looked at the diff --git a/lib/functions.php b/lib/functions.php
index 8799cc79..62f07146 100644
--- a/lib/functions.php
+++ b/lib/functions.php
@@ -4130,7 +4130,9 @@ function calculate_percentiles($data, $percentile = 95, $whisker = false) {
function get_timeinstate($host) {
$interval = read_config_option('poller_interval');
- if ($host['status_event_count'] > 0) {
+ if (isset($host['instate'])) {
+ $time = $host['instate'];
+ } elseif ($host['status_event_count'] > 0) {
$time = $host['status_event_count'] * $interval;
} elseif (strtotime($host['status_rec_date']) > 943916400) {
$time = time() - strtotime($host['status_rec_date']);And I ended up with
So, the functions are not aligned properly. I will create a patch to rectify that. |
netniV
added a commit
that referenced
this issue
Feb 19, 2019
Host state time was not correctly calculated if snmp_sysUptimeInstance was used. The lib/functions.php was dividing the value by 100 but the SQL statement was not, resulting in invalid sort orders. Additionally, the get_timeinstate() function was updated to utilise the 'instate' element if it exists in the $hosts variable.
netniV
changed the title
|
This is now in the main development code, please check and close if issue is resolved. |
|
I will check that on 29 feb because I am in Jordan. |
|
It's good right now. |
|
Thanks @interduo |
|
I checked it before too less precisely. It's good right now calculating "in state" field but the sorting is broken as on begining.
|
|
We should sort unixtime not the text in field. |
|
The time is sorted by the value, not the display. Have you all the latest updates? |
|
I've got a commit 299f329 in my repo so it should work. |
|
If lib/function.php's get_daysfromtime() doesn't look something like: function get_daysfromtime($time, $secs = false, $pad = '', $format = DAYS_FORMAT_SHORT, $all = false) {
global $days_from_time_settings;
// Ensure we use an existing format or we'll end up with no text at all
if (!isset($days_from_time_settings['text'][$format])) {
$format = DAYS_FORMAT_SHORT;
}
$mods = $days_from_time_settings['mods'];
$text = $days_from_time_settings['text'][$format];
$result = '';
foreach ($mods as $index => $mod) {
if ($mod > 0 || $secs) {
if ($time >= $mod) {
if ($mod < 1) {
$mod = 1;
}
$val = floor($time/$mod);
$time %= $mod;
} else {
$val = 0;
}
if ($all || $val > 0) {
$result .= padleft($pad, $val, 2) . $text['prefix'] . $text[$index] . $text['suffix'];
$all = true;
}
}It isn't the latest rendition of it. |
|
It's the same. Should I clean some cache or rebuild sth? |
|
Run this: SELECT
host.id,host.hostname, graphs, data_sources,
IF(availability_method = 0,
'availability',
IF(status_event_count > 0 AND status IN (1, 2),
'status_event_count',
IF(UNIX_TIMESTAMP(status_rec_date) < 943916400 AND status IN (0, 3),
'total_polls',
IF(UNIX_TIMESTAMP(status_rec_date) > 943916400,
'status_rec_date',
IF(snmp_sysUptimeInstance>0 AND snmp_version > 0,
'sysUptimeInstance',
'current_time'
)
)
)
)
) AS method,
IF(availability_method = 0,
'0',
IF(status_event_count > 0 AND status IN (1, 2),
status_event_count*(SELECT IFNULL(value,'300') FROM settings where name = 'poller_interval'),
IF(UNIX_TIMESTAMP(status_rec_date) < 943916400 AND status IN (0, 3),
total_polls*(SELECT IFNULL(value,'300') FROM settings where name = 'poller_interval'),
IF(UNIX_TIMESTAMP(status_rec_date) > 943916400,
UNIX_TIMESTAMP() - UNIX_TIMESTAMP(status_rec_date),
IF(snmp_sysUptimeInstance>0 AND snmp_version > 0,
snmp_sysUptimeInstance/100,
UNIX_TIMESTAMP()
)
)
)
)
) AS instate
FROM host LEFT JOIN (
SELECT host_id, COUNT(*) AS graphs
FROM graph_local
GROUP BY host_id
) AS gl
ON host.id=gl.host_id
LEFT JOIN (
SELECT host_id, COUNT(*) AS data_sources
FROM data_local
GROUP BY host_id
) AS dl
ON host.id=dl.host_id
WHERE deleted = ''
GROUP BY host.id
ORDER BY `instate` ASC
LIMIT 0,30; |
|
Just realised I'd left the $poller_interval in there so it now pulls it from the settings table. |
|
|
SQL is sorting that as a string. Not a number. |
|
So the type of 'instate' is mistaken? |
|
I don't know why, but yes. |
|
Try this new one: SELECT
host.id,host.hostname, graphs, data_sources,
IF(availability_method = 0,
'availability',
IF(status_event_count > 0 AND status IN (1, 2),
'status_event_count',
IF(UNIX_TIMESTAMP(status_rec_date) < 943916400 AND status IN (0, 3),
'total_polls',
IF(UNIX_TIMESTAMP(status_rec_date) > 943916400,
'status_rec_date',
IF(snmp_sysUptimeInstance>0 AND snmp_version > 0,
'sysUptimeInstance',
'current_time'
)
)
)
)
) AS method,
CAST(
IF(availability_method = 0,
'0',
IF(status_event_count > 0 AND status IN (1, 2),
status_event_count*(SELECT IFNULL(value,'300') FROM settings where name = 'poller_interval'),
IF(UNIX_TIMESTAMP(status_rec_date) < 943916400 AND status IN (0, 3),
total_polls*(SELECT IFNULL(value,'300') FROM settings where name = 'poller_interval'),
IF(UNIX_TIMESTAMP(status_rec_date) > 943916400,
UNIX_TIMESTAMP() - UNIX_TIMESTAMP(status_rec_date),
IF(snmp_sysUptimeInstance>0 AND snmp_version > 0,
snmp_sysUptimeInstance/100,
UNIX_TIMESTAMP()
)
)
)
)
) AS unsigned
) AS instate
FROM host LEFT JOIN (
SELECT host_id, COUNT(*) AS graphs
FROM graph_local
GROUP BY host_id
) AS gl
ON host.id=gl.host_id
LEFT JOIN (
SELECT host_id, COUNT(*) AS data_sources
FROM data_local
GROUP BY host_id
) AS dl
ON host.id=dl.host_id
WHERE deleted = ''
GROUP BY host.id
ORDER BY `instate` ASC
LIMIT 0,30;This should work for you as it changes my data from to |
|
Now query result is sorted correctly by instate in expected way. |
|
I have now committed this to the latest development code. |
|
Why did You not close this issue? |
|
We must have posted around the same time so I missed yours. You can actually close your own issues if you believe them to be resolved 👍 |
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
Describe the bug
There is a broken sorting in host.php form. "In state" column.
To Reproduce
Steps to reproduce the behavior:
Expected behavior
Devices sorted properly by "In state" time.
Screenshots
Did that in first point.
Desktop:
The text was updated successfully, but these errors were encountered: