View source for Project TTN Daten in InfluxDB für Grafana

= Flexible Visualization of TTN Data =

This page describes installing Grafana and InfluxDB on openSuse 42.2.
It also describes how to configure Apache 2.4 to receive data from a TheThingsNetwork applications.
It uses a TTN HTTP integration for a device like https://github.com/joba-1/ATTiny84TTN to store it in an InfluxDB database.
Finally it shows an example Grafana dashboard to display the data from the InfluxDB.

== InfluxDB ==

=== Installing InfluxDB ===

Here is the [https://portal.influxdata.com/downloads/ Download Portal]
Currently, it tells me to

 wget https://dl.influxdata.com/influxdb/releases/influxdb-1.7.9.x86_64.rpm
 sudo zypper install influxdb-1.7.9.x86_64.rpm

It warns about missing package shadow-util. On openSuse this is named shadow and is installed -> ignore.

To use systemd for managing the database service, copy the service file

 sudo cp -av /usr/lib/influxdb/scripts/influxdb.service /etc/systemd/system/

For later versions of openSuse, [https://docs.influxdata.com/influxdb/v1.7/introduction/installation/# Influx Installation Documentation] suggests using a repository - should have the same results:

 sudo zypper ar -f obs://devel:languages:go/ go
 sudo zypper in influxdb

=== Configure InfluxDB ===

I did not need to change anything here, but in case you want to check:

 sudo vi /etc/influxdb/influxdb.conf

For Flux query language (i tried on 1.8.5) you may need 
'''
[http] 
flux-enabled = true
'''

=== Start InfluxDB ===

 sudo systemctl daemon-reload
 sudo systemctl enable influxdb
 sudo systemctl start influxdb
 sudo systemctl status influxdb

=== Test InfluxDB ===

Call the CLI and insert some test data. More details here: https://docs.influxdata.com/influxdb/v1.7/tools/shell/

 influx
  CREATE DATABASE my_test_db
  USE my_test_db
  INSERT my_table,my_key=my_test my_value=1
  INSERT my_table,my_key=my_test my_value=2
  INSERT my_table,my_key=my_test my_value=3
  SELECT * FROM my_table
  QUIT

=== Tips and Tricks for InfluxDB ===

==== Delete measurements ====

Sometimes invalid data gets into the database and needs to be cleaned.
InfluxDB does not allow to delete existing values, so brute force is the only option I know
The "group by *" is required to preserve tags

 influx --database ttn -execute 'SELECT * INTO temp FROM "measurements" WHERE pres_hPa > 700'
 influx --database ttn -execute 'drop measurement "measurements"'
 influx --database ttn -execute 'SELECT * INTO "measurements" FROM temp group by *'
 influx --database ttn -execute 'drop measurement temp'

==== Use Python to rework InfluxDB data ====

Example code (to copy fields into tags with same name) 

 from influxdb import InfluxDBClient
 
 # InfluxDB parameters
 server = 'job4'
 port = 8086
 database='ttn'
 
 # CLI interface
 if __name__ == "__main__":
    # read table "measurements", fix anomalies and write into table "data"
    client = InfluxDBClient(host=server, port=port, database=database)
    response = client.query('select * from "measurements"', epoch='s')
    print("Status: {}\n".format(response.error))
    points = response.get_points()
    data = []
    for point in points:
        if point['app_id'] == None:
            point['app_id'] = point['app_id_1']
        point['app_id'] = point['app_id'].strip('"')
        point.pop('app_id_1',  None)
        if point['dev_id'] == None:
            point['dev_id'] = point['dev_id_1']
        point['dev_id'] = point['dev_id'].strip('"')
        point.pop('dev_id_1',  None)
        item = {
            "measurement": "data",
            "tags": {
                "app_id": point["app_id"],
                "dev_id": point["dev_id"]
            },
            "time": point["time"],
            "fields": {
                "temp_degC": float(point["temp_degC"]),
                "vcc_V": float(point["vcc_V"]),
                "pres_hPa": float(point["pres_hPa"]),
                "humi_Percent": float(point["humi_Percent"]),
                "gtw_id": point["gtw_id"],
                "rssi": float(point["rssi"])
            }
        }
        data.append(item)
        if len(data) == 10000:
            print("Add {} items".format(len(data)))
            client.write_points(data, time_precision='s')
            data = []
    print("Add {} items".format(len(data)))
    client.write_points(data, time_precision='s')

==== Post from ESP8266 ====

<pre>
// Post Data to InfluxDB

#include <ESP8266HTTPClient.h>

WiFiClient client;
HTTPClient http;
int _influx_status = 0;

void post_data() {
  static const char Uri[]="/write?db=" INFLUX_DB "&precision=s";

  char msg[300]; 
  snprintf(msg, sizeof(msg), 
    "temperatures zulauf=%.2f,ruecklauf=%.2f,vorlauf=%.2f,kamin=%.2f\n",
    _temp_c[0], _temp_c[1], _temp_c[2], _temp_c[3]);
  http.begin(client, INFLUX_SERVER, INFLUX_PORT, Uri);
  http.setUserAgent(NAME);
  _influx_status = http.POST(msg);
  String payload = http.getString();
  http.end();
  if( _influx_status < 200 || _influx_status > 299 ) {
    snprintf(msg, sizeof(msg), 
      "Post %s:%d%s status %d response '%s'",
      INFLUX_SERVER, INFLUX_PORT, Uri, _influx_status, payload.c_str());
    syslog.log(LOG_ERR, msg);
  };
}
</pre>

== Grafana ==

=== Install Grafana ===

I now use leap supplied grafana 7.4. No need for the below

Here are the [https://grafana.com/grafana/download installation instructions]. Basically just do

 wget https://dl.grafana.com/oss/release/grafana-6.5.2-1.x86_64.rpm 
 sudo zypper install grafana-6.5.2-1.x86_64.rpm 

It complains about missing urw-fonts, but they are not really required -> ignore.
Also accept there is no signing key

=== Start Grafana ===

 sudo systemctl daemon-reload 
 sudo systemctl enable grafana-server.service 
 sudo systemctl start grafana-server.service 
 sudo systemctl status grafana-server.service

=== Test Grafana ===

Open http://localhost:3000

Login as user admin and password admin. You will be asked for a new password immediately.
Go to the steps in the web UI: 
* Add datasource
** Select InfluxDB
** Check use as default (optional)
** Select Query Language InfluxQL (Flux is also possible, but has no graphical query editor)
** Enter URL of your InfluxDB, e.g. http://localhost:8086
** Enter our test db in InfluxDB Details: my_test_db
** Select Save & Test should respond with success
* Add dashboard
** Go back to the first page via the Grafana symbol in the upper left and select New dashboard
** Select Add query
** Select measurement: my_table
** Select field: my_value
** optionally enter an alias: my_count
** Select next icon: Visualization
** Select Null values: connected (to draw lines between dots)
** Select Legend: Min and Max (to display them below the dashboard)
** Select next icon: General
** Enter a panel title
** We could add more panels, but for now: select Save dashboard icon and give it a name: My counts
** Select the region around the dot, repeat the zoom until you see the single values.

=== Datasource with InfluxDB Query Language Flux ===
Supported at least since InfluxDB 1.8.5 with Grafana 7.4.2, needs to be enabled in influxdb.conf
* URL: unchanged, e.g. http://localhost:8086
* Access: unchanged (Server)
* Cookies: can stay empty
* Auth: can all stay disabled
* Custom HTTP headers: not needed
* Organization: like configured for Grafana (I use Banzhaf)
* Token: user:password
* Default Bucket: database name
* Min interval and max serias not needed

== Python and Apache ==

=== Insert JSON Values into InfluxDB with Python ===

The ThingsNetwork offers an HTTP integration service.
It sends a POST request with JSON data to an URL you define each time your LoRaWAN device sends data.
Lets store this data with InfluxDB so we can display it with Grafana.

As a first step, lets assume we somehow get the JSON data of one measurement in one line via standard input.
The script will parse the json, format a messages with the interesting fields (here including some poayload fileds from my thp84 app) post a request to the InfluxDB and print the result of that request.

 <nowiki>
#!/usr/bin/python

import sys
import json
import requests
import time

server = 'localhost'
port = 8086
database='my_test_db'

url = 'http://{}:{}/write?db={}&precision=s'.format(server, port, database)

for line in sys.stdin:
    try:
        data = json.loads(line)
        ts = time.strptime(data["metadata"]["time"].split('.', 1)[0], '%Y-%m-%dT%H:%M:%S')
        msg = 'node,app_id="{}",dev_id="{}" temp_degC={},vcc_V={},pres_hPa={},humi_Percent={},gtw_id="{}",rssi={} {}'.format(
            data["app_id"], data["dev_id"], data["payload_fields"]["temp_degC"], data["payload_fields"]["vcc_V"],
            data["payload_fields"]["pres_hPa"], data["payload_fields"]["humi_Percent"], data["metadata"]["gateways"][0]["gtw_id"],
            data["metadata"]["gateways"][0]["rssi"], int(time.mktime(ts)))
        result = requests.post(url, data=msg)
        print('insert date "{}" result: "{}"'.format(data["metadata"]["time"], result.text))
    except Exception as e:
        print("ignoring '{}': '{}'".format(line, e))
</nowiki>

=== Call Python as CGI from Apache Webserver ===

Next step is to use similar python code to insert the data automatically each time apache receives an HTTP request from The ThingsNetwork.

* Enable the apache python module by adding "python" to the APACHE_MODULES line of /etc/sysconfig/apache2.
* Enable execution of python scripts in a directory by adding file /etc/apache2/conf.d/cgi-py.conf (basename not relevant, but needs to end in .conf):
 <Directory /srv/www/htdocs/ttn>
  Options +ExecCGI
  AddHandler cgi-script .py
 </Directory>
* Create the directory and restart apache to load the new config
 mkdir /srv/www/htdocs/ttn
 systemctrl restart apache
* Create a python file in the configured directory that prints the whole html page, including headers and make the file executable
 vi /srv/www/htdocs/ttn/hello-cgi.py
 chmod +x /srv/www/htdocs/ttn/hello-cgi.py
* Content of a simple example file:
 #!/usr/bin/env python
 print("Content-Type: text/html;charset=utf-8")
 print()
 print("Hello CGI World!")

=== Call Python as WSGI from Apache Webserver ===

WSGI is the more modern approach to calling python. To make it work,

* Install apache module for wsgi and python3 (zypper in apache2-mod_wsgi-python3)
* If above step didn't do it, enable the apache wsgi module by adding "wsgi" to the APACHE_MODULES line of /etc/sysconfig/apache2.
* Enable execution of a python script by adding file /etc/apache2/conf.d/wsgi-py.conf (basename not relevant, but needs to end in .conf):
 WSGIScriptAlias /ttn/hello-wsgi.py /srv/www/htdocs/ttn/hello-wsgi.py
 <Directory /srv/www/htdocs/ttn>
    Require all granted
 </Directory>
* Create the directory and restart apache to load the new config
 mkdir /srv/www/htdocs/ttn
 systemctrl restart apache
* Create the python file used in the alias that prints the whole html page, including headers and make the file executable
 vi /srv/www/htdocs/ttn/hello-wsgi.py
 chmod +x /srv/www/htdocs/ttn/hello-wsgi.py
* Content of a simple example file:
 def application(environ, start_response):
    status = '200 OK'
    output = b'Hello WSGI World!'
 
    response_headers = [('Content-type', 'text/plain'),
                        ('Content-Length', str(len(output)))]
    start_response(status, response_headers)
 
    return [output]
* A bit more involved example showing the environment and POST data (if any)
 import json
 
 def application(environ, start_response):
    status = '200 OK'
    output = 'Hello WSGI World!\n'
 
    response_body = [
        '%s: %s' % (key, value) for key, value in sorted(environ.items())
    ]
    output += '\n'.join(response_body)
 
    try:
        if environ["REQUEST_METHOD"] == "POST":
            output += "\nPOST method"
        request_body_size = int(environ['CONTENT_LENGTH'])
        request_body = environ['wsgi.input'].read(request_body_size)
        s = "".join(chr(b) for b in request_body)
        output += "\nData: " + s
        data = json.loads(s)
        output += "\nJson: " + json.dumps(data, sort_keys=True, indent=4)
    except Exception as e:
        output += "\nException: " + str(e)
 
    response_headers = [('Content-type', 'text/plain'),
                        ('Content-Length', str(len(output)))]
    start_response(status, response_headers)
 
    return [output.encode('Utf-8')]

Call this e.g. with
 wget -q -O- --post-data '{"a":1}' http://localhost/ttn/hello-wsgi.py

More infos about setting up wsgi are in the [https://modwsgi.readthedocs.io/en/develop/user-guides/configuration-guidelines.html WSGI guidelines].

=== Transform TTN JSON data to InfluxDB Inserts ===

Modified python script to send POST request data from TTN to a logfile and insert data into InfluxDB

 <nowiki>
import json
import requests
import time
import calendar

# Log to contain the whole JSON objects from TTN
log_file = '/var/log/ttn/thp84.log'

# InfluxDB parameters
server = 'localhost'
port = 8086
database='ttn'
url = 'http://{}:{}/write?db={}&precision=s'.format(server, port, database)

def application(environ, start_response):
    try:
        if environ["REQUEST_METHOD"] == "POST":

            # get data as received from TTN
            request_body_size = int(environ['CONTENT_LENGTH'])
            request_body = environ['wsgi.input'].read(request_body_size)
            body_string = "".join(chr(b) for b in request_body)

            # write received data to log file
            with open(log_file, 'a') as log:
                log.write(body_string)
                log.write('\n')

            # parse utc timestamp from TTN into time tuple
            data = json.loads(body_string)
            ts = time.strptime(data["metadata"]["time"].split('.', 1)[0], '%Y-%m-%dT%H:%M:%S')

            # find gateway with best RSSI
            rssi = -999
            for gw in data["metadata"]["gateways"]:
                if gw["rssi"] > rssi:
                    rssi = gw["rssi"]
                    gtw_id = gw["gtw_id"]

            # build InfluxDB insert string with explicit utc time in seconds resolution
            msg = 'measurements,app_id="{}",dev_id="{}" temp_degC={},vcc_V={},pres_hPa={},humi_Percent={},gtw_id="{}",rssi={} {}'.format(
                data["app_id"], data["dev_id"], data["payload_fields"]["temp_degC"], data["payload_fields"]["vcc_V"],
                data["payload_fields"]["pres_hPa"], data["payload_fields"]["humi_Percent"], gtw_id, rssi, int(calendar.timegm(ts)))

            # send insert request to the database
            result = requests.post(url, data=msg)
            output = 'OK'
    except Exception as e:
        output = "Exception: " + str(e)

    # send OK to TTN
    status = '200 OK'
    response_headers = [('Content-type', 'text/plain'),
                        ('Content-Length', str(len(output)))]
    start_response(status, response_headers)

    return [output.encode('Utf-8')]
</nowiki>

=== Import Historic TTN Data ===

Import data from logs into InfluxDB 

 cat /var/log/ttn/thp84.log | python3 thp84.py

=== Grafana Dashboard for TTN Data from InfluxDB ===

build dashboard with a panel for each data item (like temperature, rssi, ...)

An example is https://github.com/joba-1/ATTiny84TTN/blob/master/plots/Grafana-dashboard-TTN-thp84-Device.json

[[File:TTN-thp84-Device-Grafana.png|600px]]

My local dashboard: http://job4:3000/d/lUpjxBfZk/ttn-thp84-device?from=now-30d&to=now