Count of different occurences of fields per timestamp

InfluxDB 2
,
1

Hey,

Currently using Influx 2 as our TimeSeries Database in a network monitoring project. In that sense, I’m currently querying a switch for all the states of the interfaces. The query returns a table per interface, with the _value field set to UP|DOWN|NOT PRESENT.
Another field stores the interface value: InterfaceX/Y.

For some simple monitoring, I’d just like to get a table, which will be used to plot a graph (x-axis = time, y-axis = amount) that just counts the amount of UP states for a given timestamp.

I get data every 10 seconds and the timestamps match, I have tried to combine the data with transforms, but so far this has been fruitless.
However, attached is the “debug version” of the transform which shows that perhaps all my data is actually a single table? Imgur: The magic of the Internet

When I run a query as such, I get relatively close, but not really my end goal.

from(bucket:"telemetry")
  |> range(start: -1h)
  |> filter(fn: (r) => r._value == "UP")
  |> group()
  |> window(period: 10s)
  |> count()

I get all the different timeslots with the _value having the actual number. But how it gets there, or how I continue to format this so it could be used as a neat graph would be quite helpful. Imgur: The magic of the Internet

Any suggestions would be extremely helpful, especially since swapping the group(), count(), and window() with places instantly gives completely different data.

Any and all clarifications and help are welcome.

2

Hi @Matti and welcome to the InfluxData forum.

Let’s put aside Grafana for now and see what your output looks like in Influx Data Explorer? Can you show that and paste here as a CSV? (obfuscate as needed)

3

Hello,

Attached is the query of the raw data in which I’ve just replaced the serial number on the switch with an arbitrary name. CSV available here: #group,false,false,true,true,false,false,true,true,true,true#datatype,string,l - Pastebin.com (can’t upload files as new user)

To reiterate what I’d like is for a given given timestamp X, where _value == "UP", simply count the amount of different interface_names.
If I understand this, I could hopefully augment this myself down the line to do the same for values other than “UP”.

Hope this clarifies the request. I’m also currently trying to take the influx university courses, so perhaps I’ll be able to answer this myself in the future.

4

Hi @Matti and thanks for sharing your data. Sent it into my junkbucket, filtered with

  |> filter(fn: (r) => r["interface_name"] == "Ethernet1/1" or r["interface_name"] == "Ethernet1/2" )
  |> filter(fn: (r) => r["_value"] == "UP")

and got this:

image
image407×537 34.5 KB

You wrote that you’d like to count the different interface_names for a given timestamp. Given the above data, we should have a count of 2 (one for Ethernet1/1 and one Ethernet1/2) for each of the 5 unique timestamps. We can group by _time and then count the interface_name.

Full query:

from(bucket: "junkbucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "3t02-s1-a-testnet")
  |> filter(fn: (r) => r["_field"] == "/interfaces/interface/state/admin-status")
  |> filter(fn: (r) => r["interface_name"] == "Ethernet1/1" or r["interface_name"] == "Ethernet1/2" )
  |> filter(fn: (r) => r["target"] == "SerialSwitch1")
  |> filter(fn: (r) => r["_value"] == "UP")
  |> group(columns: ["_time"]) // Group by timestamp
  |> count(column: "interface_name")
  |> yield(name: "test")

and output:

image
image1329×377 20.6 KB

5

Hey,

Yes this is correct, I have altered the interface search to include all of them by modifying it to the following: |> filter(fn: (r) => r["interface_name"] != "")

Finally, in my current latest tests, the timestamps of the different data is about 1-2 milliseconds different, which makes the group operator fail. As a workaround, I group on _start instead of _time. This gives me the correct time, however, the _time field is gone at that moment and only the _start is present. Is there a way to overwite the _time field with _start value for the output table?

Thanks for the help so far!

6

Hi @Matti

How about using the truncateTimeColumn() function to fix the above?

1 Like
7

Yes, this fixed the problem and is quite a clean solution. I originally fixed it by just windowing on 10seconds and then duplicating the _stop column to _time, but this function is cleaner IMHO.

Thank you for the assistance, this has been very helpful to understand the keying and grouping of the tables.

8

That’s great. Could you please mark the above as “Solution” so others can find it in the future?

9

Done, hopefully this is helpful for others. I have now tried to utilize this in grafana, however there it throws an error stating that the time field is missing.
However, if I download the .csv the data looks like this:

#group,false,false,true,false
#datatype,string,long,dateTime:RFC3339,long
#default,_result,,,
,result,table,_time,interfaces_up_count
,,0,2024-02-26T11:27:10Z,80
,,1,2024-02-26T11:27:20Z,80
,,2,2024-02-26T11:27:30Z,80
,,3,2024-02-26T11:27:40Z,80
,,4,2024-02-26T11:27:50Z,80
,,5,2024-02-26T11:28:00Z,80
,,6,2024-02-26T11:28:10Z,80
,,7,2024-02-26T11:28:20Z,80
,,8,2024-02-26T11:28:30Z,80

I’m trying to use the timeseries on grafana if that helps. If needed I can open a new thread to address this, however it feels like I’m missing just a tiny puzzle piece to complete this.