If I select a single node and right click to get to syslog it loads really quickly. If I right click on a container or subnet and view syslog it never seems to complete. I haven't looked at the SQL but I assume there are missing indexes for container/subnet and its doing a full table scan?
I came across this old thread talking about adding the indexes to source: https://www.netxms.org/forum/feature-requests/syslog-table-index/ (https://www.netxms.org/forum/feature-requests/syslog-table-index/)
Is it feasible to add one to containers and/or subnets too?
Cheers.
Looking at the query in postgres we have a large syslog table (>20GB), and it seems the main issue is the scan back through all timestamps by default when right clicking on a zone or subnet and choosing syslog:
netxms=# explain SELECT msg_timestamp,source_object_id,zone_uin,facility,severity,hostname,msg_tag,msg_text FROM syslog WHERE msg_id<=399610423 AND (source_object_id IN (45902, 45930, 45940, 45948, 45953, 45967, 46061, 46106, 46107, 46109)) ORDER BY msg_timestamp DESC LIMIT 1000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.57..15744.67 rows=1000 width=160)
-> Index Scan Backward using idx_syslog_msg_timestamp on syslog (cost=0.57..18440640.31 rows=1171273 width=160)
Filter: ((msg_id <= 399610423) AND (source_object_id = ANY ('{45902,45930,45940,45948,45953,45967,46061,46106,46107,46109}'::integer[])))
(3 rows)
If I manually build a search in the syslog window its a much shorter scan (and a lightly different query constructed):
netxms=# explain SELECT msg_timestamp,source_object_id,zone_uin,facility,severity,hostname,msg_tag,msg_text FROM syslog WHERE msg_id<=399611781 AND ((source_object_id = 45948) OR (source_object_id = 45940) OR (source_object_id = 45930) OR (source_object_id = 46109) OR (source_object_id = 46107) OR (source_object_id = 46106)) AND (msg_timestamp BETWEEN 1569333600 AND 1569419999) ORDER BY msg_timestamp DESC LIMIT 1000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.57..1418.03 rows=48 width=160)
-> Index Scan Backward using idx_syslog_msg_timestamp on syslog (cost=0.57..1418.03 rows=48 width=160)
Index Cond: ((msg_timestamp >= 1569333600) AND (msg_timestamp <= 1569419999))
Filter: ((msg_id <= 399611781) AND ((source_object_id = 45948) OR (source_object_id = 45940) OR (source_object_id = 45930) OR (source_object_id = 46109) OR (source_object_id = 46107) OR (source_object_id = 46106)))
(4 rows)
Could there be a preference to set a default date range for syslog/event log searches (so that any right click of a container/subnet/zone defaults to say 1 day or 1 week). Or could you not immediately execute the search when right clicking and instead take you to the populated syslog search window to allow you to enter a time range?
Or is there some index-fu that could speed this up?
Not sure what the most usable approach is here.
Cheers.
For searches like that, I manually open the Syslog window and add my constraints before searching.
That's for the same reason you mentioned: if you don't, it will go through the entire syslog table (even if it only returns the latest 4000 lines or something like that).
A default to limit Syslog (and SNMP Trap and Event Log) output to anything newer than a configurable (Server Side? Client Side? Both?) interval would be a huge improvement for that.