Flux Subquery for State Timeline

Fluxlang
1

I have this datastructure:

|Hosts   | Status | Time |
|Host A |     0      |  a  |
|Host A |     1      |  b   |
|Host A |     0      |  c   |
|Host B |     0      |  a  |
|Host B |     0      |  b  |
|Host B |     0      |  c  |

and so on…
Where 0 is OK and 1 is CRIT state.

Now - in Grafana I want to have a STATE-TIMELINE showing only hosts with Problems in the last x hours.

Right now I have this query:

from(bucket: "XYZ")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] == "service_state")
  |> filter(fn: (r) => r["service_description"] == "Check_MK")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> filter(fn: (r) => r._value != 0 )
  |> keep(columns: ["host_name","_time", "_value"])
  |> yield(name: "mean")

Now the problem with this query is that it filters out ALL 0 states. That means a recovery for a problem host is lost.

Which results in the following:

Bildschirmfoto vom 2023-07-31 11-20-00
Bildschirmfoto vom 2023-07-31 11-20-00848×310 22.6 KB

What I want is the all hosts with problems are shown in state-timeline but recovery records(state 0) should not be dropped.

In SQL I would solve this with a subquery. In Flux I have no clue how to fix this.

2

Ist this simple requirement really so hard to realize ?
I am wondering what the advantage over 30 years old SQL is then.

3

I mean, Im pretty sure is easy. the think is that I did not understand what you want to acomplish.

if you want state 0 to be there why are you filtering everything that is different from zero in the first place?

It is just different than SQL I struggle with SQL and sub queries because I are more used to Flux, but both have their use cases SQL is a relational database, InfluxDB is a time series.

sure, there is an overlap in the things you can do with but there are some stuff that is better suited for each type of database.

and just a Hint. Flux is like a top-down structure, you remove things that you don’t want and then use aggregate functions or transformations to calculate specific things.

second hint you have also the option to use FluxQL which has a similar syntax to SQL.