Creating state timelines from JSON records

Grafana Dashboards
1
  • 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.

2

Please provide data where endTime is missing

3

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

1 Like
4

Do you want it to look like the following?

image
image1423×418 27.6 KB

5

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

image
image2338×1370 303 KB

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

1 Like
6

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

7

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.

8

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

9

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);

)

image
image1840×642 37.4 KB

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

10

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