Creating state timelines from JSON records

  • What Grafana version and what operating system are you using?
    v9.3.2, Linux

I am using the “Infinity” data source plugin to fetch records from my mongoDB database in the form of JSON objects, returned through REST API URLs

I want to visualize these in the form of self-updating timelines, using the “State Timelines” visual.

Here is a list of sample records:

[
	{
		"incidentName": "ES Economizer Inlet FW Temperature low",
		"priority": "warning",
		"startTime": "2022-11-04T16:53:00.000Z",
		"endTime": "2022-11-05T14:06:00.000Z",
		"systemName": [
			"Pcfb"
		],
		"id": "6364ffb851a8a70007086acf"
	},
	{
		"incidentName": "ES PFBC Furnace Pressure high",
		"priority": "danger",
		"startTime": "2022-11-04T17:35:00.000Z",
		"endTime": "2022-11-05T14:06:00.000Z",
		"systemName": [
			"Pcfb"
		],
		"id": "636505253f5cc20006bcfc76"
	},
	{
		"incidentName": "ES Furnace Pressure abnormal",
		"priority": "warning",
		"startTime": "2022-11-04T17:34:00.000Z",
		"endTime": "2022-11-08T18:32:00.000Z",
		"systemName": [
			"Pcfb"
		],
		"id": "6365061e3f5cc20006bcfc90"
	},
	{
		"incidentName": "Unit Performance not healthy",
		"priority": "warning",
		"startTime": "2023-06-11T21:58:00.000Z",
		"systemName": [
			"Unit Performance"
		],
		"id": "6485f8c770bd1400070a2013"
	},
	{
		"incidentName": "Generator System not healthy",
		"priority": "warning",
		"startTime": "2023-06-11T22:04:00.000Z",
		"systemName": [
			"Generator System"
		],
		"id": "64860d3070bd1400070a20a6"
	}
]

As you can see, the records contain two fields that are strings containing the “startTime” and “endTime”. Each records needs to be its own timeline. But the “state timelines” expects data in a long format rather than wide format. I tried using the Group to Matrix transformation, and got somewhat close to what I need - but, there are records where the “endTime” itself is missing and that causes the timelines to become incorrect.

Please provide data where endTime is missing

I have updated and added records where “endTime” is missing

1 Like

Do you want it to look like the following?

I want it to be like below (I created this using the Plotly library in python):

PS: for the records having no “endTime” - I want the horizontal timeline to go from the “startTime” to current time

1 Like

what if the start time is 2011-01-01 and end date is null? Do you really want it to be today’s date?

No, that would stretch the window too far. Anyways, I can confirm that I don’t have such long durations for any records. The max duration would roughly be 1-2 weeks.

You might want to go the UQL jsonata query with the infinity plugin check this out to get you on the jsonata journey for your data

https://try.jsonata.org/FTH2-NpIv

I tried implementing it as below, but got an error : “error applying uql query”

I pasted this code as given in above link, into the “UQL” block:

(
   
    $A := $map($, function($v, $i, $a) {
       { 
           'incidentName': $v.incidentName,
            'priority': $v.priority,
            'time': $substring($v.startTime,0,10)
       }
    });
   $B := 
   $map($, function($v, $i, $a) {
       { 
           'incidentName': $v.incidentName,
            'priority': $v.priority,            
            'time':   $exists($v.endTime) ? $substring($v.endTime,0,10) : $substring($v.startTime,0,10)
       }
    });
    $final := $append($A, $B);
    $final^(incidentName, time);

)

PS: I must admit, I am not familiar with UQL

good, this is your chance to lean :laughing:

Try it in try in https://try.jsonata.org/ first with the link I posted and try to solve it.

For starters you have in the url which is not going to work.

Also read the doc for infinity url here

https://sriramajeyam.com/grafana-infinity-datasource/wiki/uql/

1 Like