From OpenNMS

Introduction

One of the most important tasks involved in keeping openNMS running smoothly is regular maintenance of the events table in the database. This article describes some useful approaches and example SQL queries for keeping the events table in good shape.

Useful Queries

Here are some SQL queries useful in the maintenance of the events table.

Informational

Queries in this section do not make any changes to the database and may be run without fear of damaging the system, although some of them may slow down other database operations while they are running.

Event Histogram

Query:

SELECT eventUEI, COUNT(eventID) AS tally
FROM events
GROUP BY eventUEI
ORDER BY tally DESC
LIMIT 10;

Sample output:

                        eventuei                         | tally 
---------------------------------------------------------+-------
 uei.opennms.org/internal/promoteQueueData               | 41837
 uei.opennms.org/threshold/highThresholdExceeded         |  3228
 uei.opennms.org/threshold/highThresholdRearmed          |  3200
 uei.opennms.org/nodes/nodeLostService                   |  2738
 uei.opennms.org/nodes/nodeRegainedService               |  2460
 uei.opennms.org/internal/authentication/successfulLogin |  1651
 uei.opennms.org/internal/capsd/rescanCompleted          |   985
 uei.opennms.org/nodes/nodeDown                          |   727
 uei.opennms.org/nodes/nodeUp                            |   652
 uei.opennms.org/threshold/lowThresholdExceeded          |   359
(10 rows)

What This Query Does

This query might be the single most useful one for getting a quick glance at what your events table looks like. It presents a list, in descending order, of the ten most common types of events in your database.

Interpreting the Results of This Query

If the top event types are remarkably more numerous (by a factor of hundreds or thousands) than any others, then there is a good chance that the PostgreSQL query planner is not performing well due to an artificial skew in the event types present. This situation can lead to poor performance in the web UI, delays in the sending of notifications, and other problems.

Enterprise-Default Event Histogram

Example query:

SELECT
    SUBSTRING(eventsnmp FOR POSITION(',' IN eventsnmp) - 1) AS trapOID,
    COUNT(eventid) AS tally
FROM events
WHERE eventUEI = 'uei.opennms.org/generic/traps/EnterpriseDefault'
GROUP BY trapOID ORDER BY tally DESC;

Sample output:

          trapoid          | tally 
---------------------------+-------
 .1.3.6.1.4.1.1793         |   202
 .1.3.6.1.4.1.2036.2.1.4.1 |    53
 .1.3.6.1.4.1.7368         |    21
 .1.3.6.1.4.1.2036.2.1.4.2 |    16
 .1.3.6.1.4.1.2036.2.1.4.3 |     1
(5 rows)

What This Query Does

This query is useful as a follow-on to Event Histogram when the top event types are unformatted events derived from enterprise-specific SNMP traps. It further classifies the unformatted events according to trap-OID of the SNMP trap from which each event was created.

Interpreting the Results of This Query

The string of digits and dots in the trapOID results column is an SNMP object identifier (OID) that identifies the vendor and management information base (MIB) branch associated with the trap. This string should always begin with the sequence .1.3.6.1.4.1, which is shorthand for iso(1).org(3).dod(6).internet(1).private(4).enterprises(1). The seventh number identifies the private enterprise number (PEN) of the vendor whose SNMP agent originated the trap. You can look up private enterprise numbers at IANA. Any digits following the PEN provide additional identifying information specific to the vendor's MIBs.