2

I am new to flux query language (with Influx DB 2) and cant find a solution for the following problem:

I have data with changing true and false values: enter image description here

I was able to calculate the time in seconds until the next change by using the events.duration function: enter image description here

Now I want to calculate the total time and the time of all "false"-events and after that I want to calculate the percentage of all false events. I tryed the following

import "contrib/tomhollingworth/events"

total = from(bucket: "********")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "********")
  |> filter(fn: (r) => r["Server"] == "********")
  |> filter(fn: (r) => r["_field"] == "********")
  |> filter(fn: (r) => r["DataNode"] == "********")

  |> events.duration(
    unit: 1s,
    columnName: "duration",
    timeColumn: "_time",
    stopColumn: "_stop"
)
  |> sum(column: "duration")

  |> yield(name: "total")

downtime = from(bucket: "********")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "********")
  |> filter(fn: (r) => r["Server"] == "********")
  |> filter(fn: (r) => r["_field"] == "********")
  |> filter(fn: (r) => r["DataNode"] == "********")

  |> events.duration(
    unit: 1s,
    columnName: "duration",
    timeColumn: "_time",
    stopColumn: "_stop"
  )
  |> pivot(rowKey:["_time"], columnKey: ["_value"], valueColumn: "duration")
  |> drop(columns: ["true"])
  |> sum(column: "false")
  |> yield(name: "downtime")

downtime_percentage = downtime.false / total.duration

With this I am getting the following error error @44:23-44:31: expected {A with false:B} but found [C] I also tryed some variations but couldnet get it to work.

I guess I am getting some basic things wrong but I couldnt figure it out yet. Let me know, if you need more information.

JCoordes
  • 147
  • 4
  • 10

1 Answers1

1

I have found a way to solve my problem. Although I am sure that there is a more elegant solution, I document my way here, maybe it helps someone and we can improve it together.

 import "contrib/tomhollingworth/events"

//Set time window in seconds (based on selected time)
time_window = int(v:  v.timeRangeStart)/-1000000000

//Filter (IoT-)Data
data= from(bucket: "*******")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "*******")
  |> filter(fn: (r) => r["Server"] == "*******")
  |> filter(fn: (r) => r["Equipment"] == "*******")
  |> filter(fn: (r) => r["DataNode"] == "******")
  
  //Use events.duration to calculate the duration in seconds of each true/false event. 
  |> events.duration(
    unit: 1s,
    columnName: "duration",
    timeColumn: "_time",
    stopColumn: "_stop"
  )    

//Sum up the event times via "sum()" and save them as an array variable via "findColumn()". This is the only way to access the value later (As far as I know. please let me know if you know other ways!).
total_array = data
    |> sum(column: "duration")
    |> findColumn(
        fn: (key) => key._field == "*******",
        column: "duration",
    )

//Calculate "missing time" in seconds in the time window, because the first event in the time window is missing.
missing_time = time_window - total_array[0]

//Create an array with the first event to determine if it is true or false
first_value_in_window = data
    |> first()
    |> findColumn(
        fn: (key) => key._field == "*******",
        column: "_value",
    )

//Calculate the downtime by creating columns with the true and false values via pivot. Then sum up the column with the false values
downtime = data
    |> map(fn: (r) => ({ r with duration_percentage: float(v: r.duration)/float(v: time_window) }))
     |> pivot(rowKey:["_time"], columnKey: ["_value"], valueColumn: "duration_percentage")
    |> map( fn: (r) => ({r with 
    downtime: if exists r.false then 
            r.false
        else
            0.0    
    }))
    |> sum(column: "downtime")
   
//Create an array with the downtime so that this value can be accessed later on
downtime_array = downtime
    |> findColumn(
        fn: (key) => key._field == "PLS_Antrieb_laeuft",
        column: "downtime",
    )

//If the first value in the considered time window is true, then the remaining time in the time window (missing_time) was downtime. Write this value in the column "false_percentage_before_window".
//The total downtime is calculated from the previously calculated sum(downtime_array) and, if applicable, the downtime of the remaining time in the time window if the first value is true (first_value_in_window[0])
data
    |> map( fn: (r) => ({r with 
     false_percentage_before_window: if first_value_in_window[0] then 
            float(v: missing_time)/float(v: time_window)
         else
            0.0   
     }))
     |> map(fn: (r) => ({ r with _value:  (downtime_array[0] + r.false_percentage_before_window) * 100.00 }))
     |> first()
     |> keep(columns: ["_value"])
     |> yield(name: "Total Downtime")

This solution assumes that the true/false events only occur alternately.

JCoordes
  • 147
  • 4
  • 10