MxChartDB: Grafana Usage

Using Grafana for Visualization of MxChartDB Data

There may be reasons to use an external tool instead of the internal chart display. In the following, the use of Grafana is outlined as an example.
All installation commands listed refer to a Debian-based Linux like Raspberry Pi OS or Ubuntu.

1. Grafana with SQLite Plugin

1.1 Installation

Install Grafana on the same computer as the SQLite database:


          
sudo apt-get update
          
sudo apt-get install -y grafana
          
          
sudo grafana-cli plugins install frser-sqlite-datasource
          
          
sudo systemctl enable grafana-server
          
sudo systemctl start grafana-server
          
sudo systemctl status grafana-server

1.2 Start

Connect to Grafana with browser command http//:<db_server>:3000.

1.3 Arrange Grafana for SQLite Access

Define username and password.
Add SQLite as data source.
In datasources settings, enter the path of the SQLite database (for example /media/ZWay_USB/sqliteDB/MxChartDB.db)

1.4 Create a Timeseries Dashbord

With MxChartDB the sensor values are stored as stringified JSON array. The single values must be extracted from that array.

1.4.1 Option 1: JSON functions are enabled in SQLite

In latest SQLite versions (>= V3.38.0) and if in older versions JSON functions are enabled at compile time, build up your chart in Grafana with queries like:


          
  select ts/1000 as ts, 
          
    json_extract(val,'$[i]') as vali,
          
    ..., 
          
    json_extract(json_extract(val,'$[j]'),'$.value') as valj, 
          (for values with tooltips)
          
    ... 
          
  from MxChartDBnnn
          
  where ts >= $__from and ts < $__to

choose type='Time series' and Time formatted columns='ts'.

1.4.2 Option 2: JSON functions are not enabled in SQLite

Older SQLite versions prior to V3.38.0 don't support JSON per default. Extraction of single sensor values with SQL is more complex.
For example:


          
  val1 = substr(val,instr(val,',')+1)*1
          
  val2 = substr(substr(val,instr(val,',')+1),instr(substr(val,instr(val,',')+1),',')+1)*1
          
  val3 = substr(substr(substr(val,instr(val,',')+1), instr(substr(val,instr(val,',')+1),',')+1), instr(substr(substr(val,instr(val,',')+1), instr(substr(val,instr(val,',')+1),',')+1),',')+1)*1

(if values are numeric)

2. Grafana with JSON Plugin

This option needs at least MxChartDB_API.py version V1.3.0.

2,1 Installation

Cause we use the MxChartDB HTTP API, we can install Grafana on any computer on the same network:


          
sudo apt-get update
          
sudo apt-get install -y grafana
          
          
sudo grafana-cli plugins install marcusolsson-json-datasource
          
          
sudo systemctl enable grafana-server
          
sudo systemctl start grafana-server
          
sudo systemctl status grafana-server

2,2 Start

Connect to Grafana with browser command http//:<grafana-server>:3000.

2,c Arrange Grafana for JSON API Access

Define username and password.
Add JSON API as data source.
In datasources settings, enter the URL of the MxChartDB API like http://IP:5000/MxChartDB/sql,
with IP = ip address of the db_server (don't use a hostname),
let Query string empty

2,3 Create a Timeseries Dashbord

In Grafana build up your chart with queries like

Method	Path
GET	?select ts, val(1), val(2), instr(val(3),"on"), ... from MxChartDBnnn where ts >= $__from and ts < $__to

Field	Query Language	Type	Alias
$.[*][0]	JSONata	Time	ts
$.[*][1]	JSONata	Number	val1
$.[*][2]	JSONata	Number	val2
$.[*][3]	JSONata	Number	val3
...	JSONata	...	...