Count of boolean values in Flux query

[sergenbiga]

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”)

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,

[usamaaltaf420]

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")

[sergenbiga]

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;

[sergenbiga]

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

[usamaaltaf420]

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

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"}))
  ])

[sergenbiga]

Thank you for your recommedation, you helped me

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.

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

[grant2]

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

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

[sergenbiga]

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.

[yosiasz]

@usamaaltaf420

ifOperStatus.2

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

[sergenbiga]

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

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.

[usamaaltaf420]

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()

[sergenbiga]

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.