Count of different occurences of fields per timestamp

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.

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)

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.

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:

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:

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!

Hi @Matti

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