Showing Switch Port Status in Flux

InfluxDB 2 Dashboards
, ,
1

Hi everyone,

I need to count all Ports that have either status 1 (=UP) or 2 (=DOWN) in order to show a simple:
31 Ports are UP
17 Ports are DOWN

Following is the query I’m struggling with:

from(bucket: "tigstack")
  |> range(start: -1m)
  |> filter(fn: (r) => 
    r._measurement == "InterfaceTable" and
    r.agent_host == "${ip}" and
    r.ifIndex =~ /${port:pipe}/ and
    r._field == "ifOperStatus" and
    r._value == "1"
   )
  |> drop(columns: ["user", "host", "_measurement"])
  |> duplicate(column: "_value", as: "PortUP")
  |> group(columns: ["PortUP"])
  |> limit(n: 1)
  |> aggregateWindow(every: 1m, fn: count, createEmpty: false)

The aim is to get a count of all ifOperStatus _values == “1” which would mean state UP.
But as soon as I add the filter for

r._value == “1”

I end up with an error: "

 compilation failed: error @10:6-14:1: expected RPAREN, got EOF

A view of the raw data looks without above mentioned == “1” like this:

table           _value              _start 	            _stop              _time                  PortUP
_result       no group           group               group              no group             group (long)
0	          1               <timestamp>         <timestamp>       <timestamp>               1
1	          1               <timestamp>         <timestamp>       <timestamp>               2

note: I used the variables I defined in grafana for easier readability

Any help will be much appreciated I’m stuck for 2 w on this panel ¯(°_o)/¯!

2

@mateo The error indicates that a right parenthesis is missing somewhere, but I can’t find one missing in the query you provided. Was this copied straight from Grafana?

Some other questions/thoughts:

  • Are 1 and 2 the only possible values for this field or are there others?
  • You don’t have to drop columns if you’re going to regroup and then count. When you run count, all columns not in the group key will get dropped anyway.
  • You shouldn’t limit to 1. This will make it so your count will always be one since count returns the number of rows with non-null values in the _value column. If you limit by 1, your count will always be 1.
  • You don’t need to use aggregateWindow() in this query. If your every matches the time range of your query, you don’t really even need to window. You can just run the aggregate function, count().

The following query will give you a count for each variant of ifOperStatus/PortUP.

from(bucket: "tigstack")
    |> range(start: -1m)
    |> filter(fn: (r) => 
        r._measurement == "InterfaceTable" and
        r.agent_host == "${ip}" and
        r.ifIndex =~ /${port:pipe}/ and
        r._field == "ifOperStatus"
    )
    |> duplicate(column: "_value", as: "PortUP")
    |> group(columns: ["PortUP"])
    |> count()
1 Like
3

Hi @scott

Thank you for your answer!

According to your questions/remarks following I provide answers:

Was this copied straight from Grafana?

Yes, I thought so but only influxdb frontend delivers the error message, in grafana I simply get ‘No data’
I checked again and found a bracked too much. Now it gives in influxdb the error:

runtime error @56:6-56:40: filter: cannot compile @ 56:17-56:39: unsupported binary expression int == string

Are 1 and 2 the only possible values for this field or are there others?

There are multiple values possible for ifOperStatus:

null -> N/A
1 -> UP
2 -> DOWN
3 -> testing
4 -> unknown
5 -> dormant
6 -> not present
7 -> lower layer down

You don’t have to drop columns if you’re going to regroup and then count. When you run count, all columns not in the group key will get dropped anyway.

This is good to know, I’ll consider this in the future

You shouldn’t limit to 1. This will make it so your count will always be one since count returns the number of rows with non-null values in the _value column. If you limit by 1, your count will always be 1.

Hmm, hence I misunderstood the function limit. Also a learning that’ll help me going along.

You don’t need to use aggregateWindow() in this query. If your every matches the time range of your query, you don’t really even need to window. You can just run the aggregate function, count().

So multiple aggregations don’t improve the output, I get that and I’m still learning on how to control the handling of the output from the metrics. Therefore your guidance helps already.

Now my remarks to the query you provided:

1.) Your query gives me a total count of ‘106’ and the swich I’m pointing to has only very few ports in state UP. It looks like your query iterates (as declared) over all interfaces including VLANs:
(The view from query inspector:)

from(bucket: "tigstack")
    |> range(start: -1m)
    |> filter(fn: (r) => 
        r._measurement == "InterfaceTable" and
        r.agent_host == "192.168.99.100" and
        r.ifIndex =~ /1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|25|26|27|28|29|30|31|32|33|34|35|36|37|38|39|40|41|42|43|44|45|46|47|48|49|50|51|52|102|200|201|251|300|401|601|602|603|606|1100|1102|1103|1104|1105|1106|2405|4324|4325|4326|4327|4328|4329|4330|4331/ and
        r._field == "ifOperStatus"
    )
    |> duplicate(column: "_value", as: "PortUP")
    |> group(columns: ["PortUP"])
    |> count()

i.e. I need to regex somehow that the statement /${port:pipe}/ stops at the management interfaces. I tried that on the variable already ${port} with the statement:
(5[0-4]|[1-4][0-9]|0?[1-9])
But this doesn’t limit the port indices in the query. How can I narrow the query down to the physical interfaces available? We have also a few switches that have only 24 ports so query should accomodate for that case.
Do you have any idea on how to do that?

2.) Your query counts all interfaces’ ifOperStatus value despite the value given, as you’ve mentionend in your answer. Which leads me back to my initial problem on how to isolate the _value == “1” from all the others for this query.

It may help you to see what my target configuration should look like:
It’s this template grafana_switch_overview_dashboard
Here I’m working on the panel in the top right corner with the query I provieded.

I sincerly hope that you can provide further help in this. It’s my first project using the telegraf influxdb grafana combination and after 3 months it still feels like fumbling around and just get it good enough for production usage :slight_smile:

4

Alright, I seem to have made some progress:

Iteration over all interfaces in influxdb gives now a reasonable output of ‘8’ when I use the toString funktion on the duplicated column.

Summary 
from(bucket: "tigstack")
  |> range(start: -1m)
  |> filter(fn: (r) => r["_measurement"] == "InterfaceTable")
  |> filter(fn: (r) => r["agent_host"] == "192.168.99.100")
  |> filter(fn: (r) => r["ifIndex"] == "1" or 
                       r["ifIndex"] == "11" or 
                       r["ifIndex"] == "10" or 
                       r["ifIndex"] == "12" or 
                       r["ifIndex"] == "13" or 
                       r["ifIndex"] == "14" or 
                       r["ifIndex"] == "15" or 
                       r["ifIndex"] == "16" or 
                       r["ifIndex"] == "17" or 
                       r["ifIndex"] == "18" or 
                       r["ifIndex"] == "19" or 
                       r["ifIndex"] == "2" or 
                       r["ifIndex"] == "20" or 
                       r["ifIndex"] == "21" or 
                       r["ifIndex"] == "22" or 
                       r["ifIndex"] == "23" or 
                       r["ifIndex"] == "24" or 
                       r["ifIndex"] == "25" or 
                       r["ifIndex"] == "26" or 
                       r["ifIndex"] == "27" or 
                       r["ifIndex"] == "28" or 
                       r["ifIndex"] == "29" or 
                       r["ifIndex"] == "3" or 
                       r["ifIndex"] == "30" or 
                       r["ifIndex"] == "31" or 
                       r["ifIndex"] == "32" or 
                       r["ifIndex"] == "33" or 
                       r["ifIndex"] == "34" or 
                       r["ifIndex"] == "35" or 
                       r["ifIndex"] == "36" or 
                       r["ifIndex"] == "37" or 
                       r["ifIndex"] == "38" or 
                       r["ifIndex"] == "39" or 
                       r["ifIndex"] == "4" or 
                       r["ifIndex"] == "40" or 
                       r["ifIndex"] == "41" or 
                       r["ifIndex"] == "42" or 
                       r["ifIndex"] == "43" or 
                       r["ifIndex"] == "44" or 
                       r["ifIndex"] == "45" or 
                       r["ifIndex"] == "46" or 
                       r["ifIndex"] == "47" or 
                       r["ifIndex"] == "48" or 
                       r["ifIndex"] == "49" or 
                       r["ifIndex"] == "50" or 
                       r["ifIndex"] == "51" or 
                       r["ifIndex"] == "52")
  |> filter(fn: (r) => r["_field"] == "ifOperStatus")
  |> duplicate(column: "_value", as: "PortUP")
  |> group(columns: ["PortUP"])
  |> toString()
  |> filter(fn: (r) => r._value == "1")
  |> count()
 

|table           _value                          PortUP
_result|         no group                      group
                     long                              long
0                    8                                  |1|

This means some kind of success (!) I’ll check out whether this is the solution and provide feedback asap :slight_smile:

5

Update:

I’m not getting the correct values as of yet, but the initial problem is solved. Thank you @scott for providing help.

My current query looks like this and throws a new error, this should be syntax…

from(bucket: "tigstack")
    |> range(start: -1m)
    |> filter(fn: (r) => 
        r._measurement == "InterfaceTable" and
        r.agent_host == "${ip}" and
        r.ifIndex =~ /${port:pipe}/ where r.ifIndex <= 52 and
        r._field == "ifOperStatus"
    )
    |> duplicate(column: "_value", as: "PortUP")
    |> group(columns: ["PortUP"])
    |> toString()
    |> filter(fn: (r) => r._value == "1")
    |> count()

‘invalid: error @6:286-6:291: undefined identifier where error @3:19-6:291: invalid binary operator <INVALID_OP> error @6:292-6:293: undefined identifier r error @7:9-7:10: undefined identifier r error @3:19-7:35: invalid binary operator <INVALID_OP>’

I’ll try to wrap my head around this after lunch. Marked as solved for now

6

I think the error is because of where in your filter call. I think the following should work:

from(bucket: "tigstack")
    |> range(start: -1m)
    |> filter(fn: (r) => 
        r._measurement == "InterfaceTable" and
        r.agent_host == "${ip}" and
        r.ifIndex =~ /${port:pipe}/ and
        r._field == "ifOperStatus"
    )
    |> filter(fn: (r) => int(v: r.ifIndex) <= 52)
    |> duplicate(column: "_value", as: "PortUP")
    |> group(columns: ["PortUP"])
    |> toString()
    |> filter(fn: (r) => r._value == "1")
    |> count()

Note: I moved the filter with the integer casting into a separate filter call so that Flux could push the previous filter call down to the storage tier. This will help a bit with query performance. Casting operations have to be done in memory and can’t be pushed down to storage.

1 Like
7

@scott thanks again.

coming from sql dbs I really find it difficult to adapt.

I instead used the regex

(5[0-4]|[1-4][0-9]|0?[1-9])

for excluding all Vlans from the queries already for the variable {$port}. I won’t want to take them into consideration going forward with the current dashboard.

Still I’m not able to extract the values as I can see the status on the switch itself, so a little more tinkering is necessary and I’ll update this thread with the final solution. hopefully my learnings are then useful to others as well.
take care
mateo

8

Hey,

it turns out adding the limitation in the query itself sanitizes the data returned in the panel. So it shows the true value.
I don’t get that bc query inspector shows no differences in the values inserted for the variable, but here we are.

i.e. I have added two queries that return the states of the switch ports I need to see:

Query A:

Summary 
    |> range(start: -1m)
    |> filter(fn: (r) => 
        r._measurement == "InterfaceTable" and
        r.agent_host == "${ip}" and
        r.ifIndex =~ /${port:pipe}/ and
        r._field == "ifOperStatus"
    )
    |> filter(fn: (r) => int(v: r.ifIndex) <= 52)
    |> duplicate(column: "_value", as: "PortUP")
    |> group(columns: ["PortUP"])
    |> toString()
    |> filter(fn: (r) => r._value == "1")
    |> count()

Query B:

Summary 
from(bucket: "tigstack")
  |> range(start: -1m)
  |> filter(fn: (r) => 
        r._measurement == "InterfaceTable" and
        r.agent_host == "${ip}" and
        r.ifIndex =~ /${port:pipe}/ and
        r._field == "ifOperStatus"
    )
    |> filter(fn: (r) => int(v: r.ifIndex) <= 52)
    |> duplicate(column: "_value", as: "PortDown")
    |> group(columns: ["PortDown"])
    |> toString()

    |> filter(fn: (r) => r._value == "2")
    |> count()

Here’s the view in grafana

image
image1745×289 17.7 KB

I used the transformation “merge tables/series” for getting both queries to be shown in the same panel.

Now a new problem arises that will be posted in a new topic:
Hint: I need the rows to have a custom text.

Ports UP       9
Ports Down       43

So here is the new topic:

Showing Switch Port Status in a single table using multiple queries