Errors inside the Event Analysis Report related with "Top 25 events by node"

Alejandro Galue added a comment - 02/Feb/12 11:43 AM Here is the SQL query used for the "Top 25 events by node": SELECT node.nodelabel, node.nodeid, events_by_node.tally FROM (SELECT COUNT(*) AS tally, events.nodeid FROM events GROUP BY events.nodeid LIMIT 25) AS events_by_node JOIN node ON (node.nodeid = events_by_node.nodeid) ORDER BY tally DESC; Ronny found that the inner query is unordered and this could be the cause of the small number of events reported by node. So the correct SQL query should be: SELECT node.nodelabel, node.nodeid, events_by_node.tally FROM (SELECT COUNT(*) AS tally, events.nodeid FROM events GROUP BY events.nodeid ORDER BY tally DESC LIMIT 25) AS events_by_node JOIN node ON (node.nodeid = events_by_node.nodeid) ORDER BY tally DESC; I've executed the original query against a sample database I have and this is what it displays: nodelabel | nodeid | tally ---------------+--------+------- NODE01 | 212 | 508 NODE02 | 211 | 352 NODE03 | 213 | 338 NODE04 | 210 | 271 NODE05 | 216 | 245 NODE06 | 209 | 240 NODE07 | 193 | 200 NODE08 | 214 | 188 NODE09 | 206 | 158 NODE10 | 208 | 155 NODE11 | 207 | 151 NODE12 | 197 | 134 NODE13 | 196 | 130 NODE14 | 160 | 128 NODE15 | 159 | 126 NODE16 | 194 | 120 NODE17 | 204 | 116 NODE18 | 200 | 114 NODE19 | 199 | 104 NODE20 | 201 | 104 NODE21 | 195 | 104 NODE22 | 203 | 90 NODE23 | 205 | 84 NODE24 | 202 | 80 NODE25 | 198 | 70 (25 rows) NOTE: I've replaced the nodes names with mock-data to protect customer's sensitive data (which is not relevant information here). If I execute the second query, this is what I got: nodelabel | nodeid | tally ---------------+--------+------- NODE26 | 2896 | 83065 NODE27 | 2368 | 64074 NODE28 | 2894 | 27652 NODE29 | 2083 | 19378 NODE30 | 3015 | 18252 NODE31 | 2977 | 17666 NODE32 | 2909 | 16446 NODE33 | 2836 | 11450 NODE34 | 1701 | 10890 NODE35 | 2829 | 10723 NODE36 | 2889 | 10118 NODE37 | 2011 | 9934 NODE38 | 2324 | 9778 NODE39 | 2970 | 9756 NODE40 | 3043 | 9416 NODE41 | 2273 | 8587 NODE42 | 1907 | 8317 NODE43 | 2274 | 8080 NODE44 | 2839 | 7222 NODE45 | 2128 | 7218 NODE46 | 2856 | 7187 NODE47 | 2325 | 7184 NODE48 | 2954 | 7128 NODE49 | 3047 | 7082 (24 rows) NOTE: I've replaced the nodes names with mock-data to protect customer's sensitive data (which is not relevant information here). The nodes displayed on this resultset is different from the first resultset. If we compare the second set of results with the output of the following query (Top 25 events): SELECT eventuei, eventdisplay, eventsource, COUNT(*) AS tally FROM events GROUP BY eventuei, eventsource, eventdisplay ORDER BY tally DESC LIMIT 25; which produce the following output: eventuei | eventdisplay | eventsource | tally -----------------------------------------------------+--------------+---------------------------------------------------------------+-------- uei.opennms.org/syslogd/cisco/controllerinterfaceUp | Y | syslogd | 206216 uei.opennms.org/syslogd/cisco/lineprotocolUp | Y | syslogd | 90630 uei.opennms.org/syslogd/cisco/lineprotocolDownturbo | Y | OpenNMS.EventTranslator | 69610 uei.opennms.org/internal/capsd/rescanCompleted | Y | OpenNMS.Capsd | 61255 uei.opennms.org/syslogd/ngn/interfaceUp | Y | syslogd | 40172 uei.opennms.org/syslogd/ngn/interfaceDown | Y | syslogd | 39879 uei.opennms.org/nodes/nodeLostService | Y | OpenNMS.Poller.DefaultPollContext | 37302 uei.opennms.org/nodes/nodeRegainedService | Y | OpenNMS.Poller.DefaultPollContext | 37259 uei.opennms.org/nodes/nodeDown | Y | OpenNMS.Poller.DefaultPollContext | 31519 uei.opennms.org/nodes/nodeUp | Y | OpenNMS.Poller.DefaultPollContext | 31497 uei.opennms.org/nodes/interfaceDown | Y | OpenNMS.Poller.DefaultPollContext | 29077 uei.opennms.org/nodes/interfaceUp | Y | OpenNMS.Poller.DefaultPollContext | 28947 uei.opennms.org/syslogd/user/Notice | Y | syslogd | 27131 uei.opennms.org/syslogd/cisco/bgpUp | Y | syslogd | 19227 uei.opennms.org/syslogd/cisco/bgpDownturbo | Y | OpenNMS.EventTranslator | 18954 uei.opennms.org/threshold/highThresholdExceeded | Y | OpenNMS.Threshd.ifInDiscards + ifOutDiscards | 15855 uei.opennms.org/threshold/highThresholdRearmed | Y | OpenNMS.Threshd.ifInDiscards + ifOutDiscards | 13439 uei.opennms.org/nodes/dataCollectionFailed | Y | OpenNMS.Collectd | 10667 uei.opennms.org/nodes/dataCollectionSucceeded | Y | OpenNMS.Collectd | 10534 uei.opennms.org/vendor/Cisco/traps/cHsrpStateChange | Y | trapd | 10033 uei.opennms.org/threshold/highThresholdExceeded | Y | OpenNMS.Threshd.ifOutOctets * 8 / 1000000 / ifHighSpeed * 100 | 9776 uei.opennms.org/syslogd/local7/Notice | Y | syslogd | 9386 uei.opennms.org/syslogd/conectivity/ospfUp | Y | syslogd | 9180 uei.opennms.org/threshold/highThresholdRearmed | Y | OpenNMS.Threshd.ifOutOctets * 8 / 1000000 / ifHighSpeed * 100 | 8350 uei.opennms.org/syslogd/conectivity/ospfDown | Y | syslogd | 7582 (25 rows) It makes sense, so the original query is wrong.

Alejandro Galue added a comment - 07/Feb/12 10:47 AM I guess that the query top25_events_by_node_uei (Top 25 events by node and UEI) suffer the same illness as top25_events_by_node. I got better numbers by adding the same "order clause" into the inner query. I mean, it should be: SELECT node.nodeid, node.nodelabel, eventdisplay, events_by_node.eventuei, events_by_node.tally FROM (SELECT COUNT(*) AS tally, events.eventuei, events.eventdisplay, events.nodeid FROM events GROUP BY events.eventuei, events.nodeid, events.eventdisplay ORDER BY tally DESC LIMIT 25) AS events_by_node JOIN node ON (node.nodeid = events_by_node.nodeid) ORDER BY tally DESC

Ronny Trommer added a comment - 08/Feb/12 4:28 AM Fixed in master with commit 1ab8fd51152009e27af038d255296b0242733722

Ronny Trommer added a comment - 08/Feb/12 4:36 AM Cherry picked to origin/1.10

Ronny Trommer added a comment - 08/Feb/12 4:44 AM Cherry picked to origin/1.8

Ronny Trommer added a comment - 08/Feb/12 4:56 AM Fixed with commit 1ab8fd51152009e27af038d255296b0242733722

Alejandro Galue added a comment - 13/Feb/12 2:11 PM I can confirm that the top25_events_by_node_uei has the same problem as top25_events_by_node. I've added the fix in the following revisions: 1.8: 248252c21a123040a34d594cb191225b31605432 1.10: 5d6e2087843daf34477d4789218ccb3ecc62f2a8