The source of the query is a MySQL database, and the table is like :

The source of the query is a MySQL database, and the table is like :

The ultimate goal is to have a time series with one line per user, showing the evolution of the occupied size for a specific share (filtered by share like '%blah%'), along with the delta between the latest result and the previous one.

I have the graph displaying all of that, but I don't know how to include the delta somewhere. I don't want to plot the delta in order to keep the graph clean.

Ideally, I would like to change the display name to include the delta concatenated to the user name, but I haven’t found how to do that.

I tried to concat inside the SQL query, but it creates 2 users for each user (one without the delta, and one with it).

For now, the SQL query is like :

SELECT
  date(time) AS time,
  user,
  SUM(size) AS total_size,
  SUM(size) - LAG(SUM(size)) OVER (PARTITION BY user ORDER BY date(time) ASC) AS delta
FROM user_storage.storage
WHERE size > 150 AND share LIKE '/home%'
GROUP BY user, date(time)
ORDER BY time ASC

which returns data like :

Any suggestions are welcome.

• sql
• time-series
• grafana

• To get attention to your problem, you could 1. post in the question the SQL you tried. 2. add a table containing 4 or 5 rows that this SQL output when played on the database. 3. show a screenshot of the graph with 2 users instead of 1. – Guillaume Outters Commented Mar 24 at 22:01
• I don't get the end goal here. Do you want to have a delta for each measurement comparing with previous one? Or do you want a single value, representing change from first seen value to last? – markalex Commented Mar 25 at 7:20
• If I take the example, "delta user2" on the last line is equal to "last total_size user2" - "previous total_size user2". With values : 100 = 2189 - 2089. The goal is to determine the space occupied or freed between last week and the most recent measurement (without checking by hand for each user the last value and the previous one). – DSX Commented Mar 25 at 7:42
• Thanks for the editing! I understand that you have 1 graph line per user (showing its total_size), and you would like to have each plot additionally tagged with its delta, as a tooltip instead of as a separate graph line? – Guillaume Outters Commented Mar 25 at 7:51
• I was (unconventionally) thinking of writing it as total_size.delta (see this fiddle), as the delta compared to total_size would then not be significant enough to disrupt the graph (less than 1 % of error induced, in the worst case of going from 51 to 150 which would be passed as 150.99 instead of 150), but would Grafana interpret that number-looking string as a float? If not, the cast to float done in SQL would remove trailing 0s (2189.100 rendered as 2189.1). – Guillaume Outters Commented Mar 25 at 8:12

1 Answer 1

Time Series seems to be stuck in the need for such a feature for at least 2022,
as proven by multiple discussions even on SO.

Some of those lead to feature request 73989 where 2 workarounds are cited:

• the vxiaobai-timeseries-panel plugin
• converting your timeline to numbers to be able to use an XY Chart instead of Time Series

Failed attempts

Exploration opened some pathways:

• an alternate solution in the conversation leading to the feature request uses JSON, but in the serie name instead of in its value;
  thus it will generate one serie per value of delta, unless you restrain yourself to grouping on the last delta value for the user. Not that useful.
• I tried outputting values as a JSON, then using the Extract Transformation to get only the total_size pushed to the graph; however what gets in the tooltip is what the graph received, not the original value
• It seems that Value mappings are able to split Value from Text (the Value gets to the graph, the Text to the tooltip), thus we could theorically map {"total_size":2189,"delta":100} to a value of 2189 with a tooltip value of "2189 (+100)";
  however:
  • a Value mapping by Value would force to define 1 mapping per (JSON) value, thus approximately 1 mapping per date per user,
    so performance would be disastrous (and we don't want to define thousands of mappings, do we?)
  • a Value mapping by Regex only works on text fields (see multiple discussions), but gets applied after Transformations (particularly the one that could extract our total_size from the JSON… after we've removed the delta we want to display)
  • Dynamic Value Mapping, by adding a "Config from query results" Transformation before the Partition by values and Extract fields Transformations, seems to 1. not allow regex mapping, and 2. be ignored (be it by matching the full JSON with select '{"size":1589}' Value, 'hey!' Text;, or just '1589' or 1589)