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.

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.

#!/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))

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

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')]

Import Historic TTN Data

Import data from logs into InfluxDB

TODO

Grafana Dashboard for TTN Data from InfluxDB

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

TODO