Display nulls as previous value

[maemigh]

What would you like to be added:
I would like the option to display nulls as the previous value in the time series

Why is this needed:
I'm using MySQL as a datasource where a row only exists in the DB if the status has changed. This results in a lot of null buckets if status doesn't change. If null could be set "as previous value", then it would automatically fill in these null buckets.

[diafour]

Hello! The exact feature was reported earlier: #151 #154. Right now statusmap is best used with "continuous" status values when datasource's result has a value for each timestamp. "Continuous" status values are easily retrieved from timedb based datasources like Prometheus or InfluxDB, and there are problems with "store only the new status" solutions.

Can you answer some questions to help design this feature?

1. What should be displayed if there is no "previous value"? For example, the first timestamp with value is somewhere in the middle of the time range and there are null buckets at the beginning.
2. Should the "previous value" mode respect the priority of values in the discrete mapping? For example, if there are multiple values in a bucket, should we just copy them to the next null bucket or just use the value with the top priority?
3. Did you try to find a SQL-based solution to fill the gaps?

[maemigh]

Hello,

Thanks for the quick response.

1. If there is no previous value, then just null. I think I could otherwise devise a query to always start with a value (possibly a separate distinct value, or using the value from the row with MAX(timestamp) < $__timeFrom, or coloring null differently if that's possible).
2. In this case, even when there are multiple values in a bucket, I think I would expect the value that comes from the last row that made it into the bucket as I believe it still follows the original ORDER by timestamp.
3. I think that I may be able to do something with recursive CTE with using $__timeFrom as the anchor and $__interval_ms / 1000 as the step, but I have not attempted to create the query yet. Older DB versions wouldn't support this though.

[maemigh]

I believe I found a way to make it work with MariaDB by using the sequence storage engine (doesn't look like mysql supports this). It's a little more complicated than if the panel supported it directly though. Example query for icinga1:

SELECT seq / 1000 as time_sec,
       (SELECT state
        FROM icinga_statehistory
        WHERE UNIX_TIMESTAMP(icinga_statehistory.state_time) < seq/1000
          AND icinga_statehistory.object_id in (SELECT object_id
                                                FROM icinga_objects
                                                WHERE name2 = 'SERVICENAME'
                                                  and name1 = 'HOSTNAME')
          AND state_type = 1
        ORDER by state_time DESC
        LIMIT 1)                    SERVICENAME
FROM seq_${__from}_to_${__to}_step_${__interval_ms}
UNION ALL
SELECT UNIX_TIMESTAMP(state_time) as time_sec, state FROM icinga_statehistory
WHERE icinga_statehistory.object_id in (SELECT object_id
                                    FROM icinga_objects
                                    WHERE name2 = 'SERVICENAME'
                                      and name1 = 'HOSTNAME')
                  AND state_type = 1
AND $__timeFilter(state_time)
ORDER BY time_sec