Hi,
I’m trying to user conditional logic for between 9 to 17 except launch break which is beetween 12:30 to 13:30. I used hour selection and other functions but I couldn’t find right function or way to write exception for launch break. I need your about this issue.
SQL query like below
SELECT case
/*working hours and above 1.5gbps - OK*/
when (TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("09:00:00","%H:%i:%s") AND TIME_FORMAT("12:30:00","%H:%i:%s")
OR TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("13:30:00","%H:%i:%s") AND TIME_FORMAT("18:00:00","%H:%i:%s")) AND (rxrate > 1500) AND WEEKDAY(NOW()) < 5 then "0"
/*working hours - below 1.3gbps WARNING*/
when (TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("09:00:00","%H:%i:%s") AND TIME_FORMAT("12:30:00","%H:%i:%s")
OR TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("13:30:00","%H:%i:%s") AND TIME_FORMAT("18:00:00","%H:%i:%s")) AND (rxrate < 1300) AND WEEKDAY(NOW()) < 5 then "1"
/*Launch Break - below 1gbps OK*/
when (TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("12:30:00","%H:%i:%s") AND TIME_FORMAT("13:30:00","%H:%i:%s")) AND (rxrate < 1200) AND WEEKDAY(NOW()) < 5 then "4"
/*working hours - below 1gbps CRITICAL*/
when (TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("09:00:00","%H:%i:%s") AND TIME_FORMAT("12:30:00","%H:%i:%s")
OR TIME_FORMAT(NOW(),"%H:%i:%s") BETWEEN TIME_FORMAT("13:30:00","%H:%i:%s") AND TIME_FORMAT("18:00:00","%H:%i:%s")) AND (rxrate < 1000) AND WEEKDAY(NOW()) < 5 then "5"
ELSE '7'
END AS "NS_RX_MESAI_KONTROL",
rxrate as "RX"
FROM throughput`Preformatted text`
WHERE $__timeFilter(logtimestamp)
my flux query like below.
import "date"
import "system"
from(bucket: "myBucket")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "mydevicesmeasurements")
|> filter(fn: (r) => r["devices"] == "device01" or r["devices"] == "device02")
|> filter(fn: (r) => r["_field"] == "rxmbitsrate")
//|> filter (fn: (r) => string(v: date.weekDay(t: r._time)) !~ /(0|6)/)
|> aggregateWindow(every: 30s, fn: mean, createEmpty: false)
|> pivot(columnKey: ["devices"], rowKey: ["_time"] , valueColumn: "_value" )
|> map(fn: (r) => ({r with _value: r["device01"] + r["device02"] }))
|> drop(columns: ["device01","device02","env"])
// I had to add +3 because of our timezone
|> map (
fn: (r) => ({r with
level: if r._value > 1000 and (((date.hour(t:r._time) + 3) < 18) and ((date.hour(t:r._time) + 3) > 9)) then
"workins hours OK"
else if r._value < 1000 and (((date.hour(t:r._time) + 3) > 17) or ((date.hour(t:r._time) + 3) < 9)) then
"out of office"
else
"OTHER",
}),
)