Use several tables from the same query in State Timeline panel

[ohanasbh]

Hi Grafana Community!

I am running Grafana in docker (latest) on Ubuntu 16.04 on a ‘shuttle’ mini-computer.

I want to display a state timeline for my external monitors.
I use a REST API from UptimeRobot through the infinity datasource.
I managed to get the result I wanted :

My problem is that I have to do one request per line, even if all the data I need is in one request.

In the JSON I get from the API, there is my 5 monitors, with different data for each.
In this data, there is a table called logs containing the timeline data.

The only way i managed to make the timeline work is by setting the monitor[i].logs as rows/root.

But by doing so, I can’t access the data from my other monitors and I have to repeat my query.

Is there any way to do what I want without repeating the query?
I don’t think I can put several values in the rows/root field, or display the timeline without setting my logs table as rows/root.

I want to expand this graph to more monitors but it doesn’t seem reasonable if I have to do one query per monitor.

Thanks a lot,
Best regards,
Thibault.

PS: I have been visiting this forum for months and it’s been extremely helpfull.
Thanks a billion to all the grafana community.

[yosiasz]

Welcome

Could you please post the json data coming back from getMonitors?

[ohanasbh]

Hi yosiasz,
thanks for the extremely quick response.

here is the json :

  "meta": {
    "custom": {
      "data": {
        "monitors": [
          {
            "create_datetime": 1661423792,
            "custom_down_durations": "210",
            "custom_uptime_ratio": "99.984",
            "friendly_name": "OHANA - COGILOG",
            "http_password": "",
            "http_username": "",
            "id": 792542720,
            "interval": 60,
            "keyword_case_type": null,
            "keyword_type": null,
            "keyword_value": "",
            "lastLogTypeBeforeStartDate": {},
            "logs": [
              {
                "datetime": 1662069251,
                "duration": 684922,
                "id": 2730362006,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1662069104,
                "duration": 147,
                "id": 2730360265,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1662015977,
                "duration": 53127,
                "id": 2729730788,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1662015914,
                "duration": 63,
                "id": 2729729838,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1661438230,
                "duration": 577684,
                "id": 2722930960,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1661438192,
                "duration": 38,
                "id": 2722930746,
                "reason": {
                  "code": "98",
                  "detail": "Monitor started"
                },
                "type": 98
              }
            ],
            "port": 64998,
            "status": 2,
            "sub_type": 99,
            "timeout": 30,
            "type": 4,
            "url": "ohana.sbh.cloud"
          },
          {
            "create_datetime": 1661423956,
            "custom_down_durations": "23375",
            "custom_uptime_ratio": "98.218",
            "friendly_name": "OHANA - GRAFANA",
            "http_password": "",
            "http_username": "",
            "id": 792542747,
            "interval": 60,
            "keyword_case_type": null,
            "keyword_type": null,
            "keyword_value": "",
            "lastLogTypeBeforeStartDate": {},
            "logs": [
              {
                "datetime": 1662069239,
                "duration": 684934,
                "id": 2730361869,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1662069104,
                "duration": 135,
                "id": 2730360266,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1661792936,
                "duration": 276168,
                "id": 2726887256,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1661778159,
                "duration": 14777,
                "id": 2726695978,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1661462474,
                "duration": 315685,
                "id": 2723197990,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1661462410,
                "duration": 64,
                "id": 2723197356,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1661462295,
                "duration": 115,
                "id": 2723195995,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1661462257,
                "duration": 38,
                "id": 2723195817,
                "reason": {
                  "code": "98",
                  "detail": "Monitor started"
                },
                "type": 98
              },
              {
                "datetime": 1661458156,
                "duration": 4101,
                "id": 2723151651,
                "reason": {
                  "code": "99",
                  "detail": "Monitor paused"
                },
                "type": 99
              },
              {
                "datetime": 1661449757,
                "duration": 8399,
                "id": 2723060505,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1661438393,
                "duration": 11364,
                "id": 2722932897,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1661438356,
                "duration": 37,
                "id": 2722932730,
                "reason": {
                  "code": "98",
                  "detail": "Monitor started"
                },
                "type": 98
              }
            ],
            "port": 3000,
            "status": 2,
            "sub_type": 99,
            "timeout": 30,
            "type": 4,
            "url": "ohana.sbh.cloud"
          },
          {
            "create_datetime": 1661424092,
            "custom_down_durations": "436826",
            "custom_uptime_ratio": "66.789",
            "friendly_name": "OHANA - RTI",
            "http_password": "",
            "http_username": "",
            "id": 792542771,
            "interval": 60,
            "keyword_case_type": null,
            "keyword_type": null,
            "keyword_value": "",
            "lastLogTypeBeforeStartDate": {},
            "logs": [
              {
                "datetime": 1662584595,
                "duration": 169578,
                "id": 2736522296,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1662149589,
                "duration": 435006,
                "id": 2731349510,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1662147438,
                "duration": 2151,
                "id": 2731323096,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1662145983,
                "duration": 1455,
                "id": 2731306763,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1662069331,
                "duration": 76652,
                "id": 2730363089,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1662069099,
                "duration": 232,
                "id": 2730360217,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1662064858,
                "duration": 4241,
                "id": 2730311941,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1662064793,
                "duration": 65,
                "id": 2730311216,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1661792650,
                "duration": 272143,
                "id": 2726883769,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1661792582,
                "duration": 68,
                "id": 2726882930,
                "reason": {
                  "code": "0",
                  "detail": "Timeout"
                },
                "type": 1
              },
              {
                "datetime": 1661438852,
                "duration": 353730,
                "id": 2722938204,
                "reason": {
                  "code": "1",
                  "detail": "OK"
                },
                "type": 2
              },
              {
                "datetime": 1661438815,
                "duration": 37,
                "id": 2722938040,
                "reason": {
                  "code": "98",
                  "detail": "Monitor started"
                },
                "type": 98
              }
            ],
            "port": 4110,
            "status": 2,
            "sub_type": 99,
            "timeout": 30,
            "type": 4,
            "url": "ohana.sbh.cloud"
          },
          {
            "create_datetime": 1655462327,
            "custom_down_durations": "2592000",
            "custom_uptime_ratio": "0.000",
            "friendly_name": "OHANA - WAN1 WISP FTTH",
            "http_password": "",
            "http_username": "",
            "id": 792031236,
            "interval": 300,
            "keyword_case_type": null,
            "keyword_type": null,
            "keyword_value": "",
            "lastLogTypeBeforeStartDate": {},
            "logs": [
              {
                "datetime": 1655479989,
                "duration": 7274184,
                "id": 2650738986,
                "reason": {
                  "code": "444444",
                  "detail": "No Response"
                },
                "type": 1
              },
              {
                "datetime": 1655479551,
                "duration": 438,
                "id": 2650734052,
                "reason": {
                  "code": "555555",
                  "detail": "Successful Response"
                },
                "type": 2
              },
              {
                "datetime": 1655477789,
                "duration": 1762,
                "id": 2650715372,
                "reason": {
                  "code": "444444",
                  "detail": "No Response"
                },
                "type": 1
              },
              {
                "datetime": 1655476765,
                "duration": 1024,
                "id": 2650703121,
                "reason": {
                  "code": "555555",
                  "detail": "Successful Response"
                },
                "type": 2
              },
              {
                "datetime": 1655476728,
                "duration": 37,
                "id": 2650702864,
                "reason": {
                  "code": "98",
                  "detail": "Monitor started"
                },
                "type": 98
              }
            ],
            "port": "",
            "status": 9,
            "sub_type": "",
            "timeout": null,
            "type": 3,
            "url": "ohana1.sbh.cloud"
          },
          {
            "create_datetime": 1655462327,
            "custom_down_durations": "785738",
            "custom_uptime_ratio": "69.686",
            "friendly_name": "OHANA - WAN2 DAUPHIN",
            "http_password": "",
            "http_username": "",
            "id": 792031237,
            "interval": 300,
            "keyword_case_type": null,
            "keyword_type": null,
            "keyword_value": "",
            "lastLogTypeBeforeStartDate": {},
            "logs": [
              {
                "datetime": 1661968618,
                "duration": 785555,
                "id": 2729091319,
                "reason": {
                  "code": "444444",
                  "detail": "No Response"
                },
                "type": 1
              },
              {
                "datetime": 1661191966,
                "duration": 776652,
                "id": 2719867767,
                "reason": {
                  "code": "555555",
                  "detail": "Successful Response"
                },
                "type": 2
              },
              {
                "datetime": 1661191783,
                "duration": 183,
                "id": 2719865690,
                "reason": {
                  "code": "444444",
                  "detail": "No Response"
                },
                "type": 1
              },
              {
                "datetime": 1660142535,
                "duration": 1049248,
                "id": 2707958948,
                "reason": {
                  "code": "555555",
                  "detail": "Successful Response"
                },
                "type": 2
              },
              {
                "datetime": 1656506465,
                "duration": 3636070,
                "id": 2665664410,
                "reason": {
                  "code": "444444",
                  "detail": "No Response"
                },
                "type": 1
              },
              {
                "datetime": 1656096018,
                "duration": 410447,
                "id": 2661048148,
                "reason": {
                  "code": "555555",
                  "detail": "Successful Response"
                },
                "type": 2
              },
              {
                "datetime": 1656095964,
                "duration": 54,
                "id": 2661047634,
                "reason": {
                  "code": "444444",
                  "detail": "No Response"
                },
                "type": 1
              },
              {
                "datetime": 1655929081,
                "duration": 166883,
                "id": 2659118292,
                "reason": {
                  "code": "555555",
                  "detail": "Successful Response"
                },
                "type": 2
              },
              {
                "datetime": 1655928304,
                "duration": 777,
                "id": 2659109814,
                "reason": {
                  "code": "444444",
                  "detail": "No Response"
                },
                "type": 1
              },
              {
                "datetime": 1655476765,
                "duration": 451539,
                "id": 2650703143,
                "reason": {
                  "code": "555555",
                  "detail": "Successful Response"
                },
                "type": 2
              },
              {
                "datetime": 1655476728,
                "duration": 37,
                "id": 2650702865,
                "reason": {
                  "code": "98",
                  "detail": "Monitor started"
                },
                "type": 98
              }
            ],
            "port": "",
            "status": 9,
            "sub_type": "",
            "timeout": null,
            "type": 3,
            "url": "ohana2.sbh.cloud"
          }
        ],
        "pagination": {
          "limit": 50,
          "offset": 0,
          "total": 5
        },
        "stat": "ok"
      },
      "duration": 233930483,
      "error": "",
      "query": {
        "alias": "",
        "columns": [
          {
            "selector": "datetime",
            "text": "start",
            "type": "timestamp_epoch_s"
          },
          {
            "selector": "type",
            "text": " ",
            "type": "number"
          }
        ],
        "csv_options": {
          "columns": "",
          "comment": "",
          "delimiter": "",
          "relax_column_count": false,
          "skip_empty_lines": false,
          "skip_lines_with_error": false
        },
        "data": "",
        "expression": "",
        "filters": [],
        "format": "table",
        "global_query_id": "",
        "groq": "",
        "json_options": {
          "columnar": false,
          "root_is_not_array": false
        },
        "query_mode": "",
        "refId": "COGILOG",
        "root_selector": "monitors[0].logs",
        "seriesCount": 0,
        "source": "url",
        "type": "json",
        "uql": "",
        "url": "the_url",
        "url_options": {
          "data": "{\n    \"api_key\": \"***\",\n    \"offset\": \"0\",\n    \"custom_uptime_ratios\": \"30\",\n    \"search\":\"OHANA\",\n    \"logs\":\"1\"\n}",
          "headers": null,
          "method": "POST",
          "params": null
        }
      },
      "responseCodeFromServer": 200
    },
    "executedQueryString": "###############\n## URL\n###############\n\n***\n\n###############\n## Curl Command\n###############\n\ncurl -X 'POST' -d '{\n    \"api_key\": \"***\",\n    \"offset\": \"0\",\n    \"custom_uptime_ratios\": \"30\",\n    \"search\":\"OHANA\",\n    \"logs\":\"1\"\n}' -H 'Content-Type: application/json' 'the_url'"
  },

sorry for the delay

[yosiasz]

parses as invalid json. please vet json data on online json validator?

[ohanasbh]

Here is a valid JSON.
thanks a lot for all your help.

[{
	"schema": {
		"refId": "COGILOG",
		"meta": {
			"custom": {
				"data": {
					"monitors": [{
							"create_datetime": 1661423792,
							"custom_down_durations": "210",
							"custom_uptime_ratio": "99.986",
							"friendly_name": "OHANA - COGILOG",
							"http_password": "",
							"http_username": "",
							"id": 792542720,
							"interval": 60,
							"keyword_case_type": null,
							"keyword_type": null,
							"keyword_value": "",
							"lastLogTypeBeforeStartDate": {},
							"logs": [{
									"datetime": 1662069251,
									"duration": 918119,
									"id": 2730362006,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1662069104,
									"duration": 147,
									"id": 2730360265,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1662015977,
									"duration": 53127,
									"id": 2729730788,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1662015914,
									"duration": 63,
									"id": 2729729838,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1661438230,
									"duration": 577684,
									"id": 2722930960,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1661438192,
									"duration": 38,
									"id": 2722930746,
									"reason": {
										"code": "98",
										"detail": "Monitor started"
									},
									"type": 98
								}
							],
							"port": 64998,
							"status": 2,
							"sub_type": 99,
							"timeout": 30,
							"type": 4,
							"url": "ohana.sbh.cloud"
						},
						{
							"create_datetime": 1661423956,
							"custom_down_durations": "23375",
							"custom_uptime_ratio": "98.487",
							"friendly_name": "OHANA - GRAFANA",
							"http_password": "",
							"http_username": "",
							"id": 792542747,
							"interval": 60,
							"keyword_case_type": null,
							"keyword_type": null,
							"keyword_value": "",
							"lastLogTypeBeforeStartDate": {},
							"logs": [{
									"datetime": 1662069239,
									"duration": 918131,
									"id": 2730361869,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1662069104,
									"duration": 135,
									"id": 2730360266,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1661792936,
									"duration": 276168,
									"id": 2726887256,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1661778159,
									"duration": 14777,
									"id": 2726695978,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1661462474,
									"duration": 315685,
									"id": 2723197990,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1661462410,
									"duration": 64,
									"id": 2723197356,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1661462295,
									"duration": 115,
									"id": 2723195995,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1661462257,
									"duration": 38,
									"id": 2723195817,
									"reason": {
										"code": "98",
										"detail": "Monitor started"
									},
									"type": 98
								},
								{
									"datetime": 1661458156,
									"duration": 4101,
									"id": 2723151651,
									"reason": {
										"code": "99",
										"detail": "Monitor paused"
									},
									"type": 99
								},
								{
									"datetime": 1661449757,
									"duration": 8399,
									"id": 2723060505,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1661438393,
									"duration": 11364,
									"id": 2722932897,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1661438356,
									"duration": 37,
									"id": 2722932730,
									"reason": {
										"code": "98",
										"detail": "Monitor started"
									},
									"type": 98
								}
							],
							"port": 3000,
							"status": 2,
							"sub_type": 99,
							"timeout": 30,
							"type": 4,
							"url": "ohana.sbh.cloud"
						},
						{
							"create_datetime": 1661424092,
							"custom_down_durations": "436826",
							"custom_uptime_ratio": "71.791",
							"friendly_name": "OHANA - RTI",
							"http_password": "",
							"http_username": "",
							"id": 792542771,
							"interval": 60,
							"keyword_case_type": null,
							"keyword_type": null,
							"keyword_value": "",
							"lastLogTypeBeforeStartDate": {},
							"logs": [{
									"datetime": 1662584595,
									"duration": 402775,
									"id": 2736522296,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1662149589,
									"duration": 435006,
									"id": 2731349510,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1662147438,
									"duration": 2151,
									"id": 2731323096,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1662145983,
									"duration": 1455,
									"id": 2731306763,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1662069331,
									"duration": 76652,
									"id": 2730363089,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1662069099,
									"duration": 232,
									"id": 2730360217,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1662064858,
									"duration": 4241,
									"id": 2730311941,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1662064793,
									"duration": 65,
									"id": 2730311216,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1661792650,
									"duration": 272143,
									"id": 2726883769,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1661792582,
									"duration": 68,
									"id": 2726882930,
									"reason": {
										"code": "0",
										"detail": "Timeout"
									},
									"type": 1
								},
								{
									"datetime": 1661438852,
									"duration": 353730,
									"id": 2722938204,
									"reason": {
										"code": "1",
										"detail": "OK"
									},
									"type": 2
								},
								{
									"datetime": 1661438815,
									"duration": 37,
									"id": 2722938040,
									"reason": {
										"code": "98",
										"detail": "Monitor started"
									},
									"type": 98
								}
							],
							"port": 4110,
							"status": 2,
							"sub_type": 99,
							"timeout": 30,
							"type": 4,
							"url": "ohana.sbh.cloud"
						},
						{
							"create_datetime": 1655462327,
							"custom_down_durations": "2592000",
							"custom_uptime_ratio": "0.000",
							"friendly_name": "OHANA - WAN1 WISP FTTH",
							"http_password": "",
							"http_username": "",
							"id": 792031236,
							"interval": 300,
							"keyword_case_type": null,
							"keyword_type": null,
							"keyword_value": "",
							"lastLogTypeBeforeStartDate": {},
							"logs": [{
									"datetime": 1655479989,
									"duration": 7507381,
									"id": 2650738986,
									"reason": {
										"code": "444444",
										"detail": "No Response"
									},
									"type": 1
								},
								{
									"datetime": 1655479551,
									"duration": 438,
									"id": 2650734052,
									"reason": {
										"code": "555555",
										"detail": "Successful Response"
									},
									"type": 2
								},
								{
									"datetime": 1655477789,
									"duration": 1762,
									"id": 2650715372,
									"reason": {
										"code": "444444",
										"detail": "No Response"
									},
									"type": 1
								},
								{
									"datetime": 1655476765,
									"duration": 1024,
									"id": 2650703121,
									"reason": {
										"code": "555555",
										"detail": "Successful Response"
									},
									"type": 2
								},
								{
									"datetime": 1655476728,
									"duration": 37,
									"id": 2650702864,
									"reason": {
										"code": "98",
										"detail": "Monitor started"
									},
									"type": 98
								}
							],
							"port": "",
							"status": 9,
							"sub_type": "",
							"timeout": null,
							"type": 3,
							"url": "ohana1.sbh.cloud"
						},
						{
							"create_datetime": 1655462327,
							"custom_down_durations": "1018935",
							"custom_uptime_ratio": "60.689",
							"friendly_name": "OHANA - WAN2 DAUPHIN",
							"http_password": "",
							"http_username": "",
							"id": 792031237,
							"interval": 300,
							"keyword_case_type": null,
							"keyword_type": null,
							"keyword_value": "",
							"lastLogTypeBeforeStartDate": {},
							"logs": [{
									"datetime": 1661968618,
									"duration": 1018752,
									"id": 2729091319,
									"reason": {
										"code": "444444",
										"detail": "No Response"
									},
									"type": 1
								},
								{
									"datetime": 1661191966,
									"duration": 776652,
									"id": 2719867767,
									"reason": {
										"code": "555555",
										"detail": "Successful Response"
									},
									"type": 2
								},
								{
									"datetime": 1661191783,
									"duration": 183,
									"id": 2719865690,
									"reason": {
										"code": "444444",
										"detail": "No Response"
									},
									"type": 1
								},
								{
									"datetime": 1660142535,
									"duration": 1049248,
									"id": 2707958948,
									"reason": {
										"code": "555555",
										"detail": "Successful Response"
									},
									"type": 2
								},
								{
									"datetime": 1656506465,
									"duration": 3636070,
									"id": 2665664410,
									"reason": {
										"code": "444444",
										"detail": "No Response"
									},
									"type": 1
								},
								{
									"datetime": 1656096018,
									"duration": 410447,
									"id": 2661048148,
									"reason": {
										"code": "555555",
										"detail": "Successful Response"
									},
									"type": 2
								},
								{
									"datetime": 1656095964,
									"duration": 54,
									"id": 2661047634,
									"reason": {
										"code": "444444",
										"detail": "No Response"
									},
									"type": 1
								},
								{
									"datetime": 1655929081,
									"duration": 166883,
									"id": 2659118292,
									"reason": {
										"code": "555555",
										"detail": "Successful Response"
									},
									"type": 2
								},
								{
									"datetime": 1655928304,
									"duration": 777,
									"id": 2659109814,
									"reason": {
										"code": "444444",
										"detail": "No Response"
									},
									"type": 1
								},
								{
									"datetime": 1655476765,
									"duration": 451539,
									"id": 2650703143,
									"reason": {
										"code": "555555",
										"detail": "Successful Response"
									},
									"type": 2
								},
								{
									"datetime": 1655476728,
									"duration": 37,
									"id": 2650702865,
									"reason": {
										"code": "98",
										"detail": "Monitor started"
									},
									"type": 98
								}
							],
							"port": "",
							"status": 9,
							"sub_type": "",
							"timeout": null,
							"type": 3,
							"url": "ohana2.sbh.cloud"
						}
					],
					"pagination": {
						"limit": 50,
						"offset": 0,
						"total": 5
					},
					"stat": "ok"
				},
				"duration": 184328658,
				"error": "",
				"query": {
					"alias": "",
					"columns": [{
							"selector": "datetime",
							"text": "start",
							"type": "timestamp_epoch_s"
						},
						{
							"selector": "type",
							"text": " ",
							"type": "number"
						}
					],
					"csv_options": {
						"columns": "",
						"comment": "",
						"delimiter": "",
						"relax_column_count": false,
						"skip_empty_lines": false,
						"skip_lines_with_error": false
					},
					"data": "",
					"expression": "",
					"filters": [],
					"format": "table",
					"global_query_id": "",
					"groq": "",
					"json_options": {
						"columnar": false,
						"root_is_not_array": false
					},
					"query_mode": "",
					"refId": "COGILOG",
					"root_selector": "monitors[0].logs",
					"seriesCount": 0,
					"source": "url",
					"type": "json",
					"uql": "",
					"url": "*url*",
					"url_options": {
						"data": "{\n    \"api_key\": \"--api_key--\",\n    \"offset\": \"0\",\n    \"custom_uptime_ratios\": \"30\",\n    \"search\":\"OHANA\",\n    \"logs\":\"1\"\n}",
						"headers": null,
						"method": "POST",
						"params": null
					}
				},
				"responseCodeFromServer": 200
			},
			"executedQueryString": "###############\n## URL\n###############\n\n*url\n\n###############\n## Curl Command\n###############\n\ncurl -X 'POST' -d '{\n    \"api_key\": \"--api_key--\",\n    \"offset\": \"0\",\n    \"custom_uptime_ratios\": \"30\",\n    \"search\":\"OHANA\",\n    \"logs\":\"1\"\n}' -H 'Content-Type: application/json' '*url*'"
		},
		"name": "COGILOG",
		"fields": [{
				"name": "start",
				"config": {
					"selector": "datetime",
					"custom": {
						"lineWidth": 0,
						"fillOpacity": 100
					},
					"color": {
						"mode": "fixed"
					},
					"thresholds": {
						"mode": "absolute",
						"steps": [{
								"color": "green",
								"value": null
							},
							{
								"color": "red",
								"value": 80
							}
						]
					}
				},
				"type": "time"
			},
			{
				"name": " ",
				"config": {
					"selector": "type",
					"custom": {
						"lineWidth": 0,
						"fillOpacity": 100,
						"spanNulls": false
					},
					"color": {
						"mode": "fixed"
					},
					"mappings": [{
						"options": {
							"1": {
								"color": "dark-red",
								"index": 0,
								"text": "Down"
							},
							"2": {
								"color": "dark-green",
								"index": 1,
								"text": "Up"
							},
							"98": {
								"color": "text",
								"index": 3,
								"text": "started"
							},
							"99": {
								"color": "transparent",
								"index": 2,
								"text": "paused"
							}
						},
						"type": "value"
					}],
					"thresholds": {
						"mode": "absolute",
						"steps": [{
								"color": "green",
								"value": null
							},
							{
								"color": "red",
								"value": 80
							}
						]
					}
				},
				"type": "number"
			}
		]
	},
	"data": {
		"values": [
			[
				"2022-09-01T21:54:11.000Z",
				"2022-09-01T21:51:44.000Z",
				"2022-09-01T07:06:17.000Z",
				"2022-09-01T07:05:14.000Z",
				"2022-08-25T14:37:10.000Z",
				"2022-08-25T14:36:32.000Z"
			],
			[
				2,
				1,
				2,
				1,
				2,
				98
			]
		]
	}
}]

[yosiasz]

use UQL and jsonata

parse-json
| jsonata "schema.meta.custom.data.monitors"
| extend "start"=unixtime_seconds_todatetime("create_datetime")
| project "type","start", "friendly_name"

[ohanasbh]

Wow, thanks a lot for this, i will try it asap.

[ohanasbh]

I have been trying to make it work with UQL and jsonata.

The input of the timeline should be logs.startdate and logs.type for each monitor.
This allows me to show up/down time sequence for each monitor.

but I don’t know how to structure my data with JSONata so that the timeline panel understands it.

I’ve been using this : JSONata Playground - Get familiar with the JSONata language to try and structure the data.
I ended up with this : schema.meta.custom.data.monitors.logs.[datetime,type]
but this still won’t be accepted by the panel.

I am really gratefull for your help.

[yosiasz]

Which data points do you want to plot and what kind of visualization?
I do not see

logs.startdate 

[ohanasbh]

I want to use the state timeline visualisation to display the on/off timeline of my monitors.

the input needed for that are logs.datetime (start of each state) and logs.type (value of each state) for each monitor

I am sorry if I am unclear, english is not my native language.

[yosiasz]

logs is an array. so which one do you want. all of them?

"logs": [
		{
			"datetime": 1662069251,
			"duration": 918119,
			"id": 2730362006,
			"reason": {
				"code": "1",
				"detail": "OK"
			},
			"type": 2
		},
		{
			"datetime": 1662069104,
			"duration": 147,
			"id": 2730360265,
			"reason": {
				"code": "0",
				"detail": "Timeout"
			},
			"type": 1
		},
		{
			"datetime": 1662015977,
			"duration": 53127,
			"id": 2729730788,
			"reason": {
				"code": "1",
				"detail": "OK"
			},
			"type": 2
		},
		{
			"datetime": 1662015914,
			"duration": 63,
			"id": 2729729838,
			"reason": {
				"code": "0",
				"detail": "Timeout"
			},
			"type": 1
		},
		{
			"datetime": 1661438230,
			"duration": 577684,
			"id": 2722930960,
			"reason": {
				"code": "1",
				"detail": "OK"
			},
			"type": 2
		},
		{
			"datetime": 1661438192,
			"duration": 38,
			"id": 2722930746,
			"reason": {
				"code": "98",
				"detail": "Monitor started"
			},
			"type": 98
		}
	]

[ohanasbh]

yeah, that’s the challenge.
Each array represents one monitor.
the goal is to display one stateline per monitor, meaning one stateline per logs array.

[yosiasz]

Also check this out https://try.jsonata.org/EUXOzQjvE

so which log do you want from that logs array ? this is another plugin you can run with apache echarts

@mikhailvolkov is the owner. You have more control via javascript data manipulation.

[yesoreyeram]

UQL can be enhanced like this to get each line of log item. Hope this gives some hint.

parse-json
| scope "0.schema.meta.custom.data.monitors"
| extend "create_datetime"=unixtime_seconds_todatetime("create_datetime")
| mv-expand "log"="logs"
| extend "log_date"=unixtime_seconds_todatetime("log.datetime"), "log_duration"="log.duration", "log_reason"="log.reason.detail"
# | project-away "keyword_case_type", "lastLogTypeBeforeStartDate"
| project "friendly_name",  "log_date", "log_duration", "log_reason"
# | summarize min("log_date") by "log_reason"

[ohanasbh]

Thanks a lot!
I managed to get (almost) the result I wanted thanks to you both.

I used this :

parse-json
| scope "0.schema.meta.custom.data.monitors"
| extend "create_datetime"=unixtime_seconds_todatetime("create_datetime")
| mv-expand "log"="logs"
| extend "log_date"=unixtime_seconds_todatetime("log.datetime"), "log_type"="log.type"
# | project-away "keyword_case_type", "lastLogTypeBeforeStartDate"
| project "friendly_name",  "log_date", "log_type"
# | summarize min("log_date") by "log_type"

and a “prepare time series” tranformation with format : multi-frame time series.

I still have to rename the series but I will figure that part out.

My real remaining problem is that I used inline source with the json I provided you.
And I did that because when I switch to UQL type query, the query doesn’t seem to take my arguments into account properly.

Here is what I mean :
with json type query, the arguments are applied, i get monitors with ohana in the name, and the logs

but when I switch to UQL type query, I can’t get it to take the arguments into account

I have tried putting in my arguments as query params tab or as body of the request, nothing seems to work.

Any ideas as to how to solve this?

Thanks a lot for all your help!

[ohanasbh]

Here are query inspector images to show you what I mean.

[yosiasz]

Time to start reading the docu

Grafana Infinity Datasource

Also check here and start groking.

or

[ohanasbh]

I have tried this :

and that :

i will try with the settings tab you pointed out.

Thanks

[yosiasz]

What you have looks like url encoded query. check this out

[ohanasbh]

Thanks for your help!
I tried putting the arguments in the data-source definition as suggested but it’s not working either.
I tried adding them to the headers to just to be sure and it won’t work either.

I am quite stunned by the difference in behaviour between json-format queries and uql-format queries. Do you have any explanation for this difference?