====== Grafana ======

===== HSE-Logger Dashboard =====

Some inspiration for using [[code:commercial:hse-logger_extensions:db-handler|Database Handler]] as a data source for Grafana.

{{:kb:pasted:20230427-133657.png}}

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

=== SQL ===
  * don't use special characters in database, tables or column names (underscores are working)
  * The MySQL data source plugin provides [[https://grafana.com/docs/grafana/latest/datasources/mysql/#macros|macros]] to simplify syntax and allow for dynamic parts
a time column needs to be in the result

==== Dashboard Items ====

=== HSE Log Table ===

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())

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

{{:kb:pasted:20230427-133717.png}}

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:

{{:kb:pasted:20230427-133733.png}}

=== Test Runs ===

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 [[https://grafana.com/grafana/plugins/williamvenner-timepickerbuttons-panel/|time picker plugin]] for this.

{{:kb:pasted:20230427-133753.png}}

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

=== State Machine States ===

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

=== Heartbeat ===
== States ==

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

== Watchdog ==

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