Flux Query in Grafana Returning Partial Results compared to InfluxDB Data Explorer

[ziebarthj]

• What Grafana version and what operating system are you using?
  This is Grafana 9.4.3 running on a Linux box

• What are you trying to achieve?
  I am querying my InfluxDB database and trying to show unique assessments (think of it like a tag, with each different assessment/tag having a unique GUID) on a geomap panel. The assessments are being written into InfluxDB every minute, and I want this geomap panel to just show the most recent record of each assessment on the map. I want to be able to select different time ranges, though, so that I can see what assessments were active during that particular time…I just want to limit the data displayed to only the most recent record for each unique assessment ID (there is a field in the data with each assessment’s GUID) so that mousing over each assessment on the map shows that assessment’s information rather than 5 or 10 records (one per minute for the time range selected).

• How are you trying to achieve it?
  I am using the following flux query in Grafana:

from(bucket: "Assessment_Data")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "current_assessments")
  |> filter(fn: (r) => r["_field"] == "assessmentlat" or r["_field"] == "assessmentlon" or r["_field"] == "assessmentid" or r["_field"] == "assessmentcreated" or r["_field"] == "assessmentcomments" or r["_field"] == "assessmentcreatedby" or r["_field"] == "assessmentelement" or r["_field"] == "assessmentfeeder" or r["_field"] == "assessmentcategory")
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value" )
  |> drop(columns: ["_start", "_stop"])
  |> group(columns: ["assessmentid"])
  |> last(column: "_time")

• What happened?
  With this query, I get just a single record for the most recent line that has been written.
  

  

• What did you expect to happen?
  I expected to get the same result that I get with this same query in InfluxDB’s Data Explorer, where I have two tables of results (one table per GUID) and I get the most recent result from each assessment.
  

  

• Can you copy/paste the configuration(s) that you are having problems with?
  I showed the query that I’m running above. Taking out the group and last functions, I get five of each record as shown here:
  

• Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
  I didn’t get any errors with this.

Any help on this would be much appreciated.

[ziebarthj]

Well, never mind on that. I was able to fix the problem. Using the Query Inspector with my original query (I’m still fairly new to Grafana, and this is a relatively unexplored feature for me), I was able to look at the queried data and realize that I was getting data back in two data frames, but only one of them was being graphed on my geomap panel.

This rang a bell for one of the transforms that I had previously tried to make work for something unrelated and had gotten a message about it being designed to work with multiple frames, which I had never understood previously. Searching through the Transforms, I came upon the “Merge” transform, which combines multiple data frames into one output. As shown below, this fixes my problem beautifully.

I’ll leave this thread here in case it ever helps anybody else discover this one extra needed step.