LoRa App Server InfluxDB integration (with Grafana)

A new feature that will be coming to LoRa App Server is InfluxDB integration :slight_smile: (https://www.influxdata.com). This makes it possible to let LoRa App Server write the sensor data directly into InfluxDB so that it can be graphed with Grafana (https://grafana.com).

Note: don’t look at the scale of this sensor data, it might be completely wrong :slight_smile:

When you have configured the Cayenne LPP codec or have implemented a custom JavaScript decoder, it will automatically write these fields as measurements into InfluxDB. Nested objects e.g.

{
    "object": {
        "foo": {
            "bar": 123.456
        }
    }
}

will be written as device_frmpayload_data_foo_bar with value 123.456. It will also store the application and device-name, devEUI and fPort as tags so that data can be aggregated / filtered per application, device and fPort.

Next to the sensor data from the frmPayload, it will also write some uplink data so that you can aggregate on the channel, data-rate etc… usage as you can see in the above screenshot.

Note that this still in development. However I’m looking forward to you feedback!

14 Likes

HI @brocaar!!!

These integrations is the because parts!

Yessss!! :rocket:

2 Likes

GREAT! Very nice feature!
Is it possible to be a beta tester?

2 Likes

That would be great. I’ll share a link in this topic with test builds when I have the configuration through the UI part ready.

1 Like

if you need someone to help testing - let me know.
:slight_smile:

2 Likes

@RogerioCassares @Silvano_Pisoni @vincems / whoever is interested:

You can find a precompiled test release here: https://www.dropbox.com/sh/t9x6ehgpskfcmb5/AACKINP5WUAHjhwggBWGaD4fa?dl=0

(you will find the source here https://github.com/brocaar/lora-app-server/tree/influx_integration)

Setting up an InfluxDB integration works the same as setting up a HTTP integration. Only you have to select the “InfluxDB integration” :slight_smile: Looking forward to your feedback!

An additional feature that I have added is that when LoRa App Server finds two fields (within the same object) named latitude and longitude, it will combine these as a single InfluxDB measurement with values latitude=...,longitude=...,geohash="...". This should make it easier to use geo plugins for Grafana :slight_smile:

1 Like

Hi, Orne. This looks cool, but I have one question (consider I’ve never used Grafana): why is InfluxDB used instead of directly using the same Postgres instance from lora-app-server? Does it have better integration with Grafana?

1 Like

Good question :slight_smile: InfluxDB is a time series database which is great for sensor measurements + it really works well with Grafana (although lately Grafana also added PostgreSQL support). Some helpful features are setting retention policies in InfluxDB and continuous queries.

With the first you define how long the data should be persisted (e.g. one day, week, month…) and using a continuous query you can downsample the data. E.g. when you device sends data every 10 minutes, you could store this raw data for one day, and then downsample this to daily, weekly, monthly aggregates for example.

An other thing why I think InfluxDB is easier for these kind of things is that you don’t have to pre-define the database schema. Where you have to create the correct indices with PostgreSQL, is is all done automatically for you with InfluxDB. Each measurement consists of a measurement name, tags, values and a timestamp. InfluxDB will make sure that tags and timestamps are indexed for you.

Also note that the integration works over HTTP, meaning that when LoRa App Server is offered as a hosted solution, this means that each user can setup his / her own (self hosted) InfluxDB database with retention policies suitable for the linked application(s).

4 Likes

Installed in 5 minutes and works perfectly.
Great Brocaar

2 Likes

Hi! Do I understand correctly that this only works when there is a custom payload codec (or the built in codec) present? And it is limited to payload data?
What about gateway data (like CRC error rates, etc.)?

1 Like

Hi! Do I understand correctly that this only works when there is a custom payload codec (or the built in codec) present? And it is limited to payload data?

Yes, you need to decode the payload as LoRa App Server needs to know what to store and under which measurement names.

What about gateway data (like CRC error rates, etc.)?

One step at a time :wink: But yes, that could definitely be something for the future.

1 Like

This has been released as LoRa App Server 0.21.0 :slight_smile:

If you have a PyCom device with PySense or PyTracker shield, you could use https://github.com/brocaar/pycom-examples as these examples work out of the box with CayenneLPP (and thus can be written directly into InfluxDB).

2 Likes

When you upgrade to LoRa App Server 0.21.0, please note that there was a small change in the naming of measurements. E.g. _temperaturesensor_ (derived from the JSON temperatureSensor) is now named _temperature_sensor_). See also https://www.loraserver.io/lora-app-server/integrate/sending-receiving/influxdb/.

2 Likes

Absolutely amazing feature to have. One middle app for saving to InfluxDB less for me =) Thank you!

1 Like

Hello
I have an MDOT-BOX and I am trying to connect with influxdb.
the following picture is the integration application config:


the next picture is the influx shell
image
the last picture is the return of JavaScript function
image

when I execute the select command in influxdb, it has not measurements.

help me please.

1 Like

Please confirm in the live device event logs that the device is actually sending uplink data / that the decoder does not return any errors :slight_smile:

1 Like

The mosquitto_sub in rx channel shows correct measures:


The LoraServer event logs:

1 Like

Hello
The lora-app-server log:


The application server restarts the process every second.

1 Like

Could you please create a GitHub issue with the complete log output? https://github.com/brocaar/lora-app-server/issues

1 Like

Hello @brocaar:

Below I have posted a more complete lora-app-server log. It seem that there is a problem with the InfluxDB connection since the log has returned “may 24 14:03:51 lab04 lora-app-server[6035]: panic: reflect.Value.Interface: cannot return value obtained from unexported field or method”.

may 24 14:03:41 lab04 lora-app-server[5992]: created by github.com/brocaar/lora-app-server/vendor/google.golang.org/grpc.(*Server).serveStreams.func1
may 24 14:03:41 lab04 lora-app-server[5992]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/google.golang.org/grpc/server.go:636 +0xa1
may 24 14:03:41 lab04 systemd[1]: lora-app-server.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
may 24 14:03:41 lab04 systemd[1]: lora-app-server.service: Failed with result ‘exit-code’.
may 24 14:03:41 lab04 systemd[1]: lora-app-server.service: Service hold-off time over, scheduling restart.
may 24 14:03:41 lab04 systemd[1]: lora-app-server.service: Scheduled restart job, restart counter is at 51.
may 24 14:03:41 lab04 systemd[1]: Stopped LoRa App Server.
may 24 14:03:41 lab04 systemd[1]: Started LoRa App Server.
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“starting LoRa App Server” docs=“https://docs.loraserver.io/” version=0.21.0
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“connecting to postgresql”
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“setup redis connection pool”
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“handler/mqtt: TLS config is empty”
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“handler/mqtt: connecting to mqtt broker” server=“tcp://localhost:1883”
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“applying database migrations”
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“handler/mqtt: connected to mqtt broker”
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“handler/mqtt: subscribing to tx topic” qos=0 topic=application/+/node/+/tx
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“migrations applied” count=0
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“starting application-server api” bind=“0.0.0.0:8001” ca-cert= tls-cert= tls-key=
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“starting join-server api” bind=“0.0.0.0:8003” ca_cert= tls_cert= tls_key=
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“starting client api server” bind=“0.0.0.0:8080” tls-cert=/etc/lora-app-server/certs/http.pem tls-key=/etc/lora-app-se
may 24 14:03:41 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:41+02:00” level=info msg=“registering rest api handler and documentation endpoint” path=/api
may 24 14:03:43 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:43+02:00” level=warning msg=“creating insecure network-server client” server=“localhost:8000”
may 24 14:03:43 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:43+02:00” level=info msg=“finished client streaming call” grpc.code=OK grpc.method=StreamFrameLogsForDevice grpc.service=ns.NetworkServer grpc.
may 24 14:03:51 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:51+02:00” level=info msg=“finished client unary call” grpc.code=OK grpc.method=GetDevice grpc.service=ns.NetworkServer grpc.time_ms=1.09 span.k
may 24 14:03:51 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:51+02:00” level=info msg=“finished client unary call” grpc.code=OK grpc.method=UpdateDevice grpc.service=ns.NetworkServer grpc.time_ms=1.706 sp
may 24 14:03:51 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:51+02:00” level=info msg=“device updated” dev_eui=0000000000000000
may 24 14:03:51 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:51+02:00” level=info msg=“handler/mqtt: publishing message” qos=0 topic=application/1/node/0000000000000000/rx
may 24 14:03:51 lab04 lora-app-server[6035]: time=“2018-05-24T14:03:51+02:00” level=info msg=“handler/http: publishing data-up payload” dev_eui=0000000000000000 url=“http://192.168.47.212:8080/HttpIntegration/
may 24 14:03:51 lab04 lora-app-server[6035]: panic: reflect.Value.Interface: cannot return value obtained from unexported field or method
may 24 14:03:51 lab04 lora-app-server[6035]: goroutine 422 [running]:
may 24 14:03:51 lab04 lora-app-server[6035]: reflect.valueInterface(0xc5c640, 0xc420346340, 0xa8, 0xc42056fd01, 0xc4200bc3a0, 0xc42056fdf0)
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/.gimme/versions/go1.10.1.linux.amd64/src/reflect/value.go:959 +0x1c1
may 24 14:03:51 lab04 lora-app-server[6035]: reflect.Value.Interface(0xc5c640, 0xc420346340, 0xa8, 0x0, 0xc5c640)
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/.gimme/versions/go1.10.1.linux.amd64/src/reflect/value.go:948 +0x44
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/internal/handler/influxdbhandler.objectToMeasurements(0x1, 0xc4202c8400, 0xe, 0xc4200bdc58, 0x6, 0x0, 0x0, 0x0, 0xc4204a8ff0, 0x1,
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/internal/handler/influxdbhandler/influxdb_handler.go:291 +0x90b
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/internal/handler/influxdbhandler.objectToMeasurements(0x1, 0xc4202c8400, 0xe, 0xc4200bdc58, 0x6, 0x0, 0x0, 0x0, 0xc4204a8ff0, 0x1,
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/internal/handler/influxdbhandler/influxdb_handler.go:296 +0xe04
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/internal/handler/influxdbhandler/influxdb_handler.go:202 +0x61a
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/internal/handler/multihandler.Handler.SendDataUp(0xee3b40, 0xc420242f00, 0x1, 0xc4202c8400, 0xe, 0xc4200bdc58, 0x6, 0x0, 0x0, 0x0,
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/internal/handler/multihandler/multi.go:41 +0x1d4
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/internal/api.(*ApplicationServerAPI).HandleUplinkData(0x164a968, 0xee0ac0, 0xc4207a42a0, 0xc420275080, 0x164a968, 0xecca20, 0xc5c3c
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/internal/api/application_server.go:183 +0x1481
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/vendor/github.com/brocaar/loraserver/api/as._ApplicationServer_HandleUplinkData_Handler.func1(0xee0ac0, 0xc4207a42a0, 0xd6a260, 0xc
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/github.com/brocaar/loraserver/api/as/as.pb.go:605 +0x86
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/vendor/github.com/grpc-ecosystem/go-grpc-middleware.ChainUnaryServer.func1.1(0xee0ac0, 0xc4207a42a0, 0xd6a260, 0xc420275080, 0x26,
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/github.com/grpc-ecosystem/go-grpc-middleware/chain.go:31 +0x104
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/vendor/github.com/grpc-ecosystem/go-grpc-middleware/logging/logrus.UnaryServerInterceptor.func1(0xee0ac0, 0xc420349f80, 0xd6a260, 0
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/github.com/grpc-ecosystem/go-grpc-middleware/logging/logrus/server_interceptors.go:31 +0x10d
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/vendor/github.com/grpc-ecosystem/go-grpc-middleware.ChainUnaryServer.func1.1(0xee0ac0, 0xc420349f80, 0xd6a260, 0xc420275080, 0x26,
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/github.com/grpc-ecosystem/go-grpc-middleware/chain.go:34 +0x97
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/vendor/github.com/grpc-ecosystem/go-grpc-middleware/tags.UnaryServerInterceptor.func1(0xee0ac0, 0xc420365320, 0xd6a260, 0xc42027508
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/github.com/grpc-ecosystem/go-grpc-middleware/tags/interceptors.go:21 +0x86
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/vendor/github.com/grpc-ecosystem/go-grpc-middleware.ChainUnaryServer.func1(0xee0ac0, 0xc420365320, 0xd6a260, 0xc420275080, 0xc42031
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/github.com/grpc-ecosystem/go-grpc-middleware/chain.go:37 +0x16c
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/vendor/github.com/brocaar/loraserver/api/as._ApplicationServer_HandleUplinkData_Handler(0xce5b40, 0x164a968, 0xee0ac0, 0xc420365320
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/github.com/brocaar/loraserver/api/as/as.pb.go:607 +0x167
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/vendor/google.golang.org/grpc.(*Server).processUnaryRPC(0xc4200d4000, 0xee5e80, 0xc42053e780, 0xc420718b40, 0xc420180bd0, 0x161aca0
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/google.golang.org/grpc/server.go:921 +0x8ac
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/vendor/google.golang.org/grpc.(*Server).handleStream(0xc4200d4000, 0xee5e80, 0xc42053e780, 0xc420718b40, 0x0)
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/google.golang.org/grpc/server.go:1143 +0x1318
may 24 14:03:51 lab04 lora-app-server[6035]: github.com/brocaar/lora-app-server/vendor/google.golang.org/grpc.(*Server).serveStreams.func1.1(0xc4200bc110, 0xc4200d4000, 0xee5e80, 0xc42053e780, 0xc420718b40)
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/google.golang.org/grpc/server.go:638 +0x9f
may 24 14:03:51 lab04 lora-app-server[6035]: created by github.com/brocaar/lora-app-server/vendor/google.golang.org/grpc.(*Server).serveStreams.func1
may 24 14:03:51 lab04 lora-app-server[6035]: /home/travis/gopath/src/github.com/brocaar/lora-app-server/vendor/google.golang.org/grpc/server.go:636 +0xa1
may 24 14:03:51 lab04 systemd[1]: lora-app-server.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
may 24 14:03:51 lab04 systemd[1]: lora-app-server.service: Failed with result ‘exit-code’.
may 24 14:03:51 lab04 systemd[1]: lora-app-server.service: Service hold-off time over, scheduling restart.
may 24 14:03:51 lab04 systemd[1]: lora-app-server.service: Scheduled restart job, restart counter is at 52.
may 24 14:03:51 lab04 systemd[1]: Stopped LoRa App Server.
may 24 14:03:51 lab04 systemd[1]: Started LoRa App Server.
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“starting LoRa App Server” docs=“https://docs.loraserver.io/” version=0.21.0
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“connecting to postgresql”
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“setup redis connection pool”
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“handler/mqtt: TLS config is empty”
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“handler/mqtt: connecting to mqtt broker” server=“tcp://localhost:1883”
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“applying database migrations”
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“handler/mqtt: connected to mqtt broker”
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“handler/mqtt: subscribing to tx topic” qos=0 topic=application/+/node/+/tx
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“migrations applied” count=0
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“starting application-server api” bind=“0.0.0.0:8001” ca-cert= tls-cert= tls-key=
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“starting join-server api” bind=“0.0.0.0:8003” ca_cert= tls_cert= tls_key=
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“starting client api server” bind=“0.0.0.0:8080” tls-cert=/etc/lora-app-server/certs/http.pem tls-key=/etc/lora-app-se
may 24 14:03:51 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:51+02:00” level=info msg=“registering rest api handler and documentation endpoint” path=/api
may 24 14:03:54 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:54+02:00” level=warning msg=“creating insecure network-server client” server=“localhost:8000”
may 24 14:03:54 lab04 lora-app-server[6072]: time=“2018-05-24T14:03:54+02:00” level=info msg=“finished client streaming call” grpc.code=OK grpc.method=StreamFrameLogsForDevice grpc.service=ns.NetworkServer grpc.

I have tried to send the JSON “object” example you proposed on the documentation page but it still not working. Here are two payloads from Mosquitto topic:

application/1/node/0000000000000000/rx {“applicationID”:“1”,“applicationName”:“AplicacionOTAA”,“deviceName”:“DEUI00”,“devEUI”:“0000000000000000”,“rxInfo”:[{“mac”:“00800000a0000d74”,“rssi”:-115,“loRaSNR”:1,“name”:“garteway mac”,“latitude”:0,“longitude”:0,“altitude”:0}],“txInfo”:{“frequency”:868100000,“dataRate”:{“modulation”:“LORA”,“bandwidth”:125,“spreadFactor”:12},“adr”:false,“codeRate”:“4/5”},“fCnt”:69,“fPort”:1,“data”:“Dv8ADwgGLcsFDbMLAdI=”,“object”:{“temperature_sensor”:{“1”:23.5}}}

application/1/node/0000000000000000/rx
{“applicationID”:“1”,“applicationName”:“AplicacionOTAA”,“deviceName”:“DEUI00”,“devEUI”:“0000000000000000”,“rxInfo”:[{“mac”:“00800000a0000d74”,“rssi”:-117,“loRaSNR”:2,“name”:“garteway mac”,“latitude”:0,“longitude”:0,“altitude”:0}],“txInfo”:{“frequency”:868100000,“dataRate”:{“modulation”:“LORA”,“bandwidth”:125,“spreadFactor”:12},“adr”:false,“codeRate”:“4/5”},“fCnt”:70,“fPort”:1,“data”:“Dv8ADwgGLdcFCnoLAdI=”,“object”:{“temperature_sensor”:{“1”:23.5}}}

Thank you very much in advance.

Regards.

1 Like