Thingspeak as a Data Source

Telegraf
1

Please excuse my naivety. I have very many (~10) sensors of my own design using ESP8266, which have been posting 8 fields of numerical data quite happily to thingspeak.com for several years. Whilst there is no specific problem with that, I am aware you can extract the time series data from them using a json query. Can someone please point me towards a working example using thingspeak, telegraf, influxdb and grafana where the data source is a thingspeak json request and the data is then stored any analysed locally (to me) for example on a raspberry pi.
I would prefer to do that, but I realise I could rewrite all the sensors applications to post more directly but I would prefer not to. Thanks in advance.

2

Hello @Paul_Stross,
Welcome!
You’ll probably want to use this plugin:

With the json data format:

This blog might help you understand some basics about InfluxDB (complete with an example about writing data with the telegraf tail plugin):

You might want to start getting familiar with InfluxDB by signing up for a free tier and importing a sample dataset
https://cloud2.influxdata.com/signup

3

Thanks for the personal reply. It is much appreciated. I have come across some of those tutorials, but I think I would do better adapting someone’s code rather than trying to hack my first attempt, as it is all so new to me. I can show you the sort of data I am trying to access from my sensors…
That would have been helpful I realise!

https://thingspeak.com/channels/135163/

I think the link should work for you. There are so many channels I had hoped someone would have cracked the exporting problem already.

Many thanks for your help. Kind regards. And if you can get me started with some working code, that would be great.

Paul

4

@Paul_Stross

I would not be aware that there is a ready-made Thingspeak plugin for Telegraf.

Nevertheless, it is possible to query Thingspeak data with Telegraf. However, you will have to invest some time. Read the documentation of Thingspeak and the related Telegraf plugins and configure the Telegraf plugins.

As far as I know Thingspeak provides a quite extensive REST API and a MQTT interface. Both can be queried with the corresponding Telegraf plugins.

1 Like
5

Thank you so much for replying. I know it should be possible and I am aware of what you are saying, but I don’t think I have sufficient knowledge of all of the links to make a working chain. However, I hoped to stand on the shoulders of others. I know that is cheeky, but that is my state of experience. Paul

6

Also I think such a plugin would have a really big base for many others who would find it of utility as ThingSpeak captures time series data in a very stereotypical form. Paul

7

@Paul_Stross

I think there probably won’t be a Thingspeak plugin for Telegraf for the following reasons:

  1. I doubt that there are many users who use Thingspeak together with Influxdb. Because Thingspeak already has its own display and analysis capabilities.
  2. It is not so easy to write the plugin in a way that it fits as many applications as possible.
  3. There is not necessarily a need for this plugin. All building blocks are already available (REST, MQTT) to realize this with existing Telegraf input plugins (http, mqtt). You just have to put some time into the right configuration.
8

There are huge numbers of ThingSpeak channels all of which produce quite stereotypical data in their 8 fields. They accumulate a lot of data too. I know the tools exist to help, so it should be possible. Yes, there is a graphing possibility in ThingSpeak. My post asked if anyone had tackled the data transfer. I think you see this problem differently from me. You are already familiar with making working examples, but it is different if you are trying to climb a different path on the learning curve on your own.
Paul

9

Hello.

Did you manage to find any ready code to load data from Thingspeak to InflubDB2?

10

No. Sorry I did not but I would still like to. Px

11

Guys, you can just use Telegraf and read the documentation… To give you a starter, I used the channel posted by @Paul_Stross to get

> 5HR_monitors,channel_id=135163,host=Hugin AbsPress4=1007.84741,AltCh4=77.16329,DS-Temp4=8.125,Humid4=31.15918,LDR4=216,Move4=0.04,RSSI4=-68,SeaPress4=1011.74579,lat=0,lon=0 1670494267000000000
> 5HR_monitors,channel_id=135163,host=Hugin AbsPress4=1007.87415,AltCh4=76.94001,DS-Temp4=8.125,Humid4=31.14844,LDR4=219,Move4=0.04,RSSI4=-68,SeaPress4=1011.77264,lat=0,lon=0 1670494417000000000
> 5HR_monitors,channel_id=135163,host=Hugin AbsPress4=1007.85968,AltCh4=77.06106,DS-Temp4=8.125,Humid4=31.13867,LDR4=223,Move4=0.04,RSSI4=-72,SeaPress4=1011.75812,lat=0,lon=0 1670494567000000000
> 5HR_monitors,channel_id=135163,host=Hugin AbsPress4=1007.90906,AltCh4=76.64858,DS-Temp4=8.0625,Humid4=31.14844,LDR4=225,Move4=0.04,RSSI4=-66,SeaPress4=1011.80768,lat=0,lon=0 1670494717000000000
> 5HR_monitors,channel_id=135163,host=Hugin AbsPress4=1007.87415,AltCh4=76.94001,DS-Temp4=8.125,Humid4=31.1377,LDR4=228,Move4=0.04,RSSI4=-67,SeaPress4=1011.77264,lat=0,lon=0 1670494867000000000
> 5HR_monitors,channel_id=135163,host=Hugin AbsPress4=1007.89459,AltCh4=76.76963,DS-Temp4=8.0625,Humid4=31.12793,LDR4=232,Move4=0.04,RSSI4=-70,SeaPress4=1011.79315,lat=0,lon=0 1670495017000000000

Here is the config I used

###############################################################################
## Fetch the data every minute at the minute (round_interval).
## For details see https://github.com/influxdata/telegraf/blob/master/docs/CONFIGURATION.md
[agent]
  interval = "60s"
  round_interval = true
  metric_batch_size = 1000
  metric_buffer_limit = 10000
  flush_interval = "10s"
###############################################################################

###############################################################################
## Query the last two minutes of the data. We need some overlap here to make
## sure not to miss any data if the query is delayed a but
[[inputs.http]]
  tagexclude = ["url"]
  urls = ["https://api.thingspeak.com/channels/135163/feeds.json?minutes=2"]

  ## Use the xpath parser (see https://github.com/influxdata/telegraf/tree/master/plugins/parsers/xpath)
  ## to extract the data from the collected JSON.
  data_format = "xpath_json"
  xpath_native_types = true

  [[inputs.http.xpath]]
    metric_name="'5HR_monitors'"
    metric_selection = "//feeds/*"
    timestamp = "created_at"
    timestamp_format = "2006-01-02T15:04:05Z07:00"

    field_selection = "*[starts-with(name(), 'field')]"
    field_value = "number(.)"
    tag_selection = "../../channel/*[starts-with(name(), 'field')]"

    [inputs.http.xpath.tags]
        channel_id = "//channel/id"

    [inputs.http.xpath.fields]
        lat = "number(//channel/latitude)"
        lon = "number(//channel/longitude)"
###############################################################################

###############################################################################
## The field names and field values are separately represented in the data
## above. So we merge the two and use the field names from the "field*" tags
## to actually name the fields.
[[processors.pivot]]
  tag_key = "field1"
  value_key = "field1"

[[processors.pivot]]
  tag_key = "field2"
  value_key = "field2"

[[processors.pivot]]
  tag_key = "field3"
  value_key = "field3"

[[processors.pivot]]
  tag_key = "field4"
  value_key = "field4"

[[processors.pivot]]
  tag_key = "field5"
  value_key = "field5"

[[processors.pivot]]
  tag_key = "field6"
  value_key = "field6"

[[processors.pivot]]
  tag_key = "field7"
  value_key = "field7"

[[processors.pivot]]
  tag_key = "field8"
  value_key = "field8"
###############################################################################

###############################################################################
## Output the data to where you want them to be. This will only print
## it to the console. You might want to use one of the output plugins
## (see https://github.com/influxdata/telegraf/tree/master/plugins/outputs)
## e.g. the InfluxDB v2 one
## (see https://github.com/influxdata/telegraf/tree/master/plugins/outputs/influxdb_v2)
[[outputs.file]]
  files = ["stdout"]
  data_format = "influx"
###############################################################################

I leave sending the data to InfluxDB to you for practicing… :wink: Use the documentation as a starter and let me know if you run into issues…

@Jay_Clifford do we have a place to store those examples?

12

Alternatively, you could adapt your devices to send to (or be queries by) Telegraf and then use the http output plugin with the JSON serializer to post data to Thingspeak and to InfluxDB simultaneously. This has the advantage that you reduce latency for the InfluxDB path…

13

That is stunningly helpful! No time to process it just now, but a MAJOR THANK YOU is due :slight_smile:

Paul

1 Like
14

Let me know if you need further help!

15

However, I could not see the timestamps within the data readouts. I’m sure that is likely to be critical.
Paul

16

The data’s contained time stamps in a YYYY/MM/ DD HH:MM format would be critical information for most users I think.

Dr Paul Stross.

17

Did you scroll to the right in my output example? There is a (nanosecond) timestamp, e.g. 1670494267000000000 (first line in the example) that’s how the time is represented in line protocol.
Using this site you can convert it back to a human-readable time **GMT** : Thursday, December 8, 2022 10:11:07 AM which is exactly what is in the data.

If you read the config carefully you see how the timestamp is defined

    timestamp = "created_at"
    timestamp_format = "2006-01-02T15:04:05Z07:00"

Where do you see an issue?

18

Thanks, I had only looked at the start of the long number and not noticed it incrementing. My mistake, apologies. Please excuse my naivety. It must be difficult for you to see that this does not come so naturally to others. I am both grateful and in awe of what you have done and jealous of how easily it all seems to be to you.
Thank you for your efforts to help others.
The output contains the raw nanoseconds. If one wanted it to print so beautifully, as you did…
"

5HR_monitors,channel_id=135163,host=Hugin AbsPress4=1007.84741,AltCh4=77.16329,DS-Temp4=8.125,Humid4=31.15918,LDR4=216,Move4=0.04,RSSI4=-68,SeaPress4=1011.74579,lat=0,lon=0 1670494267000000000

"
BUT print the output with a human readable time output e.g. “2006-01-02T15:04:05Z07:00” How would you do that? I could not see TOML as a conversion format, but I am sure to be missing something.
Paul

19

Sorry @Paul_Stross if my comment sounded offensive. This was not my intention, I was just wondering what I’m missing!

The output you see is in InfluxDB’s line protocol format. Telegraf will send this data to InfluxDB once you configured the outputs.influxdb_v2 plugin with your server URL, credentials etc. Once in InfluxDB you can easily translate the timestamp to a human-readable time.

As Telegraf itself is not intended to be directed to humans directly (ideally you configure and work with your data-sink aka Database) we barely have any means to show things “nicely”. Your best bet is to use the JSON serialization format and configure the timestamp, but for debugging I recommend pasting your timestamp into a tool like https://www.epochconverter.com/…

20

Thanks. It is becoming clearer now. I am preoccupied with other things just now but you have pointed me back in the right direction. I am more motivated to return to the project now and get stuck in again but it might be in the New-Year…
Paul