Sum values based on Timestamp for InfluxDb (Flux)

Question

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)

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

score 0 · Answer 1 · answered Apr 10 '23 at 13:32

0

I'm not familiar with flux, but you can sum your time series at Grafana.

Go to panel edit mode > Transform:

Add "Add field from calculation" transformation,
select Calculation: Total,
enable option Replace all fields.

answered Apr 10 '23 at 13:32

markalex

8,623
2
7
32

Doesn't works. Problem is due to the complexity explained in the *Note* above. For a timeinstant say 006 there was no update from Agent2. But at 005, Agent2 was running 30 sessions. But when I transform it using Total, it says Agent1=35 & Agent2=null and therefore=> Total=35 – Anshuman Chatterjee Apr 11 '23 at 12:15

score 0 · Accepted Answer · answered Apr 21 '23 at 09:21

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)

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")

Sum values based on Timestamp for InfluxDb (Flux)

2 Answers2