Store TTN received data in a file

Application Development
1

Hi everyone,
There is any possibility to download an store the data from TTN into a excel file or similar?
Thanks in advance for your time,
Regards

2

One option often called out on the forum is this method (GoogleSheets rather than Excel)

…note the caution wrt security by obscurity via the web url :wink:

3

Hi @monclus, I store all the TTN uplink data on my applications as MQTT/JSON into txt files. Very simple as follows:

On a public cloud Linux VM I run the mosquitto_sub system to subscribe to the TTN MQTT uplink stream and simply output the JSON records into a txt log file.

You can then very simply process the JSON records in your favourite language and extract whatever matters to you. Creating a CSV file for excel import would be easy. It also creates an ongoing archive of all uplinks.

I use systemd to start/restart the mosquitto_sub MQTT subscriber automatically and use a cron job at midnight to restart via systemctl to get a datestamped file per 24hrs. This way they all run continuously, restart after errors, etc.

4

Wow, thanks for the reply and I appreciate it so much; but I barely understood what are you telling me. There is some guide I can follow being a newcomer?
Regards

5

Hi @monclus, The best way to start is to combine reading the docs and using the system. The best documentation entry point for the way I use MQTT to get data into a file is https://www.thethingsnetwork.org/docs/applications/mqtt/api.html

Everything that I describe is just automating and securing the use of the mosquitto_sub software that is described in the TTN MQTT API documentation. For automation (on Linux) look at online tutorials on the standard system engineering tools; systemd, systemctl and cron.

If you are a real novice at system engineering then start with https://en.wikipedia.org/wiki/Systemd , https://en.wikipedia.org/wiki/Cron and https://en.wikipedia.org/wiki/Pipeline_(software) for Linux.

6

Just to add a short explainer for Newbies like I am:
The easiest way I found to simply store data from TTN in a file is really the way @cultsdotelecomatgmai describes it.

Here is just a very basic how-to for storing data.

  1. I assume you have linux (i have ubuntu 20.04) installed, this can be in a virtual machine on your local PC. The nice thing is that you can also use a virtual machine on some cloud service like AWS, so you don’t have to run your local PC all the time.
  2. You need to install mosquitto and understand the very basics of it, otherwise you will be stuck: read this short quick-start
  3. One of the simplest ways to save data to a file is by just piping it from the output of mosquitto_sub. This command writes all uplink messages from all devices from your given application to a new file or appends to it if the file already exists:
    mosquitto_sub -h yourregionhere.thethings.network -t “+/devices/+/up” -u “your_app_id_here” -P “your_access_key_here” -v | tee -a /pathtoyourfile/yourfile.txt

Of course, this only works as long as you execute the above command manually. Ideally, this would be put in some sort of startup script.
For me the nice thing of this approach is that you use MQTT from the very start, which is useful later on for different purposes (visualization of data, storing in a “real” database etc).

1 Like