Timestamp_format if timestamp value is iso8601 but wrapped in quotes?

Telegraf
1

Hi folks,

I have json data that is fairly straight forward but sadly, the time value is in quotes :frowning:

{
    "data": [
        {
            "time": "2023-05-04T23:10:00",
            "value": 1000
        },
    ]
}

So if I just set timestamp_format = ā€œ2023-05-04T23:10:00ā€ (http.json_v2), nothing returns (works just fine if I donā€™t specify a format, but with an extra default timestamp I donā€™t want of course). Iā€™ve been looking for a way to escape or wildcard the quotes but I guess my duckduckgo-fu is weak ā€¦

Is there something I can do here that doesnā€™t involve me downloading the data first, cleaning it up in python and then pushing it ā€¦

Thanks in advance!

Valentin

2

Hello @Valentiiiin,
Hmm wait Iā€™m confused. What is the extra default timestamp you donā€™t want of course?
Are you specifying the timestamp column?

timestamp_path = ā€œā€ # A string with valid GJSON path syntax to a valid timestamp (single value)
You gotta use gjson syntax
https://github.com/tidwall/gjson/blob/v1.7.5/SYNTAX.md
So yours would be
data.1
I do believe.

Here are a collection of examples that can be helpful too:

You might have to go the python route but you could do it with telegraf using the execd processor plugin:

hereā€™s an example repo that uses it so you can understand the file structure

3

Hi @Anaisdg , thanks for getting back!!

Sorry, I didnā€™t mean to confuse, let me re-iterate. If I donā€™t specify the timestamp:

  name_override = "interval"
  tagexclude = ["url", "host"]
  data_format = "json_v2"
      [[inputs.http.json_v2]]
          [[inputs.http.json_v2.object]]
          path = "data"
          #timestamp_key = "time"
          #timestamp_format = "2023-05-07T19:30:00"
          included_keys = [
            "value",
            "time"
          ]

telegraf - test returns the following:

interval time="2023-05-07T20:20:00",value=11093.7 1683491942000000000

I meant to say that I donā€™t need the current time, i.e. the last column in unix / epoch time since that is off from the actual, sensor provided measurement. But when I specify the timeformat:

  name_override = "interval"
  tagexclude = ["url", "host"]
  data_format = "json_v2"
      [[inputs.http.json_v2]]
          [[inputs.http.json_v2.object]]
          path = "data"
          timestamp_key = "time"
          timestamp_format = "2023-05-07T19:30:00"
          included_keys = [
            "value",
            "time"
          ]

telegraf - test returns nothing beyond:

(...)
2023-05-07T20:40:15Z W! Outputs are not used in testing mode!
2023-05-07T20:40:15Z I! Tags enabled: host=hostname

I didnā€™t look into verbose logging, Iā€™m just assuming that the timestamp_format canā€™t deal with the quotes the datetime is wrapped in (canā€™t change the sensor json output Iā€™m afraid).

"time": "2023-05-04T23:10:00",

doesnā€™t work.

"time": 2023-05-04T23:10:00,

should work based on other examples Iā€™ve found (I havenā€™t fully confirmed with a local json / repro yet).

Just to confirm, you would assume that is the issue too, right? I.e. that timestamp_format canā€™t match values (iso / epoch) in quotes?

Iā€™ve stumbled over execd but I was also looking at processors.regex, to apply a simple regex to remove the quotes, I just didnā€™t find how to get a value from the json and then use the processed value as an input for timestamp_format. Iā€™ll look at read some more if you say that is possible.

Sorry for my ignorance and thanks again for your help!

Valentin

4

The timestamp format needs to be in Go ā€œreference timeā€ which is defined to be the specific time: Mon Jan 2 15:04:05 MST 2006

Therefore you need to use:

timestamp_format = "2006-01-02T15:04:05"
5

Ouch.

I am an idiot. I swear I copied the right datetime initially but I didnā€™t when I created a new config.

Sorry for wasting everyoneā€™s time. Any favorite charitable cause I can send some money towards?

1 Like
6

No you arenā€™t! It happens :slight_smile: