Search code examples
grafanainfluxdbinfluxdb-2

Sum values based on Timestamp for InfluxDb (Flux)

I have data for executions running on different machines(Agents). The data is like:

_time AgentName RunningSessions
001 Agent1 1
002 Agent1 4
003 Agent1 10
004 Agent1 12
005 Agent1 15
001 Agent2 1
002 Agent2 5
003 Agent2 8
004 Agent2 10
005 Agent2 15

For the above data, my plot is like this(showing two colors for different Agents)

enter image description here

I need to combine the data based on _time so that the final data is like:

_time RunningSessions
001 2
002 9
003 18
004 22
005 30

What would be an optimal flux query to join the source data to produce the sum of RunningSessions based on time.

Note: There is a complexity here for data missing for a time instant from an Agent. Suppose for time=006, Agent1 is having 35 sessions, but Agent2 hasn't reported any change, so for time=006 Agent2 must still be running the last() 30 sessions.

This is my current query:

  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "RunningSessions")
  |> filter(fn: (r) => r["_field"] == "totalSessionsCount")
  |> group(columns: ["_time"])
  |> sum(column: "_value")
  |> group()

Update (1): Adding current Table view & query enter image description here

Solution

  • I was missing the time precision of milliseconds which was not being shown in the UI, but it was there due to which I couldn't group with the _time column.

    truncateTimeColumn(unit: 1s)

    enter image description here

    from(bucket: "${bucket}")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "RunningSessions")
  |> filter(fn: (r) => r["_field"] == "totalSessionsCount")
  |> truncateTimeColumn(unit: 1s)
  |> aggregateWindow(every: 5s, fn: last, createEmpty: true)
  |> group(columns: ["_time"])
  |> sum(column: "_value")
  |> group()
  |> yield(name: "Total VU Load")