Change background color of plot if the last data point is older than 10 minutes

Grafana Time Series Panel
1

  • What Grafana version and what operating system are you using?
    Grafana v11

  • What are you trying to achieve?
    I would like to change the background color of a time-series plot to red if the latest data point has a timestamp that is 10 minutes or more from the current time.

  • How are you trying to achieve it?
    I added the following line to my select statement to calculate the time since last data point was received
    :
    TIMESTAMPDIFF(SECOND, MESSAGE_TS, NOW()) AS time_since_last_data,

    Then I added the following field overrides:

    1. Set a threshold to change the background color to red if ‘time_since_last_data’ is greater than 600 seconds
    2. Changed the line width of ‘time_since_last_data’ to 0 (to hide the plot of ‘time_since_last_data’)
    3. Changed Show Points to ‘Never’ (to hide the plot of ‘time_since_last_data’)
    4. Changed the axis placement of ‘time_since_last_data’ to ‘Hidden’ to scale the plot to the data I actually want to view instead of scaling to ‘time_since_last_data’)

    I also tried creating a separate query for ‘time_since_last_data’ but there was no change to background color when the 600 second condition was met.

  • What happened?
    time_since_last_data is plotting all points relative to NOW(), which means there is always a datapoint greater than the 600 second threshold, causing the background to remain red at all times.

  • What did you expect to happen?
    I expected the background to only change to red if the latest value of ‘time_since_last_data’ is greater than 600 seconds.

2

I was able to solve this by calculating the time since the last data point and applying this same calculated value to each row in the table being plotted. This effectively created a horizontal line across my plot with a static value set to the time since the last data point was received, which I was able to hide using overrides. Here is the relevant line of the select statement of my query:

UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP((SELECT MAX(time_field) FROM table_name WHERE conditions)) AS time_since_last_data

I then used a few extra overrides than what is listed in my original post to hide ‘time_since_last_data’ from the plot and the tooltip. Here is a full list of overrides used:

  1. Thresholds > Thresholds
    • Set color to red
    • Set threshold to 600 (time_since_last_data is in seconds, so 600 seconds = 10 minutes)
  2. Thresholds > Show Thresholds
    • As filled regions (i.e. plot background color)
  3. Axis > Placement
    • Hidden (prevents time_since_last_data from altering y-axis scale)
  4. Axis > Soft min
    • 600 (when time_since_last_data > 600, time_since_last_data’s y-axis begins at 600, ensuring the entire panel is colored red)
  5. Axis > Soft max
    • 600 (when time_since_last_data < 600, prevents a red background from showing at the upper portion of the plot)
  6. Graph Styles > Line Width
    • 0 (hides time_since_last_data line graph from plot)
  7. Graph Styles > Show Points
    • Never (hides time_since_last_data points from plot)
  8. Graph Styles > Point size
    • 0 (hides time_since_last_data points when hovering over the plot. NOTE: the sliding scale has a minimum of 1. To get around this, type 0 into the text box next to the sliding scale)
  9. Series > Hide in area
    • Tooltip (hides time_since_last_data from the tooltip. NOTE: the ‘tooltip mode’ should be set to ‘All’ for this to work effectively)
1 Like