Grafana

Some inspiration for using Database Handler as a data source for Grafana.

• Grafana can use multiple data sources, which must be configured.
• Caution with timezone configuration matching the timestamps in the database

• don't use special characters in database, tables or column names (underscores are working)
• The MySQL data source plugin provides macros to simplify syntax and allow for dynamic parts

a time column needs to be in the result

The easiest query returns the whole HSE Log table and only adds a time filter for the date picker:

 SELECT time, logger_name as ´system´, level, message, source, error_code, error_message FROM AD_HIL.hse_log WHERE (time >= $__timeFrom() AND time <= $__timeTo())

The Traffic Light visualization can be used for displaying a status.

Example query, which finds all log entries with an error code:

 SELECT now() as time, COUNT(*) as status
 FROM AD_HIL.hse_log
 WHERE
 TIME >= $__timeFrom()
 AND
 TIME <= $__timeTo()
 AND
 error_code <> 0

Thresholds configuration:

Query the database to find the start- and end dates of test runs, which are needed, when the dashboard should show only values for one test run. In the future, we want to use the time picker plugin for this.

Example Query, looking for the first message of all test runs:

SELECT a.time AS starttime,
      DATE_ADD(b.time, INTERVAL -1 SECOND) AS endtime
FROM AD_HIL.hse_log AS a
JOIN AD_HIL.hse_log AS b
WHERE
  (a.message = 'Logging started...')
AND
  (b.message = 'Logging started...')
AND
  b.id= (
        SELECT MIN(ID)
        FROM (
            SELECT ID, TIME AS starttime FROM AD_HIL.hse_log WHERE (message = 'Logging started...') LIMIT 50
            ) AS sub
        WHERE sub.id > a.id
      )
ORDER BY
  a.time desc
LIMIT 10

It might be interesting to see the states of the system in a table. With this example query the states can be determined.

SELECT time, SUBSTRING(message, 16) AS 'State Machine state' FROM AD_HIL.hse_log WHERE message LIKE 'State Changed%' AND (time >= $timeFrom() AND time ⇐ $timeTo()) ORDER BY TIME desc

This example query finds a specific log message of the heartbeat module and visualizes it in a table:

SELECT time, SUBSTRING(message, 22) AS 'HB State'
FROM AD_HIL.hse_log
WHERE source LIKE 'Heartbeat Provider.lvlib:Main.vi:4140019->Heartbeat Provider%'
AND message LIKE 'Requested Set State:%'
AND (time >= $__timeFrom() AND time <= $__timeTo())
ORDER BY time desc

By looking at repeating log entries, a graph (Time Series visualization) can be filled, showing when the module was running:

SELECT time, 1 as 'Heartbeat Messages'
FROM AD_HIL.hse_log
WHERE source LIKE 'Heartbeat Provider.lvlib:Main.vi:4140019->Heartbeat Provider%'
AND (time >= $__timeFrom() AND time <= $__timeTo())
ORDER BY TIME desc