Level 1 users not seeing alert acknowledge button

Tags: #<Tag:0x00007f064222b380>

Starting recently, our level 1 users no longer see the acknowledge button for their alerts and we see the following in the log…

Blockquote
[2020-01-14 12:13:16] production.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘D.device_id’ i
n ‘where clause’ (SQL: SELECT COUNT(alerts.id) FROM alerts LEFT JOIN devices ON alerts.device_id=
devices.device_id LEFT JOIN locations ON devices.location_id = locations.id RIGHT JOIN alert_r ules ON alerts.rule_id=alert_rules.id WHERE devices.disabled = 0 AND alerts.state!=0 AND D
.device_id IN (72,75,76,77,78,83,84,85,23,24,25,58,59,60,61,63,64,156,65,66,80,266,269,270,271,272,277,283,3
06,307)) (SQL: SELECT COUNT(alerts.id) FROM alerts LEFT JOIN devices ON alerts.device_id=devices .device_id LEFT JOIN locations ON devices.location_id = locations.id RIGHT JOIN alert_rules ON
alerts.rule_id=alert_rules.id WHERE devices.disabled = 0 AND alerts.state!=0 AND D.device _id IN (72,75,76,77,78,83,84,85,23,24,25,58,59,60,61,63,64,156,65,66,80,266,269,270,271,272,277,283,306,307))
#0 /data/librenms/includes/html/table/alerts.inc.php(79): dbFetchCell(‘SELECT COUNT(a...', Array) #1 /data/librenms/html/ajax_table.php(40): include_once('/data/librenms/...') #2 {main} [2020-01-14 12:13:16] production.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'D.device_id' i n 'where clause' (SQL: SELECT alerts.*, devices.hostname, devices.sysName, devices.hardware, lo
cations.location, alert_rules.rule, alert_rules.name, alert_rules.severity FROMalertsLEFT JOINdevicesONalerts.device_id=devices.device_idLEFT JOINlocationsONdevices.location_id =locations.id RIGHT JOINalert_rulesONalerts.rule_id=alert_rules.idWHERE devices.disabled= 0 ANDalerts.state!=0 AND D.device_idIN (72,75,76,77,78,83,84,85,23,24,25,58,59,60,61,63,64,156,65,66,80,266,269,270,271,272,277,283,306,307) ORDER BY timestamp DESC LIMIT 0,50) (SQL: SELECTalerts.*, devices.hostname, devices.sysName, devices.hardware, locations.location, alert_rules.rule, alert_rules.name, alert_rules.severity FROMalertsLEFT JOINdevicesONalerts.device_id=devices.device_idLEFT JOINlocationsONdevices.location_id=locations.id RIGHT JOINalert_rulesONalerts.rule_id=alert_rules.idWHERE devices.disabled= 0 ANDalerts.state!=0 AND D.device_idIN (72,75,76,77,78,83,84,85,23,24,25,58,59,60,61,63,64,156,65,66,80,266,269,270,271,272,277,283,306,307) ORDER BY timestamp DESC LIMIT 0,50)#0 /data/librenms/includes/html/table/alerts.inc.php(105): dbFetchRows('SELECTalerts`…’, Array)
#1 /data/librenms/html/ajax_table.php(40): include_once(’/data/librenms/…’)
#2 {main}

Blockquote

@louis can you check this out?

@JohnSPeach could you please copy validate.php ?

1 Like

@louis
Sorry about that
indent preformatted text by 4 spaces====================================

Component Version
LibreNMS 1.59-25-gee8297df1
DB Schema 2020_01_09_1300_migrate_devices_attribs_table (153)
PHP 7.2.19-0ubuntu0.18.04.2
MySQL 10.1.41-MariaDB-0ubuntu0.18.04.1
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

====================================

[OK] Composer Version: 1.9.2
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct

I have the same issue, except that no alerts are listed for users who are set to Normal level and have specific devices listed. The below error is produced each time a user refreshes the alert page.

[2020-01-15 16:44:10] production.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'D.device_id' in 'where clause' (SQL: SELECT `alerts`.*, `devices`.`hostname`, `devices`.`sysName`, `devices`.`hardware`, `locations`.`location`, `alert_rules`.`rule`, `alert_rules`.`name`, `alert_rules`.`severity`  FROM `alerts` LEFT JOIN `devices` ON `alerts`.`device_id`=`devices`.`device_id` LEFT JOIN `locations` ON `devices`.`location_id` = `locations`.`id`  RIGHT JOIN `alert_rules` ON `alerts`.`rule_id`=`alert_rules`.`id` WHERE  `devices`.`disabled` = 0 AND `alerts`.`state`!=0 AND `D`.`device_id` IN (14,5,17,18,15,2,7,12,16,3,13,19,71,76,77,80,82,53,69,54,66,68,64,83,28,27,63,34,31,26,44,47,67,59,60,56,45,51,65,41,42,48,58,36,29,57,70,37,23,33,79,24,52,55,84,87,91,90,89,136,137,143,140,144,184,191,187,186,194,85,185,204,202,39,206,212,214,38,223,30,20,225,226,228,230,40) ORDER BY timestamp DESC LIMIT 0,50) (SQL: SELECT `alerts`.*, `devices`.`hostname`, `devices`.`sysName`, `devices`.`hardware`, `locations`.`location`, `alert_rules`.`rule`, `alert_rules`.`name`, `alert_rules`.`severity`  FROM `alerts` LEFT JOIN `devices` ON `alerts`.`device_id`=`devices`.`device_id` LEFT JOIN `locations` ON `devices`.`location_id` = `locations`.`id`  RIGHT JOIN `alert_rules` ON `alerts`.`rule_id`=`alert_rules`.`id` WHERE  `devices`.`disabled` = 0 AND `alerts`.`state`!=0 AND `D`.`device_id` IN (14,5,17,18,15,2,7,12,16,3,13,19,71,76,77,80,82,53,69,54,66,68,64,83,28,27,63,34,31,26,44,47,67,59,60,56,45,51,65,41,42,48,58,36,29,57,70,37,23,33,79,24,52,55,84,87,91,90,89,136,137,143,140,144,184,191,187,186,194,85,185,204,202,39,206,212,214,38,223,30,20,225,226,228,230,40) ORDER BY timestamp DESC LIMIT 0,50)#0 /data/librenms/includes/html/table/alerts.inc.php(105): dbFetchRows('SELECT `alerts`...', Array)
#1 /data/librenms/html/ajax_table.php(40): include_once('/data/librenms/...')
#2 {main}

====================================
Component | Version
--------- | -------
LibreNMS  | 1.59-25-gee8297d
DB Schema | 2020_01_09_1300_migrate_devices_attribs_table (153)
PHP       | 7.2.24
MySQL     | 5.5.64-MariaDB
RRDTool   | 1.6.0
SNMP      | NET-SNMP 5.7.2
====================================

Hello

I have a patch a patch.

@Kevin_Krumm please merge it.

1 Like

@InsaneSplash @JohnSPeach

Please test the patch
./scripts/github-apply 11034

To revert - otherwise daily upgrade won’t be applied
./scripts/github-remove -d

2 Likes

@louis Looks like its working fine after the patch!

I currently don’t have any alerts - I’ve asked to see if one can be generated for testing…

Yes, it works here too, thank-you.

Fix has been merged into the code base.

@JohnSPeach please revert the change and run daily.sh to get last update

./scripts/github-remove -d
./daily.sh

@louis Thank-you - already done!