OP5 - How to convert the report_data table to a more human readable format with SQL queries

The merlin.report_data table is used to store information about state changes and other information used when generating reports, but it's not very intuitive to read the data unless you are familiar with the event types of Nagios, so the below query may help.

How to run queries

You can run queries directly from the command line, or you can use third party tools like DBeaver. Just make sure you stick to "SELECT" queries, do not make any changes to the database unless you have proper backups in place and feel comfortable with restoring them.

To get a MySQL prompt on the command line, run the following command to select the "merlin" database where report data resides:

# mysql -D merlin

Example query

SELECT id, 
       From_unixtime(timestamp) AS 'Timestamp', 
       hard, 
       CASE 
         WHEN state = 0 
              AND event_type = 801 THEN 'HOST UP' 
         WHEN state = 0 
              AND event_type = 701 THEN 'SERVICE OK' 
         WHEN state = 1 
              AND event_type = 801 THEN 'HOST DOWN' 
         WHEN state = 1 
              AND event_type = 701 THEN 'SERVICE WARNING' 
         WHEN state = 2 
              AND event_type = 801 THEN 'HOST UNREACHABLE' 
         WHEN state = 2 
              AND event_type = 701 THEN 'SERVICE CRITICAL' 
         WHEN state = 3 
              AND event_type = 801 THEN 'HOST UNREACHABLE' 
         WHEN state = 3 
              AND event_type = 701 THEN 'SERVICE UNKNOWN' 
         WHEN event_type = 1000 THEN 'FLAPPING STARTED' 
         WHEN event_type = 1001 THEN 'FLAPPING STOPPED' 
         WHEN event_type = 1103 THEN 'DOWNTIME START' 
         WHEN event_type = 1104 THEN 'DOWNTIME STOP' 
         WHEN event_type = 100 THEN 'PROCESS START' 
         WHEN event_type = 103 THEN 'PROCESS SHUTDOWN' 
         ELSE state 
       end                      AS 'Interpreted State', 
       host_name                AS 'Host', 
       service_description      AS 'Service' 
FROM   merlin.report_data 
ORDER  BY timestamp DESC 
LIMIT  100; 

This query will make the data points somewhat more understandable to the reader, as it will interpret the Nagios event types and state codes for you. It will, however, list all of the 100 newest points in the database. If you want to show more, change the "LIMIT".

To exclude data points related to services and only show hosts, you can filter by:

WHERE service_description = ''

If you want to show data for only a specific service under a specific host, the end of the query should look something like:

(...)
FROM merlin.report_data 
WHERE service_description = 'Host latency' AND host_name = 'poller02'
ORDER BY timestamp DESC
LIMIT 100; 

Wildcard matching

If you want to find a service that includes a specific string, you can do wildcard matching with the "LIKE" operator:

WHERE service_description = 'Host latency' AND host_name LIKE 'poller%'

This will match "poller01", "poller02", and "pollerX" but not "abc_poller" or "1poller"

See also: MySQL manual on pattern matching.