Unable to merge promql request result in one columne

[nomade]

Hello everyone,

I’m trying to build a dashboard to monitor the update status of my VMs as well as the last node boot time.

To do that, I use a script that retrive the last use of yum/dnf update command, convert the date in epoch format and store it in a file.
With node_exporter and the textfile collector, I expose this metric in prometheus.

In Prometheus we can see the metric like this :

rhel_last_update_timestamp{device_type=“UNIX”, instance=“PROMETHEUS-PARIS”, job=“node_exporter”} 1757429520

For the boot time, I use the built-in metric of node_exporter “node_boot_time_seconds”

On Grafana I use a promql request to calculate the difference in days between the present time and the timestamp of the two metrics like below :

(time() - rhel_last_update_timestamp{device_type=“UNIX”}) / 86400

(time() - node_boot_time_seconds{device_type=“UNIX”}) / 86400

the idea is to have a table with 3 columnes, one for the name of the VM, one for the number of days since the last update and one for the number of days since the last boot and that every other VM is added in a line :

VM | Last Update | Last Boot

VM1 | Xday(s) | Xday(s)

VM2 | Xday(s) | Xday(s)

etc.

The problem is that every additional VM add 2 new columnes, one for the value of “time” which is used in the promql request to calculate the number of day and an other for the result.

I’ve tried to use transformation with Labels to fields, join by field and organize but without success.

I’m not able to force the values of each VM to fit into the two columes Last Update and Last Boot.

Can someone help me on this topic ? I don’t find anything about my problem on the web.

I use the version v12.0.2 of Grafana.

Thanks in advance for your help.

Regards,

Louis

[nomade]

{
  "id": 1,
  "type": "table",
  "title": "New panel",
  "gridPos": {
    "x": 0,
    "y": 0,
    "h": 18,
    "w": 14
  },
  "fieldConfig": {
    "defaults": {
      "custom": {
        "align": "auto",
        "cellOptions": {
          "type": "auto"
        },
        "inspect": false,
        "filterable": false
      },
      "mappings": [],
      "thresholds": {
        "mode": "absolute",
        "steps": [
          {
            "color": "green",
            "value": null
          }
        ]
      },
      "color": {
        "fixedColor": "red",
        "mode": "thresholds"
      },
      "decimals": 0,
      "unit": "none"
    },
    "overrides": [
      {
        "matcher": {
          "id": "byName",
          "options": "Last Update"
        },
        "properties": [
          {
            "id": "thresholds",
            "value": {
              "mode": "absolute",
              "steps": [
                {
                  "color": "green",
                  "value": null
                },
                {
                  "color": "#EAB839",
                  "value": 25
                },
                {
                  "color": "red",
                  "value": 30
                }
              ]
            }
          },
          {
            "id": "unit",
            "value": "day(s)"
          },
          {
            "id": "custom.cellOptions",
            "value": {
              "type": "color-background"
            }
          }
        ]
      },
      {
        "matcher": {
          "id": "byName",
          "options": "Last Reboot"
        },
        "properties": [
          {
            "id": "thresholds",
            "value": {
              "mode": "absolute",
              "steps": [
                {
                  "color": "green",
                  "value": null
                },
                {
                  "color": "red",
                  "value": 30
                }
              ]
            }
          },
          {
            "id": "custom.cellOptions",
            "value": {
              "type": "color-background"
            }
          },
          {
            "id": "unit",
            "value": "day(s)"
          }
        ]
      },
      {
        "matcher": {
          "id": "byName",
          "options": ""
        },
        "properties": []
      }
    ]
  },
  "transformations": [
    {
      "id": "labelsToFields",
      "options": {
        "keepLabels": [
          "instance"
        ]
      }
    },
    {
      "id": "joinByField",
      "options": {
        "byField": "instance",
        "mode": "outer"
      }
    },
    {
      "id": "organize",
      "options": {
        "excludeByName": {
          "Time 1": true,
          "Time 2": true,
          "Time 3": true,
          "Value 3": true
        },
        "includeByName": {},
        "indexByName": {},
        "renameByName": {
          "Time 2": "",
          "Value 1": "Last Update",
          "Value 2": "Last Reboot",
          "instance": "VM"
        }
      }
    }
  ],
  "pluginVersion": "12.0.2",
  "targets": [
    {
      "editorMode": "code",
      "expr": "(time() - rhel_last_update_timestamp{device_type=\"UNIX\"}) / 86400",
      "legendFormat": "{{instance}}",
      "range": true,
      "refId": "A"
    },
    {
      "datasource": {
        "type": "prometheus",
        "uid": "prometheus-main"
      },
      "editorMode": "code",
      "expr": "(time() - node_boot_time_seconds{device_type=\"UNIX\"}) / 86400",
      "hide": false,
      "instant": false,
      "legendFormat": "__auto",
      "range": true,
      "refId": "B"
    }
  ],
  "datasource": {
    "uid": "prometheus"
  },
  "options": {
    "showHeader": true,
    "cellHeight": "sm",
    "footer": {
      "show": false,
      "reducer": [
        "sum"
      ],
      "countRows": false,
      "fields": "",
      "enablePagination": false
    },
    "sortBy": []
  }
}

[nomade]

[dawiddebowski]

Hi, what you could do and should probably work is:

1. change the query type from range to instant and use Table format instead of timeseries

   

2. Use Merge Transformation or Join by fields and pick the label with your VM name (probably instance)

3. Use Organize Fields to hide unnecessary fields.

You might have to use an aggregation on your queries, like max(<current query>) by (instance) so that you’ll get less resutls for the Merge transformation to work.

[nomade]

Hi @dawiddebowski
Thanks a lot for your help, it’s work now !
I didn’t need to modify the queries with “max” and “by”; simply changing the type from “range” to ‘instant’ and using “Table” as the format allowed me to obtain the desired result.