Level 1 users not seeing alert acknowledge button

JohnSPeach · 14 January 2020 17:19

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

Kevin_Krumm · 15 January 2020 14:14

@louis can you check this out?

louis · 15 January 2020 14:20

@JohnSPeach could you please copy validate.php ?

JohnSPeach · 15 January 2020 14:34

@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

InsaneSplash · 15 January 2020 14:45

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
====================================

louis · 15 January 2020 15:00

Hello

I have a patch a patch.

@Kevin_Krumm please merge it.

github.com/librenms/librenms

Fix alerts not displayed

librenms:master ← louis-6wind:fix-alerts-not-displayed

opened 02:59PM - 15 Jan 20 UTC

louis-6wind

+1 -1

Fix following error : ``` [2020-01-14 12:13:16] production.ERROR: SQLSTATE[42S…22]: 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)) ``` https://community.librenms.org/t/level-1-users-not-seeing-alert-acknowledge-button/10637 Side effect of PR https://github.com/librenms/librenms/pull/10568 DO NOT DELETE THIS TEXT #### Please note > Please read this information carefully. You can run `./scripts/pre-commit.php` to check your code before submitting. - [X] Have you followed our [code guidelines?](http://docs.librenms.org/Developing/Code-Guidelines/) #### Testers If you would like to test this pull request then please run: `./scripts/github-apply <pr_id>`, i.e `./scripts/github-apply 5926` After you are done testing, you can remove the changes with `./scripts/github-remove`. If there are schema changes, you can ask on discord how to revert.

louis · 15 January 2020 15:08

@InsaneSplash @JohnSPeach

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

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

InsaneSplash · 15 January 2020 15:25

@louis Looks like its working fine after the patch!

JohnSPeach · 15 January 2020 15:26

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

JohnSPeach · 15 January 2020 15:35

Yes, it works here too, thank-you.

Kevin_Krumm · 15 January 2020 16:13

Fix has been merged into the code base.

louis · 15 January 2020 16:18

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

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

JohnSPeach · 15 January 2020 16:29

@louis Thank-you - already done!