Showing Switch Port Status in a single table using multiple queries

Grafana
, ,
1

Hi,

this is the continuation of topic shwoint switch port status in flux

Using two queries (8 more to go), I have currently the correct output for a switch port status.
See summary of former topic below.

Summary

Query A

    |> 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:

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 how it currently shows 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.

My target configuration should form a table looking like this:

image
image1381×1181 104 KB

In order to achieve this I want to add a row ‘title’ below the Metric.
like this:

Ports UP       9
Ports Down       43

I could use the override fields but both queries return a field with the name _value:
image
unfortunately the same is true when I want to override fields returned by query:
image

So either I need a solution to add a custom ‘Display Name’ (I assume) in order to get the distinct rows I’m looking for, or a way to change the representation of the table in general.

I hope anyone faced similar problems and knows how to help in this case.
take care
mateo

2

Use map() to add column with required title to your queries:

For example:

    |> map(fn: (r) => ({ r with Metric: "Ports UP" }))
3

@mateo @ebabeshko is correct. You use map() to “remap” each rows column and values.

I should also note that you could technically do this all in a single “query” (single Flux script with multiple queries that are combined in a single output), but if Grafana’s merge/join functionality is working for you, go with it.

4

YES!

That looks good, only the order was reversed, the transformation ‘Organize fields by name’ helped with that:
image

Now up to my next queries. I tried the same approach in order to have that same column headers. But I’m losing all values when I start to group the values after duplicating the column (hence the comments in order to see what happens when)

from(bucket: "tigstack")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "InterfaceTable")
  |> filter(fn: (r) => r["agent_host"] == "192.168.99.101")
  |> filter(fn: (r) => r["ifIndex"] == "1" or 
                             r["ifIndex"] == "11" [... cropped for readability])
  |> filter(fn: (r) => r["_field"] == "ifSpeed")  
  |> duplicate(column: "_value", as: "1gig")
  |> keep(columns: ["1gig"])
//  |> group(columns: ["1gig"])

//  |> filter(fn: (r) => int(v: r.ifSpeed) == 10000000000)
//  |> filter(fn: (r) => r._value == "1000000000")

I thought that the group(columns: …) isolates the selected column including all values, but the values get overriden by zeros.

How can I keep the values without changing the approach drasically?

5

keeps only column 1gig and drops all other. is that what you want?

6

Actually yes, but with the values included. I then compare the _value with bit values for ifSpeed in order to get single rows for all options possible in order to get to a panel looking like this (cropped):

image
image1337×229 26.7 KB

7

@ebabeshko
of course any other approach that leaves me with a Metric and a Value column in the and is desired as well, in case you have an idea on how to achieve this :wink: