Can't get this JSON structure working #164

ideasman69 · 2021-09-02T01:19:41Z

ideasman69
Sep 2, 2021

Hi guys,

I'm having trouble with this JSON structure:

[
  {
    "origin": "TS",
    "id": "M8C8E02434D442725422CCB337057792F",
    "type": "1.5.1:1",
    "self": "https://metricsourcehost01/uimapiM8C8E02434D442725422CCB337057792F",
    "source": "destinationhost01.our.domain.net",
    "target": "destinationhost01.our.domain.net-0",
    "probe": "cdm",
    "for_computer_system": {
      "id": "14873",
      "self": "https://metricsourcehost01/uimapi/devices/14873",
      "name": "destinationhost01.our.domain.net",
      "ip": "10.1.1.16"
    },
    "for_device": {
      "id": "D4F3D290D787D3FA4E7CD2824BFA6B1C8",
      "self": "https://metricsourcehost01/uimapi/devices/D4F3D290D787D3FA4E7CD2824BFA6B1C8"
    },
    "for_configuration_item": {
      "id": "CCE5006B73554FE7D307C1A355429286A",
      "self": "https://metricsourcehost01/uimapi/TBD/CCE5006B73554FE7D307C1A355429286A",
      "name": "CPU-0",
      "qosName": "QOS_CPU_MULTI_USAGE",
      "description": "Individual CPU Usage",
      "unit": "%"
    },
    "uimMetricDefinition": null,
    "minSampleValue": 61.17,
    "maxSampleValue": 72.78,
    "meanSampleValue": 64.864,
    "sample": [
      {
        "time": "2021-09-02T00:50:32.000Z",
        "timeSinceEpoch": 1630543832,
        "value": 61.17,
        "rate": 60
      },
      {
        "time": "2021-09-02T00:49:32.000Z",
        "timeSinceEpoch": 1630543772,
        "value": 63.52,
        "rate": 60
      },
      {
        "time": "2021-09-02T00:48:32.000Z",
        "timeSinceEpoch": 1630543712,
        "value": 62.79,
        "rate": 60
      },
      {
        "time": "2021-09-02T00:47:32.000Z",
        "timeSinceEpoch": 1630543652,
        "value": 64.06,
        "rate": 60
      },
      {
        "time": "2021-09-02T00:46:32.000Z",
        "timeSinceEpoch": 1630543592,
        "value": 72.78,
        "rate": 60
      }
    ]
  },
  {
    "origin": "TS",
    "id": "M9D90857B9F9BE73EB15912D3314DB2DA",
    "type": "1.5.1:1",
    "self": "https://metricsourcehost01/uimapiM9D90857B9F9BE73EB15912D3314DB2DA",
    "source": "destinationhost01.our.domain.net",
    "target": "destinationhost01.our.domain.net-1",
    "probe": "cdm",
    "for_computer_system": {
      "id": "14873",
      "self": "https://metricsourcehost01/uimapi/devices/14873",
      "name": "destinationhost01.our.domain.net",
      "ip": "10.1.1.16"
    },
    "for_device": {
      "id": "D4F3D290D787D3FA4E7CD2824BFA6B1C8",
      "self": "https://metricsourcehost01/uimapi/devices/D4F3D290D787D3FA4E7CD2824BFA6B1C8"
    },
    "for_configuration_item": {
      "id": "CF1D7A708DD4C6C9D303025AE3D2334AE",
      "self": "https://metricsourcehost01/uimapi/TBD/CF1D7A708DD4C6C9D303025AE3D2334AE",
      "name": "CPU-1",
      "qosName": "QOS_CPU_MULTI_USAGE",
      "description": "Individual CPU Usage",
      "unit": "%"
    },
    "uimMetricDefinition": null,
    "minSampleValue": 59.85,
    "maxSampleValue": 72.31,
    "meanSampleValue": 64.296,
    "sample": [
      {
        "time": "2021-09-02T00:50:32.000Z",
        "timeSinceEpoch": 1630543832,
        "value": 59.85,
        "rate": 60
      },
      {
        "time": "2021-09-02T00:49:32.000Z",
        "timeSinceEpoch": 1630543772,
        "value": 63.88,
        "rate": 60
      },
      {
        "time": "2021-09-02T00:48:32.000Z",
        "timeSinceEpoch": 1630543712,
        "value": 60.17,
        "rate": 60
      },
      {
        "time": "2021-09-02T00:47:32.000Z",
        "timeSinceEpoch": 1630543652,
        "value": 65.27,
        "rate": 60
      },
      {
        "time": "2021-09-02T00:46:32.000Z",
        "timeSinceEpoch": 1630543592,
        "value": 72.31,
        "rate": 60
      }
    ]
  }
]

If I target only the time and value samples into the following two fields, its a great start:
Time: $.[*].sample[*].time
Value: $.[*].sample[*].value

However, all the samples get combined together with no way to differenciate between the two different CPU cores.

I'd love to use the $[*].for_configuration_item.name value in a third column next to each sample in its respective node.

Is what I'm after doable with this plugin?

Thanks!

Answered by ideasman69

Sep 8, 2021

Hi Marcus,

Just thought I'd loop back to say that I found a way utilising filtering and the ^ function in JSONPath Plus.

My fields now look like this:

Figured I'd filter using !=0 on timeSinceEpoch as it shouldn't go back to 0 :)

The table view shows the additional 'name' column which has a CPU-x for each sample array value:

Using the experimental Group by function and selecting the new name data groups them as expected:

and in turn, so does the graph:

Thanks again for this amazing plugin 👍

View full answer

marcusolsson · 2021-09-02T04:03:24Z

marcusolsson
Sep 2, 2021

I'm not sure it's possible to achieve using JSON Paths only. Would it work if you created two separate Grafana queries and used filters to return a subset of the samples in each query?

5 replies

ideasman69 Sep 2, 2021
Author

Hi Marcus, thanks for the response.

I can defintiely give it a go, but my issue seems to be distinguishing between the two (or more) sample arrays. The number of CPU cores differs across our server fleet so I'm not sure which logic would allow the samples to be grouped, and to reiterate over any subsequent data sets (if they exist)

It's left me scratching my head for the last few days

ideasman69 Sep 2, 2021
Author

Splitting out the queries does seem to work:

but it's very slow. I'm not sure if each of these queries is hitting our source API with the exact same request...

It would be great if the queries could be grouped by the initial index number when using [*] after the object/element root

marcusolsson Sep 2, 2021

Yeah. It's not an ideal solution for sure. Right now the query editor heavily depends on JSON Paths, which is a bit limiting in this way. I've been thinking about building a more graphical query editor that would let you define nested JSON Paths, which would let me control the logic more than now (and support other query languages). For now though, I don't have a good solution for you unfortunately :(

ideasman69 Sep 2, 2021
Author

No worries! Thanks very much for your time. The plugin is amazing and has managed to get me 90% to my goal, so it's all very much appreciated.

I've got no idea if it's possible, but if there's some way of adding a feature to have any children arrays grouped by their parent object - that would be perfect:

Funnily enough - if I just use combine all the data, the graph almost looks ok:

Anyway - thank you again and keep up the amazing work!

Cheers

marcusolsson Sep 3, 2021

Grafana or the plugin doesn't sort your timestamps, which results in the crossing lines when the timestamps are unordered. You can sort your timestamps with a transformation I believe.

Thanks!

ideasman69 · 2021-09-08T03:20:41Z

ideasman69
Sep 8, 2021
Author

Hi Marcus,

Just thought I'd loop back to say that I found a way utilising filtering and the ^ function in JSONPath Plus.

My fields now look like this:

Figured I'd filter using !=0 on timeSinceEpoch as it shouldn't go back to 0 :)

The table view shows the additional 'name' column which has a CPU-x for each sample array value:

Using the experimental Group by function and selecting the new name data groups them as expected:

and in turn, so does the graph:

Thanks again for this amazing plugin 👍

3 replies

marcusolsson Sep 8, 2021

Thanks for sharing! I've never seen ^ being used like that. Have to try it out myself!

ideasman69 Sep 8, 2021
Author

No worries! After playing around with it a bit more, I've found it doesn't even need a filter.

Now just using the following to build the graph:

$.[*].sample[*].value
$.[*].sample[*].time
$.[*].sample[*].value^^^^.for_configuration_item.name

arkadiichystiakov Mar 20, 2024

Hi ideasman! I have been trying to figure out the same promblem the whole day. If you still have that in mind, would be great if you could help me.
here is a data sample:

{
  "asset_group_id": 467837,
  "unit": "liters",
  "resolution": "hourly",
  "asset_data": [
    {
      "asset_id": 1,
      "asset_name": "A1",
      "data": {
        "timestamps": [
          "2022-04-02T01:00:00",
          "2022-04-02T02:00:00",
          "2022-04-02T03:00:00",
          "2022-04-02T05:00:00",
          "2022-04-02T08:00:00",
          "2022-04-02T10:00:00",
          "2022-04-02T13:00:00",
          "2022-04-02T14:00:00",
          "2022-04-02T17:00:00",
          "2022-04-02T18:00:00",
          "2022-04-02T19:00:00",
          "2022-04-02T20:00:00",
          "2022-04-02T21:00:00"
        ],
        "consumption": [
          3,
          11,
          21,
          9,
          35,
          23,
          20,
          22,
          28,
          150,
          147,
          81,
          14
        ],
        "total": 564
      }
    },
    {
      "asset_id": 2,
      "asset_name": "A2",
      "data": {
        "timestamps": [
          "2022-04-02T00:00:00",
          "2022-04-02T01:00:00",
          "2022-04-02T02:00:00",
          "2022-04-02T03:00:00",
          "2022-04-02T04:00:00",
          "2022-04-02T05:00:00",
          "2022-04-02T06:00:00",
          "2022-04-02T07:00:00",
          "2022-04-02T08:00:00",
          "2022-04-02T09:00:00",
          "2022-04-02T10:00:00",
          "2022-04-02T11:00:00",
          "2022-04-02T12:00:00",
          "2022-04-02T13:00:00",
          "2022-04-02T14:00:00",
          "2022-04-02T15:00:00",
          "2022-04-02T16:00:00",
          "2022-04-02T17:00:00",
          "2022-04-02T20:00:00",
          "2022-04-02T21:00:00",
          "2022-04-02T22:00:00",
          "2022-04-02T23:00:00"
        ],
        "consumption": [
          41,
          40,
          45,
          41,
          45,
          51,
          50,
          58,
          65,
          55,
          36,
          56,
          51,
          48,
          36,
          52,
          55,
          8,
          20,
          49,
          34,
          28
        ],
        "total": 964
      }
    },
    {
      "asset_id": 3,
      "asset_name": "A3",
      "data": {
        "timestamps": [
          "2022-04-02T01:00:00",
          "2022-04-02T03:00:00",
          "2022-04-02T10:00:00",
          "2022-04-02T13:00:00",
          "2022-04-02T14:00:00",
          "2022-04-02T17:00:00",
          "2022-04-02T18:00:00",
          "2022-04-02T19:00:00",
          "2022-04-02T20:00:00",
          "2022-04-02T23:00:00"
        ],
        "consumption": [
          0,
          2,
          0,
          1,
          1,
          47,
          71,
          53,
          0,
          0
        ],
        "total": 175
      }
    },
    {
      "asset_id": 4,
      "asset_name": "A4",
      "data": {
        "timestamps": [
          "2022-04-02T01:00:00",
          "2022-04-02T02:00:00",
          "2022-04-02T03:00:00",
          "2022-04-02T04:00:00",
          "2022-04-02T05:00:00",
          "2022-04-02T06:00:00",
          "2022-04-02T07:00:00",
          "2022-04-02T08:00:00",
          "2022-04-02T09:00:00",
          "2022-04-02T10:00:00",
          "2022-04-02T13:00:00",
          "2022-04-02T14:00:00",
          "2022-04-02T17:00:00",
          "2022-04-02T18:00:00",
          "2022-04-02T19:00:00",
          "2022-04-02T20:00:00",
          "2022-04-02T21:00:00",
          "2022-04-02T22:00:00",
          "2022-04-02T23:00:00"
        ],
        "consumption": [
          2,
          83,
          83,
          90,
          101,
          105,
          119,
          128,
          113,
          55,
          16,
          58,
          29,
          8,
          40,
          79,
          100,
          69,
          39
        ],
        "total": 1317
      }
    }
  ],
  "total_consumption": 3020
}

This is how my fields look now:

It somehow won't append the asset_id automatically to every value, as in your expample, it just displays 2 arrays of data. What did you do so that the name column appeared

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Can't get this JSON structure working #164

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 2 comments 8 replies

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Can't get this JSON structure working #164

ideasman69 Sep 2, 2021

Replies: 2 comments · 8 replies

marcusolsson Sep 2, 2021

ideasman69 Sep 2, 2021 Author

ideasman69 Sep 2, 2021 Author

marcusolsson Sep 2, 2021

ideasman69 Sep 2, 2021 Author

marcusolsson Sep 3, 2021

ideasman69 Sep 8, 2021 Author

marcusolsson Sep 8, 2021

ideasman69 Sep 8, 2021 Author

arkadiichystiakov Mar 20, 2024

ideasman69
Sep 2, 2021

Replies: 2 comments 8 replies

marcusolsson
Sep 2, 2021

ideasman69 Sep 2, 2021
Author

ideasman69 Sep 2, 2021
Author

ideasman69 Sep 2, 2021
Author

ideasman69
Sep 8, 2021
Author

ideasman69 Sep 8, 2021
Author