Store historical location data (e.g. in time series database)

[victorhooi]

It would be awesome if we could store historical location data - e.g. how many devices, and which devices (by MAC address) were at each location, in say, 5 minute increments.

We could store this in a time-series DB like InfluxDB.

[schollz]

That would be cool. I'm not sure how to use Influx, but this data could be pulled using SQLite pretty easily.

[DatanoiseTV]

I am currently hacking around and seeing if I can get anything like this up and running.

[elvarb]

If there is an api to query the current situation that reply could easily be used for Logstash or Telegraf

https://www.elastic.co/guide/en/logstash/current/plugins-inputs-http_poller.html https://github.com/influxdata/telegraf/tree/master/plugins/inputs/http

[DatanoiseTV]

@elvarb But doesn't the telegraf JSON HTTP input just take integer and float responses? Could be problematic to deal with strings.

[elvarb]

@DatanoiseTV not sure how the telegraf json input works, not used it. The big picture I see that instead of integrating it against one system, provide an api or some interface so its easier to integrate with anything you like.

The big big reason this project is so interesting to me is that I have done a POC using ESP8266. Monitor for management frames and send them to the Elastic stack, providing a very nice interface with Kibana for the data.

[ishanjain28]

Hi, I implemented a HTTP endpoint in find that spits outs current state of each location. The JSON in my case looks like this,

{
    "total_number_of_devices": 1,
    "by_location": [
        {
            "device": "red",
            "timestamp": "2018-06-14T07:14:03.325Z",
            "probability": 0.79,
            "randomized": false,
            "num_scanners": 19,
            "active_mins": 80,
            "first_seen": "2018-06-13T07:38:05.953Z",
            "location": "unitech"
        }
    ]
}

Unfortunately, Influx DB/Telegraf does not support nested JSON. So, Integrating telegraf is not possible until this issue is resolved.

There is, however, another way, And that would be to send separate JSON for each device, But that might not be desirable.

Let me know what you guys think..

[tevfik]

Did you try seriesly from dustin (https://github.com/dustin/seriesly/) it has an flexible api. I'm testing it with my own web based client, pretty cool performance also writen in go.

[jekkos]

I have this working by forwarding MQTT messages to influxdb through telegraf. Quite cool as then you can do any kind of visualization in grafana.

[victorhooi]

@jekkos Wow, that seems pretty cool - much easier than polling get_locations (which seems to be currently broken - as it maxes out CPU on find3, and times out). Are you able to provide some more details on how you captured the MQTT messages, and forwarded them?

[jekkos]

well if you setup telegraf and connect it to your mqtt broker, then you can forward those messages to influxdb quite easily. Beside telegraf you will need the mqtt consumer plugin that you then configure to read from the mqtt topic that has the find3 messages.

[jekkos]

https://github.com/influxdata/telegraf/tree/master/plugins/inputs/mqtt_consumer

[jekkos]

Also check https://community.influxdata.com/t/sonoff-mqtt-json-parsing-to-influxdb/3448/3 and https://github.com/influxdata/telegraf/tree/master/plugins/parsers/json

[nurettin]

I subscribe to the mqtt channel somewhere/location/# to get location updates from all devices. Then I forward it to a more performant and secure mqtt server with channel name locations/somewhere/all using a simple forwarder written in QtMqtt (which is pretty robust). From there, I can forward to web via mqtt:ws protocol or I could write a client to listen to locations/# and forward all data to db.