Filter by plugin output not working

I apologize if this is something that’s already been resolved or if I’m doing something wrong, I did a search and didn’t find anything really similar, but if I missed something please let me know.

I’m trying to pull up a history of alerts this year which triggered with a specific word (“Commercial”) in the output of the check. Icingaweb2 gives me the option of filtering by “Output”, which should do what I want, but when I select it it gives me an error and a huge SQL statement, as seen in screenshots. This is done on the History > Event Overview page.

For reference, I can find the info I want from MySQL with the following query:

select * from icinga_statehistory where long_output like '%Commercial%' AND state_time > "2018-12-31 23:59:59";


Then when I click the “Apply” button:

And the specific stack trace:

SQLSTATE[HY000]: General error: 1111 Invalid use of group function

#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(744): Zend_Db_Adapter_Pdo_Abstract->query(Object(Zend_Db_Select), Array)
#5 /usr/share/php/Icinga/Data/Db/DbConnection.php(332): Zend_Db_Adapter_Abstract->fetchRow(Object(Zend_Db_Select))
#6 /usr/share/php/Icinga/Data/SimpleQuery.php(579): Icinga\Data\Db\DbConnection->fetchRow(Object(Icinga\Module\Monitoring\Backend\Ido\Query\EventhistoryQuery))
#7 /usr/share/php/Icinga/Data/SimpleQuery.php(455): Icinga\Data\SimpleQuery->fetchRow()
#8 /usr/share/icingaweb2/modules/monitoring/library/Monitoring/DataView/DataView.php(538): Icinga\Data\SimpleQuery->hasResult()
#9 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/partials/event-history.phtml(32): Icinga\Module\Monitoring\DataView\DataView->hasResult()
#10 /usr/share/php/Icinga/Web/View.php(259): include(String)
#11 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
#12 /usr/share/icingaweb2/library/vendor/Zend/View/Helper/Partial.php(106): Zend_View_Abstract->render()
#13 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(345): Zend_View_Helper_Partial->partial(String, Array)
#14 /usr/share/php/Icinga/Web/View.php(278): Zend_View_Abstract->__call(String, Array)
#15 zend.view:///usr/share/icingaweb2/modules/monitoring/application/views/scripts/list/eventhistory.phtml(19): Icinga\Web\View->__call(String, Array)
#16 /usr/share/php/Icinga/Web/View.php(259): include(String)
#17 /usr/share/icingaweb2/library/vendor/Zend/View/Abstract.php(877): Icinga\Web\View->_run(String)
#18 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(904): Zend_View_Abstract->render()
#19 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(925): Zend_Controller_Action_Helper_ViewRenderer->renderScript(String, NULL)
#20 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/Helper/ViewRenderer.php(964): Zend_Controller_Action_Helper_ViewRenderer->render()
#21 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action/HelperBroker.php(272): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#22 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(518): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#23 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#24 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#25 /usr/share/php/Icinga/Application/Web.php(300): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#26 /usr/share/php/Icinga/Application/webrouter.php(104): Icinga\Application\Web->dispatch()
#27 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#28 {main}

Hi,

I belief you hit a bug in Icinga Web 2, I just tried this locally and hit the same error.

I also found an issue in the tracker:

The issue seems to be already addressed with the following pull request:

The pull request is targeted for the release v2.7.0. Maybe you can test this locally and provide feedback if the linked pull request resolves your issue?

Best regards
Michael

I manually made the changes in the PR and it seems to have fixed it, thanks! I’m not getting any results, but at least now I can search. I’ll look around, it might be worth seeing if there’s an enhancement to add “LIKE” capability to that field for searching for specific words in the string.

Doesn’t the wildcard * work? (e.g. *foo*)

It does not, no.

Search:

image

Here is an alert I am trying to find

image

EDIT: That alert occurred on 7/1, I didn’t realize it didn’t show there until after I posted.
EDIT2: I’ve also tried searching *down. and Service Providers Check:* with the same results. Is there another PR that enabled this functionality that I’m missing?

EDIT3: So doing some more testing, it looks like the issue from edit 2 was my timestamp check, it defaults to 7 days ago. When searching for Service Providers* I get nothing past June 23rd, removing that check I get things from today. I cannot use *search terms* syntax at all, that always just reports nothing. I also for whatever reason cannot search for *Commercial DIP port down., though I can get results for *down., but the latest was from March and it’s a different check entirely. Unless I’m stupid and missing some basic functionality, it looks like this definitely needs some work.

It’s not you. Though, wildcard matching is there since the beginning. But what is filtered with output is just the first line of a plugin’s output. Displayed however is both the first line and remaining content (long output).

So you can search for *Commercial* but you won’t get a match for the event you’re looking for because that’s where it appears in the long output and the list doesn’t even search there.

Now this warrants a new issue on Github. If you don’t mind creating another one.

1 Like