Advanced filtering of logs by date/time seems broken?

[damarrin]

Hello,

New forum user here, thank you for the excellent software that is Zoneminder.

I'm trying to diagnose some issues and was trying to filter log entries by date and time. I seem unable to do it. Entering something like 23.09.2024 or 2024-09-23 or just 11:14 in the date/time dialog of the advanced popup will produce an empty list of results. Additionally, an error is immediately produced in the log:

23.09.2024, 11:35:14 CEST web_php 12612 ERR SQL-ERR dbFetchAll no result, statement was 'SELECT TimeKey, Component, ServerId, Pid, Code, Message, File, Line FROM `Logs` WHERE (DateTime LIKE ?) ORDER BY TimeKey DESC LIMIT ?, ?'params: 23.09.2024,0,10 /usr/share/zoneminder/www/includes/database.php 195

Is this correct behavior? At least I'd say the error is way overkill for just a failure to find some log entries.

Thank you very much.

[iconnor]

Dates will be stored in the db in mysql format... YYYY-mm-dd HH:MM:SS
Your version with periods won't work.

[damarrin]

Thank you for your response. I just entered 2024-09-23 19:03:22 and no entries were returned, even though I know there's one at that exact second.

[iconnor]

There is a situation where the db is in the wrong charset and searching doesn't work. Please check the logs. There could be a message about collation

[damarrin]

No errors about collation, but I get this:

25.09.2024, 11:55:51 CEST web_php 30467 ERR SQL-ERR 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'DateTime' in 'where clause'', statement was 'SELECT TimeKey, Component, ServerId, Pid, Code, Message, File, Line FROM `Logs` WHERE (DateTime LIKE ?) ORDER BY TimeKey DESC LIMIT ?, ?' params:12:10,0,25 /usr/share/zoneminder/www/includes/database.php 161
25.09.2024, 11:55:50 CEST web_php 30467 ERR SQL-ERR dbFetchOne no result, statement was 'SELECT count(*) AS Total FROM Logs WHERE (DateTime LIKE ?)'params: 12:10 /usr/share/zoneminder/www/includes/database.php 170

Perhaps that's a clue?

[iconnor]

Definitely a clue. Apparently we never actually added support for DateTime or Server adv search fields.

You can get a build with the fix by following my proposed ppa, or apply the change manually.

https://github.com/ZoneMinder/zoneminde ... 584174aceb

[aiworldx]

No errors about collation, but I get this:

25.09.2024, 11:55:51 CEST web_php 30467 ERR SQL-ERR 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'DateTime' in 'where clause'', statement was 'SELECT TimeKey, Component, ServerId, Pid, Code, Message, File, Line FROM `Logs` WHERE (DateTime LIKE ?) ORDER BY TimeKey DESC LIMIT ?, ?' params:12:10,0,25 /usr/share/zoneminder/www/includes/database.php 161
25.09.2024, 11:55:50 CEST web_php 30467 ERR SQL-ERR dbFetchOne no result, statement was 'SELECT count(*) AS Total FROM Logs WHERE (DateTime LIKE ?)'params: 12:10 geometry dash world /usr/share/zoneminder/www/includes/database.php 170

Perhaps that's a clue?

Yes, it looks like you’re encountering an SQL error related to a missing column named DateTime. Verify that the Logs table actually has a column named DateTime. If it’s misspelled or named differently (like date_time or created_at), that would cause the error. If this is a recent change or update, there might have been a migration that didn’t run correctly. Ensure all database migrations have been applied.

[damarrin]

Definitely a clue. Apparently we never actually added support for DateTime or Server adv search fields.

You can get a build with the fix by following my proposed ppa, or apply the change manually.

https://github.com/ZoneMinder/zoneminde ... 584174aceb

I tried the version from proposed, but it isn't showing the login page for me, so I can't really test anything. I downgraded to the released version and I can login again.

Edit: I replaced the log.php file with the one from github and it doesn't throw any errors in the log any more, but still doesn't find anything. I tried entering stuff like 2024-09-26, 11:41, 12:34:58, entries I know exist in the log, but still get no results.

[iconnor]

You may have to use wildcards. Like 2024-08%
% is the sql wildcard character.

Unfortunately the advanced searchdialog is generate automatically by bootstrap table.. I don't know how to add help text to it.

[damarrin]

Yeah, using % works. Even when looking for a full date/time it must have a trailing %, like 2024-09-25 11:47:34%