Use Grafana
===========

Mysql
-----

Add user and give SELECT rights :

.. code-block:: shell

  sudo mysql -uroot -p -e "GRANT SELECT ON PyScada_db.* TO 'Grafana-user'@'localhost' IDENTIFIED BY 'Grafana-user-password';"

Nginx
-----

Add in `/etc/nginx/nginx.conf` after ``http { ... }`` :

::

  include /etc/nginx/grafana-access.conf;

Create `/etc/nginx/grafana-access.conf` with :

::

  stream {
    # MySQL server
    server {
      listen     3305;
      proxy_pass 127.0.0.1:3306;
      proxy_timeout 60s;
      proxy_connect_timeout 30s;
    }
  }

Restart Nginx :

.. code-block:: shell

  sudo systemctl restart nginx

Grafana
-------

Add MySQL datasource :

- Host :

  - Local : `/run/mysqld/mysqld.sock`
  - Remote : SERVER_WITH_NGINX_IP:3305

- Database : ``PyScada_db``
- User : ``Grafana-user``
- Password : ``Grafana-user-password``

Create a dashboard:

- Or import the `example dashboard <https://github.com/pyscada/PyScada/blob/master/extras/Grafana-test-dashboard.json>`_.

- Or for example, add theses variables : set ``refresh on dashboard load``, ``multi-value`` and ``all option`` :

  - Add mysql datasource variable (type Datasource).
  - Add variables with type query using ``$Datasource`` :

    - Protocols : ``SELECT protocol AS __text, id AS __value FROM pyscada_deviceprotocol``
    - Devices : ``SELECT d.short_name AS __text, d.id AS __value FROM pyscada_device d WHERE d.protocol_id IN (${Protocols}) AND d.active = 1``
    - Units : ``SELECT u.unit AS __text, u.id AS __value FROM pyscada_unit u``
    - Variables : ``SELECT v.name AS __text, v.id AS __value FROM pyscada_variable v WHERE v.device_id IN (${Devices}) AND v.unit_id IN (${Units}) AND v.active = 1``
    - Time group (type Interval) : ``1s,10s,1m,10m,30m,1h,6h,12h,1d,7d,14d,30d,1M``
    - Null as (type custom) : ``0, NULL, previous``

 - Example query :

  .. code-block::

    SELECT
      $__timeGroupAlias(r.date_saved,$time_group),
      avg(IFNULL(r.value_float64, 0.0) + IFNULL(r.value_int64, 0.0) + IFNULL(r.value_int32, 0.0) + IFNULL(r.value_int16, 0.0) + IFNULL(r.value_boolean, 0.0)),
      v.name AS metric
    FROM pyscada_recordeddata as r
    JOIN pyscada_variable v ON r.variable_id = v.id
    WHERE
      $__timeFilter(r.date_saved) AND
      r.variable_id IN (${Variables})
    GROUP BY time, metric
    ORDER BY $__timeGroup(r.date_saved,$time_group,$null_as)

Embed in pyscada HMI
--------------------

Edit Grafana config file:

.. code-block:: shell

  sudo nano /etc/grafana/grafana.ini

Find and set :
  - allow_embedding = true
  - For localhost grafana : root_url = http://localhost:3000/grafana/

For localhost grafana add in `/etc/nginx/sites-enabled/pyscada.conf` :

::

  location /grafana/ {
    proxy_pass http://127.0.0.1:3000/;
  }

Restart Grafana server:

.. code-block:: shell

  sudo systemctl restart grafana-server.service

Create a custom html panel with the code from a dashboard or a panel from sharing options in grafana

Other
-----

use ssl : http://www.turbogeek.co.uk/2020/09/30/grafana-how-to-configure-ssl-https-in-grafana/