State Timeline does not display duration correctly

Grafana Dashboards
1

Grafana Version: 12.0.1+security-01
OS: Containerised (managed by other team)
Queried data is in SQL Server 2022

I am trying to visualise the duration of SQL server agent jobs running in an SQL server instance to visualise duration and simplifying investigations into heavy jobs that overlap. (For the non-SQL person think of agent jobs like scheduled tasks or cron jobs).

I am trying to achieve this by using the “State timeline” visualisation, I’ve seen mentioned an old 3rd party visualisation namned “Gantt” that obviously has been discontinued by the guy behind it, and he also points to State Timeline to be what you are looking for instead. So with this post in my back I tried to get this working.

Grafana screenshot
Grafana screenshot1851×1130 270 KB

The above screenshot is showing what happens, I have a table and a State Timeline and as we can see there is something fishy going on, the green and the red squares are jobs that are running very long, as we can se from the table, but shown in the state timeline they seem to run in seconds, and as for the yellow squares we can see that it is 4 separate runs, but in the state timeline it seems like the 2nd and 3rd run is one consecutive run…

The data is compiled with this query:


DECLARE @cols NVARCHAR(MAX);
DECLARE @sql  NVARCHAR(MAX);
DECLARE @instans NVARCHAR(200) = '${Instans}';

-- Step 1: Create a temp table for filtered data
DROP TABLE IF EXISTS #Filtered;

SELECT 
    invokationtime AT TIME ZONE 'Central European Standard Time' AT TIME ZONE 'UTC' AS invokationtime,

    finishtime AT TIME ZONE 'Central European Standard Time' AT TIME ZONE 'UTC' AS finishtime,
    jobname
INTO #Filtered
FROM AgentLogs.dbo.AgentJobHistoryLogs
WHERE computername = @instans
AND invokationtime AT TIME ZONE 'Central European Standard Time' AT TIME ZONE 'UTC' BETWEEN $__timeFrom() AND $__timeTo();

-- Step 2: Build pivot column list
SELECT @cols = STRING_AGG(QUOTENAME(jobname), ',')
WITHIN GROUP (ORDER BY jobname)
FROM (SELECT DISTINCT jobname FROM #Filtered) AS j;

-- Step 3: Build dynamic pivot SQL
SET @sql = '
SELECT 
    invokationtime,
    finishtime,
' + @cols + '
FROM
(
    SELECT 
        invokationtime,
        finishtime,
        jobname,
''running'' AS JobRan
    FROM #Filtered
) AS src
PIVOT
(
    MAX(JobRan)
    FOR jobname IN (' + @cols + ')
) AS pvt;
';

-- Step 4: Execute final pivot
EXEC sys.sp_executesql @sql;

Yes, I had some help from ChatGPT to get it going, the Pivot is pretty much to get a format that looks like the one in the sample post mentioned above. the weird dynamic part is basically as I don’t know what jobs are available for that instance and I need to know the job names to Pivot. And finally to get time ranges to work in a “good looking” way in Grafana I have to do some tricks with the time.

A sample of the data looks like:
invokationtime;finishtime;DBA - CleanupJobHistory;DBA - CycleErrorlog;DBA - DatabaseBackup - ALL_DATABASES - FULL - NOTSTD;DBA - DatabaseBackup - ALL_DATABASES - LOG;DBA - DatabaseBackup - CCP_DB - DIFF;DBA - DatabaseIntegrityCheck - ALL_DATABASES;DBA - ExportConfig;DBA - IndexOptimize - USER_DATABASES - NOTSTD;DBA - Local AgentJobLogHarvest;DBA - Stop IndexOptimize - USER_DATABASES - NOTSTD;DBA - syspolicy_purge_history;DBA - UpdateStatistics - USER_DATABASES - NOTSTD
2025-11-22 23:04:08.0000000 +00:00;2025-11-23 05:00:00.0000000 +00:00;NULL;NULL;NULL;NULL;NULL;NULL;NULL;running;NULL;NULL;NULL;NULL
2025-11-22 23:05:00.0000000 +00:00;2025-11-22 23:05:01.0000000 +00:00;NULL;NULL;NULL;running;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL
2025-11-22 23:07:00.0000000 +00:00;2025-11-22 23:07:00.0000000 +00:00;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;running;NULL;NULL;NULL
2025-11-22 23:20:00.0000000 +00:00;2025-11-22 23:20:00.0000000 +00:00;NULL;NULL;NULL;running;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL
2025-11-22 23:35:00.0000000 +00:00;2025-11-22 23:35:01.0000000 +00:00;NULL;NULL;NULL;running;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL
2025-11-22 23:44:08.0000000 +00:00;2025-11-23 04:08:31.0000000 +00:00;NULL;NULL;NULL;NULL;NULL;running;NULL;NULL;NULL;NULL;NULL;NULL
2025-11-22 23:50:00.0000000 +00:00;2025-11-22 23:50:07.0000000 +00:00;NULL;NULL;NULL;running;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL
2025-11-23 00:05:00.0000000 +00:00;2025-11-23 00:05:00.0000000 +00:00;NULL;NULL;NULL;running;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL
2025-11-23 00:20:00.0000000 +00:00;2025-11-23 00:20:01.0000000 +00:00;NULL;NULL;NULL;running;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL
2025-11-23 00:35:00.0000000 +00:00;2025-11-23 00:35:01.0000000 +00:00;NULL;NULL;NULL;running;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL
2025-11-23 00:50:00.0000000 +00:00;2025-11-23 00:50:00.0000000 +00:00;NULL;NULL;NULL;running;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL

What I expect to happen is that the examples above, green, yellow, red, that the data is displayed as the data shows, the Integritycheck line should be 4 hours long. Could this be an issue that my time stamps (starts and stop) is overlapping, the samples I’ve seen the timestamps is usually broken into a consecutive stream of times, with no overlaps, but from the interwebz I understand that this should not be a problem. I have also read that there is mentions about that the columns in the data should have specific names as “time”, “time_end”, “series” and “value”, but that seems to have no effect. Our friend ChatGPT also is very confused and tries either to present the query above, or a Union query that works even worse, in which the starts and the stops are one different rows in the result set and looks nothing like the sample in the above mentioned post. Any one have any ideas?

I am open to use another visualisation, storing the data differently (if the motivation is good) or anything else, I just want to get this working…