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 recieve data from a Things Network applications HTTP integration and store it in an InfluxDB database. Finally it shows how to create a Grafana dashboard to display the data from the InfluxDB.

InfluxDB

Installing InfluxDB

Here is the 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, 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

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

Grafana

Install Grafana

Here are the 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
  • 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.

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.

#!/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[        # print("insert '{}'".format(msg))
"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))

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]

Transform TTN JSON data to InfluxDB Inserts

Modify the python script to send POST request data from TTN to InfluxDB

Coming soon...