Any query experts that can help with my simple Graph idea out there?

Grafana Dashboards
1

Hello,

I have been asked to poll a switch using Telegraf which I am doing and I’m showing all the ports in a graph.

A value of 1 means the port is up

A value of 2 and 6 means the port is down.

What I have been asked is to add the up ports together, so if 10 ports on the switch go from 2 (down) to 1 (up) you will see the graph increase from 0 to 10 for that period.

This way we can see how active switches are.

All I can get it to show is if the port is up or down.

I this if my Query has something like field = ifOperStatus = 2 then this might help as it would show just the ‘up’ ports and hopefully I can graph this as a total.

I’m not sure if this is possible?

Query looks like this:

SELECT “ifOperStatus” FROM “autogen”.“interface” WHERE (“agent_host” =~ /^$host$/) AND $timeFilter GROUP BY “ifDescr”

but adding a 1 didn’t work

SELECT “ifOperStatus” = ‘1’ FROM “autogen”.“interface” WHERE (“agent_host” =~ /^$host$/) AND $timeFilter GROUP BY “ifDescr”

image
image1487×774 81.9 KB

2

welcome

please provide
grafana version
sample data (looks like influx)

ifOperStatus,agent_host,ifDescr
1,vader,shisham

etc. so that we can try to emulate your data in our environment since we do not have direct access to your influxdb :wink:

3

Hello!

Do you want the data from InfluxDB which I’d have to workout how to do or another way?

It would make my week if I can get this working, thanks.

4

:point_up_2::point_up_2:t6:
Please provide it as sample csv

5

Struggling to get an output to CSV, I can try in the morning, but in Grafana if I change the graph to a table I see this. We just care about graphing any port with a 1. So if you had a switch with 48 ports and we find 10 ports are showing a 1 then the graph would show a value of 10.

image
image1920×859 78.1 KB

6

Not going to type all of that data in the image for you to provide you help. type it as below. It is text, copy it, fill in all of the data and post it here.

time,ifDescr, ifOperStatus
2022-06-16 15:22:29.24.000','x4600',2
2022-06-16 15:22:29.24.000','x4600',2
2022-06-16 15:22:29.24.000','x4600',2
2022-06-16 15:22:29.24.000','x4600',2
2022-06-16 15:22:29.24.000','x4600',2
7

Time,ifDescr,ifOperStatus

15/06/2022 22:29,X460G2-48p-10G4 Port 1,2

15/06/2022 22:29,X460G2-48p-10G4 Port 2,2

15/06/2022 22:29,X460G2-48p-10G4 Port 3,2

15/06/2022 22:29,X460G2-48p-10G4 Port 4,2

15/06/2022 22:29,X460G2-48p-10G4 Port 5,2

15/06/2022 22:29,X460G2-48p-10G4 Port 6,2

15/06/2022 22:29,X460G2-48p-10G4 Port 7,2

15/06/2022 22:29,X460G2-48p-10G4 Port 8,1

15/06/2022 22:29,X460G2-48p-10G4 Port 9,2

15/06/2022 22:29,X460G2-48p-10G4 Port 10,2

15/06/2022 22:29,X460G2-48p-10G4 Port 11,1

15/06/2022 22:29,X460G2-48p-10G4 Port 12,2

15/06/2022 22:29,X460G2-48p-10G4 Port 13,1

15/06/2022 22:29,X460G2-48p-10G4 Port 14,2

15/06/2022 22:29,X460G2-48p-10G4 Port 15,2

15/06/2022 22:29,X460G2-48p-10G4 Port 16,2

15/06/2022 22:29,X460G2-48p-10G4 Port 17,1

15/06/2022 22:29,X460G2-48p-10G4 Port 18,2

15/06/2022 22:29,X460G2-48p-10G4 Port 19,2

15/06/2022 22:29,X460G2-48p-10G4 Port 20,2

15/06/2022 22:29,X460G2-48p-10G4 Port 21,1

15/06/2022 22:29,X460G2-48p-10G4 Port 22,2

15/06/2022 22:29,X460G2-48p-10G4 Port 23,2

15/06/2022 22:29,X460G2-48p-10G4 Port 24,2

15/06/2022 22:29,X460G2-48p-10G4 Port 25,1

15/06/2022 22:29,X460G2-48p-10G4 Port 26,2

15/06/2022 22:29,X460G2-48p-10G4 Port 27,2

15/06/2022 22:29,X460G2-48p-10G4 Port 28,1

15/06/2022 22:29,X460G2-48p-10G4 Port 29,2

15/06/2022 22:29,X460G2-48p-10G4 Port 30,2

15/06/2022 22:29,X460G2-48p-10G4 Port 31,2

15/06/2022 22:29,X460G2-48p-10G4 Port 32,1

15/06/2022 22:29,X460G2-48p-10G4 Port 33,2

15/06/2022 22:29,X460G2-48p-10G4 Port 34,1

15/06/2022 22:29,X460G2-48p-10G4 Port 35,2

15/06/2022 22:29,X460G2-48p-10G4 Port 36,1

15/06/2022 22:29,X460G2-48p-10G4 Port 37,2

15/06/2022 22:29,X460G2-48p-10G4 Port 38,1

15/06/2022 22:29,X460G2-48p-10G4 Port 39,1

15/06/2022 22:29,X460G2-48p-10G4 Port 40,1

15/06/2022 22:29,X460G2-48p-10G4 Port 41,2

15/06/2022 22:29,X460G2-48p-10G4 Port 42,2

15/06/2022 22:29,X460G2-48p-10G4 Port 43,2

15/06/2022 22:29,X460G2-48p-10G4 Port 44,2

15/06/2022 22:29,X460G2-48p-10G4 Port 45,6

15/06/2022 22:29,X460G2-48p-10G4 Port 46,6

15/06/2022 22:29,X460G2-48p-10G4 Port 47,6

15/06/2022 22:29,X460G2-48p-10G4 Port 48,6

1 Like
8

hello i think you want to display a stacked graph, you can achieve this with timeseries panel and the stack option.

9

Yeah I tried that and it for some reason made no difference to the graph, plus I need to exclude the values of 2 and 6 and only focus on the 1’s (port is up).

image
image1884×779 116 KB

10

try to add group by tag and time, i think it’s group format issue.
if it’s not working, try to play with grafana transformation feature, like “label to fields” and “prepare time series”
https://grafana.com/docs/grafana/latest/basics/timeseries-dimensions/

11

How would I tackle excluding the ports with a value of 2 and 6 though?

12

classic filter

|> filter(fn: (r) => r["_value"] != 2 or r["_value"] != 6)

13

Where do I put that?

14

it’s a “where” clause : _value != 1

15

Sorry I think I’m being stupid, where in that query do I add that, I’m quite new to Grafana that’s all.

16

I believe OP is using InfluxQL not Influx.

SELECT "ifOperStatus", "ifOperStatus" 
  FROM "router-rp"."network" 
WHERE   $timeFilter and "ifOperStatus" != 2 and  "ifOperStatus" != 6 GROUP BY "type"

image
image1734×987 72 KB

17

Close, so how do you stack this? So this shows 13 up?

18

to explain a little more : you can use a time series panel to display stacked data, only if mutiple data point are on the same timespamp/date .

This is what you want to do, i believe

image
image1504×282 23.2 KB

my data look like this :

image
image1520×358 21.9 KB

my query look like this :
SELECT gt.name as titre,gi.name,CAST(DATE_FORMAT(gt.date, ‘%Y-%m-%d 00:00:00’) as Date) as ‘time’,urgency+priority+impact as criticite from glpi_tickets gt
join glpi_itilcategories gi on gt.itilcategories_id = gi.id
JOIN glpi_entities ge ON gt.entities_id = ge.id
where gi.name like ‘blabla’ and $__timeFilter(gt.date) group by titre,criticite

Hope this example will help,
I think you need to work on your SQL skill, it’s not really about Grafana.
A good way to see this is write query to match the data format Grafana is expecting in order to display what you want.

1 Like
19

Wow that is advanced, I think InfluxDB is based on SQL then?

I was having a look too and got closer with just:

SELECT count(“ifOperStatus”) FROM “interface” WHERE (“agent_host” =~ /^$host$/ AND “ifOperStatus” = 1) AND $timeFilter GROUP BY time(1m) fill(none)

But it should total to only 10 ports being up, so not quite there. I just want to count the 1’s.

image
image1442×654 52.5 KB

20

“I think InfluxDB is based on SQL then?”
it’s not, Time series database is a different paradigm :

i’m not sure what you want to do anymore:

image
image424×518 16.5 KB

you may can use calculation option