Grouping / Filtering for Notification Attributes in Icingaweb2

Hi,
is there any possibiliy to assign groups on notifications or filter on attributes of notification objects? I wanted to create a filter which shows me all Service Objects, which have an specific command, e.g. all services which are part of an Notfication Object which as the notification-command “mail-service-notification” set.
In custom filters I can only use Service or Host attributes or vars, but not from notification objects. This would be a really great feature to get an quick overview which Services and Hosts are notified by mail, sms, or something else. By now we are not using the director, so I don’t know if this opportunitiy already exists there.

Kind Regards
Robert

Hi,

unfortunately this is not possible, since the Icinga 2 notifications are mapped into the IDO 1.x schema where this isn’t available 1:1 then. Do you want to use such filters for specific views, or is it about debugging a notification problem? If the latter applies, I’d suggest looking into the REST API of Icinga 2, and/or the debug console.

Cheers,
Michael

Hi Michael,
we wanted to create specific views so if someone asks “what services are notified by sms” you get the information with just one click.
By the way, we have an custom host variable named “os”, which is set to “Windows” on Windows Hosts. When trying to filter on this variable i always run into SQL errors like

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION ‘latin1_general_ci’ is not valid for CHARACTER SET ‘utf8mb4’, query was: SELECT so.name1 AS host_name, SUM( CASE WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0 THEN 0 ELSE 1 END ) AS unhandled_service_count FROM icinga_objects AS so
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id AND so.is_active = 1
INNER JOIN icinga_servicestatus AS ss ON ss.service_object_id = so.object_id AND ss.current_state > 0
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = s.host_object_id
INNER JOIN (SELECT ho.object_id FROM icinga_objects AS ho
INNER JOIN icinga_hosts AS h ON h.host_object_id = ho.object_id AND ho.is_active = 1 AND ho.objecttype_id = 1
INNER JOIN icinga_hoststatus AS hs ON hs.host_object_id = ho.object_id
LEFT JOIN icinga_customvariablestatus AS hcv_os ON h.host_object_id = hcv_os.object_id AND hcv_os.varname = ‘os’ COLLATE latin1_general_ci WHERE (hcv_os.varvalue = ‘Windows’) LIMIT 25) AS h ON h.object_id = s.host_object_id GROUP BY so.name1 HAVING (SUM(
CASE
WHEN (ss.problem_has_been_acknowledged + ss.scheduled_downtime_depth + COALESCE(hs.current_state, 0)) > 0
THEN 0
ELSE 1
END
) > 0)

#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(String, Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(799): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#4 /usr/share/php/Icinga/Data/Db/DbConnection.php(370): Zend_Db_Adapter_Abstract->fetchPairs(Object(Zend_Db_Select))
#5 /usr/share/php/Icinga/Data/SimpleQuery.php(628): Icinga\Data\Db\DbConnection->fetchPairs(Object(Icinga\Module\Monitoring\Backend\Ido\Query\HostserviceproblemsummaryQuery))
#6 /usr/share/icingaweb2/modules/monitoring/application/controllers/ListController.php(113): Icinga\Data\SimpleQuery->fetchPairs()
#7 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Module\Monitoring\Controllers\ListController->hostsAction()
#8 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#9 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#10 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#11 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
#12 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#13 {main}

SQLSTATE[42000]: Syntax error or access violation: 1253 COLLATION ‘latin1_general_ci’ is not valid for CHARACTER SET ‘utf8mb4’

#0 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement/Pdo.php(219): PDOStatement->execute(Array)
#1 /usr/share/icingaweb2/library/vendor/Zend/Db/Statement.php(297): Zend_Db_Statement_Pdo->_execute(Array)
#2 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(470): Zend_Db_Statement->execute(Array)
#3 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(String, Array)
#4 /usr/share/icingaweb2/library/vendor/Zend/Db/Adapter/Abstract.php(799): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#5 /usr/share/php/Icinga/Data/Db/DbConnection.php(370): Zend_Db_Adapter_Abstract->fetchPairs(Object(Zend_Db_Select))
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(628): Icinga\Data\Db\DbConnection->fetchPairs(Object(Icinga\Module\Monitoring\Backend\Ido\Query\HostserviceproblemsummaryQuery))
#7 /usr/share/icingaweb2/modules/monitoring/application/controllers/ListController.php(113): Icinga\Data\SimpleQuery->fetchPairs()
#8 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Module\Monitoring\Controllers\ListController->hostsAction()
#9 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#10 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#11 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#12 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
#13 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#14 {main}

Have you any idea what the problem could be? PHP and everything else is up to date
Cheers,
Robert

Seems to be the problem here.
Three github issues I found for this error message.



I’m no DB expert, so I can’t help any further, sorry :frowning:

1 Like