Timestamp_format if timestamp value is iso8601 but wrapped in quotes?

Valentiiiin · May 7, 2023, 8:20pm

Hi folks,

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

{
    "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

Anaisdg · May 8, 2023, 10:33am

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
gjson/SYNTAX.md at v1.7.5 · tidwall/gjson · GitHub
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:

github.com

influxdata/telegraf/blob/master/plugins/processors/execd/README.md

# Execd Processor Plugin

The `execd` processor plugin runs an external program as a separate process and
pipes metrics in to the process's STDIN and reads processed metrics from its
STDOUT.  The programs must accept influx line protocol on standard in (STDIN)
and output metrics in influx line protocol to standard output (STDOUT).

Program output on standard error is mirrored to the telegraf log.

Telegraf minimum version: Telegraf 1.15.0

## Caveats

- Metrics with tracking will be considered "delivered" as soon as they are passed
  to the external process. There is currently no way to match up which metric
  coming out of the execd process relates to which metric going in (keep in mind
  that processors can add and drop metrics, and that this is all done
  asynchronously).
- it's not currently possible to use a data_format other than "influx", due to
  the requirement that it is serialize-parse symmetrical and does not lose any

This file has been truncated. show original

here’s an example repo that uses it so you can understand the file structure

Valentiiiin · May 8, 2023, 1:29pm

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

jpowers · May 8, 2023, 1:32pm

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"

Valentiiiin · May 8, 2023, 1:42pm

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?

jpowers · May 8, 2023, 1:53pm

No you aren’t! It happens