Filtered Query based on different fields

Dear community,

I am struggled trying to create a, from my point of view, difficult query. I would appreciate if someone gives me an example that I could use to solve my exercise.
I have the following variables/metrics for a welding operation:

  • JOBNUMBER: Basically the workorder number, introduced before starting the welding process.
  • PROCESSACTIVE: Boolean variable, indicating when the welding starts.
  • VOLTAGE: The realtime voltage of the machine.
  • POWER: The realtime power of the machine.

I would like to give the user the possibility to filter the curves by selecting the JOBNUMBER taking into account that the operation needs to be windowed when PROCESSACTIVE is true. An example of welding process can be seen below:

I really appreciate at least one example of an optimized query. As I am trying to learn, I would appreciate to have one example in flux and another in influxql if possible.
I think the steps I should do are:

  • Create the dropdown filter looking for all the JOBNUMBERS in one timeframe.
  • Look for the timestamp when PROCESSACTIVE changes from false to true and from true to false to really determine the time filter of the operation.
  • Filter the voltage and power based on the window determined before.

Many many thanks in advance.

few questions:
-so jobnumber is not a tag on the other data ?
-process active is only write when it change of state ?
-final purpose: just display the time series of voltage and power ?

Data extract/example are appreciated.

1 Like


  • jobnumber is not a tag, it is a metric like the others.
  • process active only writes when changing from true-false-true.
  • final purpose: To have a dropdown on top of the screen allowing user to display exactly the curves associated to each jobnumber. Basically, you need to detect the timeframe associated to each jobnumber when processactive=true.

Don’t hesitate to ask more questions in case of doubts.

So to be sure, your data are like, sorted by timestamp:

1 Like


Almost perfect, I will modify it a bit following InfluxLine:

measurement,tag1=val1,tag2=val2 field1=“v1”,field2=1i 0000000000000000000

jobnumber,enterprise=“company1”,country=“SP” Value=[workordernumber] timestamp_1
processactive,enterprise=“company1”,country=“SP” Value=true timestamp_2
voltage,enterprise=“company1”,country=“SP” Value=xx timestamp_3
power,enterprise=“company1”,country=“SP” Value=xx timestamp_4
voltage,enterprise=“company1”,country=“SP” Value=xx timestamp_5
voltage,enterprise=“company1”,country=“SP” Value=xx timestamp_6
processactive,enterprise=“company1”,country=“SP” Value=false timestamp_n

Hello @alex1510,
How are you identifying when the timestamp is true or false? Based on your line protocol, it looks like they’re just plain timestamps without a tag to identify them as true or false.
Just to make sure you’re looking to bucket your time into windows where the start time of the bucket is a “true timestamp” and the end of the window is a false timestamp?

Unfortunately I can’t think of a good way to perform this type of custom windowing with Flux. This is the best I can think of.
Lets assume youre identifying the true and false timestamps by a tag:

//create a cumulative sum for the true timestamps:
from(bucket: "yourBucket")
  |> range(start: -1d)
  |> filter(fn: (r) => r._measurement == "yourMeasurement" && r._field == "yourField")
  |> map(fn: (r) => ({
      r with
      status: r.status,
      statusCount: if r.status == "true" then 1 else 0
  |> cumulativeSum(columns: ["statusCount"])

Given the example Flux code provided, let’s break down what the output would look like. The code aims to transform a dataset by incrementing a count every time a “true” value appears in the status column. The transformation involves first converting the “true”/“false” values to numerical values (1 for “true”, 0 for “false”) and then applying a cumulative sum on these values to get a running total of “true” statuses.

Assuming your original dataset in a measurement looks something like this:

_time status
2023-01-01T00:00:00Z true
2023-01-01T01:00:00Z false
2023-01-01T02:00:00Z true
2023-01-01T03:00:00Z true
2023-01-01T04:00:00Z false

After applying the provided Flux transformation, the output would add a statusCount column that represents the cumulative sum of “true” values up to that point:

_time status statusCount
2023-01-01T00:00:00Z true 1
2023-01-01T01:00:00Z false 1
2023-01-01T02:00:00Z true 2
2023-01-01T03:00:00Z true 3
2023-01-01T04:00:00Z false 3

So now you can just group() by your statusCount and then filter for your other required tag, JOBNUMBER. Or you might want to filter for JOBNUMBER first. I’m not exactly sure how that would affect your output as I’m not entirely clear what your input looks like and what your requiremetns are.


Thank you for the reply and explanation but I don’t really understand the transformations you perform.

What I want to do is just offer a user the possibility to filter the welding by its JOB NUMBER when PROCESSACTIVE is true. To do so, I need a query able to retrieve the current and power only for the period between PROCESSACTIVE true and again false…normally around 1min. The idea is just to generate the report of each welding operation.

From your query I don’t understand why you count status. You also mentioned tags when…as you see…I don’t have any relevant tag that helps to filter.

Any other idea?