Reduce a Row Down to the Max Value of all Columns in that row

[ziebarthj]

• What Grafana version and what operating system are you using?
  9.4.3 running in some flavor of Linux

• What are you trying to achieve?
  I am trying to create a dashboard with a State Timeline that condenses a large number of “alarm levels” down into a single maximum alarm level for each of my stations. This is going to be used in an overall “system status” dashboard to tell me, at a glance, if each station is completely normal, if it has minor alarms, major alarms, critical alarms, or if its communications are off-line.

• How are you trying to achieve it?
  Inside of InfluxDB, I have a task that looks at a large number of alarm points and processes them down into a smaller list of “what’s actually wrong” at each substation. Basically, I am applying 20+ years of knowledge from looking at our SCADA system to look at certain combinations of alarms and values and come up with more functional alarm points for our operations guys to look at. These processed alarm levels (consisting of 0=normal, 1=minor alarm, 2=major alarm, 3=critical alarm, and 5=comms off-line) are being written into a separate bucket from my original data, with each station having its own _measurement value and then each alarm point having its own _field of CS (for current state) and _value of 0-5. I have gotten so far as to come up with the following Flux query to get all of my data into one manageable block:

from(bucket: "SCADA_Processed")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] == "CS")
  |> aggregateWindow(every: 5m, fn: last, createEmpty: false)
  |> pivot(rowKey: ["_time", "_measurement"], columnKey: ["point_id"], valueColumn: "_value")
  |> yield(name: "last")

This gives me a nice table with one row every five minutes and all of my alarm values in columns. What I need now is to condense all of the fields in each row into the maximum value of all the columns in that row so that I can display that value in my state timeline for each station. I can’t figure out a Flux query or a Grafana transform to do this, but I’m figuring that there has to be a way. I’m basically trying to create a summary metric showing the worst case of everything at each station and then graph it in a state timeline.

• What happened?
  If I run this query in InfluxDB Data Explorer, I get a table like this:
  
  

In my Grafana dashboard editor, when editing my state timeline panel, I get various unreadable jumbles of all my points on separate lines, a graph reduced to a single value, or nothing at all, depending on what transformer I try. I’m just not finding the right combination of query and transforms to give me what I’m after.

• What did you expect to happen?
  I want to get to where I have a state timeline panel with each station (_measurement) on line and the worst-case alarm value from all of the alarm points in that station graphed over time.

[sowdenraymond]

You can try the reduce transformation

[ziebarthj]

I can’t figure out how to make that transform do anything useful at all. Do you have some guidance on how to actually set it up so that it does something, given the data that I’m starting with? I also thought that the description and documentation for that transform looked promising, but when it comes to actually trying to set it up, I can’t figure out how to make anything useful pop out of it.

Reduce transform with the query as noted above:

Reduce transform with the pivot line taken out of that query, just to be sure having everything in one table wasn’t confusing it:

[sowdenraymond]

[ziebarthj]

Thanks, @sowdenraymond That nudged me along far enough to figure out how to make it work. I had tried that transform, but it didn’t seem to do anything useful for me. But, in an effort to make things easier to see, I “temporarily” backed off my query to only look at the data points associated with one of my _measurements. When I did that, the transform that you suggested here started to work. However, with a little more trial and error, I found that adding the second _measurement into my query broke this transform again. I finally figured out that the fact that the two _measurements that I’m currently using have different sets of fields associated with them was what was breaking the Add Field From Calculation transform.

So, I broke my single query down into two queries, each looking at only one _measurement:

This allowed me put in two separate instances of the Add Field From Calculation transform and filter each to only use the set of fields coming from the query for the correct _measurement.

Once I did that, I was able to add a Filter Data by Query transform to filter out all of the original data point fields, leaving me with only the two summarized data fields that I made in the first two transforms. Converting those fields from numbers to text allowed me to set up the Value Mappings that I needed to make my screen contain readable results, and you can see the result at the top of those pictures…I have exactly what I needed.

Thanks for your help!