Graph data about processes and their different stages over time

I’m very new to Grafana and have been searching for quite some time, so I’m making this post hoping that someone can help me.
I have data about different code build processes that run in parallel. Each process starts in a queue, then runs, then is ended. These stages are identified by 3 datetime timestamps, which indicate when each starts. Here’s a sample to show what the (relevant) data looks like. All timestamps are for the same day, but dates are also present :

BuildId | QueueTime | AssignTime | FinishTime
0001 | 08:00 | 08:10 | 08:20
0002 | 08:05 | 08:15 | 08:30
0003 | 08:10 | 08:11 | 08:25

note: BuildId is unique, AssignTime is when the build starts to run

In this example, we can see that some jobs can be in queue at the same time, and that they can be running at the same time. What I would like is a time series that displays the number of jobs that are in queue over time, in minute intervals. The issue is that I just have no idea how I’m supposed to transform my data.
I’m also limited by my data source (Azure storage table), which allows me to only query the data over a REST API without aggregation, unlike SQL where you can make pretty advanced queries.

Currently using Grafana v10.3.3

edit: raw json data can be found on pastebin

welcome to forum @seane

What is your datasource? ms sql, mysql, postgres, influxdb?

Hello, my datasource is an Azure storage table, so as I wrote I can’t really change the query except to filter the columns.

1 Like

So when calling it via rest api it returns data as a table with columns and rows or as json?
If json please post real sample structure but with fake data and columns

The data returned is in json. I added a pastebin link at the bottom of my post.

1 Like

I am not seeing any build running in the same time in the example you provided. and which date time column are you referring to?

did you mean in the same time range 8:00 to 9:00? can you be more specific

Apologies for the confusion, I’ve edited the raw data to correspond to my original sample. So if you look at build 1, its queue starts at 08:00 and ends at 08:10, and for build 2, it starts at 08:05 and ends at 08:15. It shows that both jobs are in queue together between 08:05 and 08:10. So in Grafana I would like to use a chart (time series?) that in this case will show that 2 jobs are in queue at 08:05, 08:06, etc. until 08:10.

so they are not running at the same time but running in the same time range. so the requirement is builds running in the same time range, in the same hour.

or are you talking about overlaps. which is a whole nother bag of chips

build 1 starts 8:45 and ends 9:15
build 2 starts 9:05 and ends 9:15

In the sample that I provided they are running at the same time, in parallel. The requirement is builds running in the same time range, in the same hour, on the same day.

1 Like

i would recommend using infinity plugin and give it your api endpoint to get your data.
parse the incoming data using UQL language which implement jsonata show above

| jsonata "$map($.value, function($v, $i, $a) {{'QueueTime': $toMillis($v.QueueTime) ~> $fromMillis('[Y]-[M01]-[D01] [H01]:[m01]')}})"
| summarize "buildcount"=count("QueueTime") by "QueueTime"
1 Like

This is going towards the right direction, thank you. I now have a time series of the amount of builds that started their queue. Now I need to figure out how to show the amount of builds that are in queue. So where the time is between QueueTime and AssignTime.

time to ramp on your jsonata skills.

try that scenario out by copying your data out to following url that has your sample data and jsonata

:fish: :fishing_pole_and_fish:

1 Like

Thanks, I’ll look into it and post what I figured out.

1 Like

as you can see there I trimmed off the time stamp at minutes using time conversion function with picture param of ~> $fromMillis('[Y]-[M01]-[D01] [H01]:[m01]')

    "QueueTime": "2024-02-01 08:00"
    "QueueTime": "2024-02-01 08:05"
    "QueueTime": "2024-02-01 08:05"

so if you are looking at the above and requirement was count by minute then you have 1 for 8:00 and 2 for 08:05. this is just my own requirement just to show how to sort it out.

you want it by hour then

 ~> $fromMillis('[Y]-[M01]-[D01] [H01]')}})

this gives you 3 count of builds in hour 8

1 Like