Count of boolean values in Flux query

Grafana InfluxDB
1

Hello everyone,

I have a problem with my data. My data separates the value as ‘‘1’’ and ‘‘2’’ and I changed it ‘‘UP’’ and ‘‘DOWN’’ . ‘‘UP’’ symbolizes ‘‘1’’ and ‘‘DOWN’’ symbolizes ‘‘2’’. My question is how can add my values like how much ‘‘1’’ I have and ‘‘2’’ I have ? I wrote one script but it ‘‘1’’ and ‘‘2’’ separately not together.

Here is my script that I mentioned for InfluxDB;

from(bucket: “telegraf”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“agent_host”] == “192.168.0.10”)
|> filter(fn: (r) => r[“_field”] == “ifOperStatus.1” or r[“_field”] == “ifOperStatus.10” or r[“_field”] == “ifOperStatus.2” or r[“_field”] == “ifOperStatus.3” or r[“_field”] == “ifOperStatus.4” or r[“_field”] == “ifOperStatus.5” or r[“_field”] == “ifOperStatus.6” or r[“_field”] == “ifOperStatus.7” or r[“_field”] == “ifOperStatus.8” or r[“_field”] == “ifOperStatus.9”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

aqaq
aqaq948×235 31.6 KB

Here is my grafana dashboard. I want to see total number of ‘‘UP’’ and ‘‘DOWN’’ I shared both maybe there is a problem in my script I have to add something to calculate my datas or perhaps I need to create something inside of grafana. Therefore, I shared both.

Thank you in advance,

2

Please try this query

from(bucket: "telegraf")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["agent_host"] == "192.168.0.10")
  |> filter(fn: (r) => r["_field"] == "ifOperStatus.1" or r["_field"] == "ifOperStatus.10" or r["_field"] == "ifOperStatus.2" or r["_field"] == "ifOperStatus.3" or r["_field"] == "ifOperStatus.4" or r["_field"] == "ifOperStatus.5" or r["_field"] == "ifOperStatus.6" or r["_field"] == "ifOperStatus.7" or r["_field"] == "ifOperStatus.8" or r["_field"] == "ifOperStatus.9")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")
  |> group(columns: ["_value"], mode: "by")
  |> count(column: "_value")
3

Hello,

Firstly, I appreciate for your help. I tried this query but nothing is changed. Here is the picture that I tried and I got this warning that is located left side of the picture;

tried
tried1366×786 153 KB

4

It did not calculate how much ‘‘UP’’ and ‘‘DOWN’’ I have. I want this result in fact. There are 4 ‘‘DOWN’’ and 6 ‘‘UP’’. I want to see this in the chart becuase I will use it for the further things.

Thank you in advance

5

Can you try it like this and I am not an expert also :slight_smile:

from(bucket: "telegraf")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["agent_host"] == "192.168.0.10")
|> filter(fn: (r) => r["_field"] == "ifOperStatus.1" or r["_field"] == "ifOperStatus.10" or r["_field"] == "ifOperStatus.2" or r["_field"] == "ifOperStatus.3" or r["_field"] == "ifOperStatus.4" or r["_field"] == "ifOperStatus.5" or r["_field"] == "ifOperStatus.6" or r["_field"] == "ifOperStatus.7" or r["_field"] == "ifOperStatus.8" or r["_field"] == "ifOperStatus.9")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: "mean")
|> count(column: "_value", fn: (r) => r._value == 1)
|> yield(name: "count_1")
|> count(column: "_value", fn: (r) => r._value == 2)
|> yield(name: "count_2")

In the pie chart visualization settings, you will need to specify which columns from the query results to use as the data for the pie chart. In this case, you will want to use the count_1 and count_2 columns as the data for the pie chart.

or this will also help

from(bucket: "telegraf")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["agent_host"] == "192.168.0.10")
  |> filter(fn: (r) => r["_field"] == "ifOperStatus.1" or r["_field"] == "ifOperStatus.10" or r["_field"] == "ifOperStatus.2" or r["_field"] == "ifOperStatus.3" or r["_field"] == "ifOperStatus.4" or r["_field"] == "ifOperStatus.5" or r["_field"] == "ifOperStatus.6" or r["_field"] == "ifOperStatus.7" or r["_field"] == "ifOperStatus.8" or r["_field"] == "ifOperStatus.9")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")
  |> count(column: "_value", fn: (r) => r._value == 1)
  |> rename(columns: {count: "value"})
  |> map(fn: (r) => ({r with label: "1"}))
  |> union(tables: [
    count(column: "_value", fn: (r) => r._value == 2)
    |> rename(columns: {count: "value"})
    |> map(fn: (r) => ({r with label: "2"}))
  ])
1 Like
6

Thank you for your recommedation, you helped me :slight_smile:

I tried like you said. Normally, I write it down in InfluxDb that copy to my grafana. I think there is problem with format because I got some errors when I write InfluxDB. Here is the picture that I got the error.

err
err1163×522 46.6 KB

Additionally, I got no data inside of grafana when I copied this.

7

From your original post, are we to assume that “ifOperStatus.5” and “ifOperStatus.10” are always UP, and anything else is DOWN?

image
image859×310 128 KB

EDIT: Nevermind, I did not thoroughly read the above posts. Is the question resolved now?

8

These datas are the situation of our interfaces and consist of 10 datas, namely ifOperStatus.1 , 2, 3 … 10. Some of them are DOWN status and some of them are UP status. All I want to do that I count how much ‘‘DOWN’’ and ‘‘UP’’. I got some recommedation from @usamaaltaf420 , however we could not resolve it due to format of script.

9

This approach of naming fields is worrisome, unsustainable and unmanageable to me.

So if you have 1k OpenStatus…

Should it be a key value approach attribute: value

10

As a matter of fact, this machine has 10 interface. I shared picture for you.

text
text758×211 26.5 KB

It does not matter how much you have. However,it matters when you have 1k because especially customer want to see total number of status. I used elactic search before and I can categorize easily how much ‘‘UP’’ and ‘‘DOWN’’ I have. I think it should be easy but I looked every side of grafana and script however I could not resolve it.

1 Like
11

using the _value field as the label and the _count field as the value.
if this doesn’t work I need to create a database to test it my self

from(bucket: "telegraf")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["agent_host"] == "192.168.0.10")
  |> filter(fn: (r) => r["_field"] == "ifOperStatus.1" or r["_field"] == "ifOperStatus.10" or r["_field"] == "ifOperStatus.2" or r["_field"] == "ifOperStatus.3" or r["_field"] == "ifOperStatus.4" or r["_field"] == "ifOperStatus.5" or r["_field"] == "ifOperStatus.6" or r["_field"] == "ifOperStatus.7" or r["_field"] == "ifOperStatus.8" or r["_field"] == "ifOperStatus.9")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")
  |> group(columns: ["_value"])
  |> count()
1 Like
12

Hello, i tested it and it did not work. However, if you have free time, we can work on that together via teams or zoom because I work at home except Tuesday and Thursday. We can arrange a time if you wish.