Meta data in column name with Grafana + influxdb + Table panel

Grafana InfluxDB
1

Hello.
I’m trying to show data from influxdb with table panel.
Everything is good except column name.
Column name contains some kind of meta like below

oldestTimestamp {_start="2021-08-20 06:27:52 +0000 UTC", _stop="2021-08-20 06:47:52 +0000 UTC", category="XXX", exchange="YYY", orderQty="ZZZ", pair="MMM-NNN", strategyName="SSS"}

Whenever data is updated, column name changes as start/stop time varies.
I need to use transform function of Grafana to arrange the order of column but it’s not applicable as column changes everytime.

I searched this forum and googled but couldn’t find any solution.

table
table736×560 41.3 KB

2

Hi @platoniclove

Can you share your panel JSON? Can you describe exactly how you’d like your table to appear?

3

Hi @mattabrams
Thank you for answering.
I want column names to be just simple as influxdb’s field name like “pnlPcnt” not "pnlPcnt {_start=“2021-8…}”.
I attached my json model below.

{
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": "-- Grafana --",
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "gnetId": null,
  "graphTooltip": 0,
  "id": 9,
  "iteration": 1629786772415,
  "links": [],
  "panels": [
    {
      "datasource": "InfluxDB Cloud",
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 0,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "viz": false
            },
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "auto",
            "spanNulls": false,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 22,
        "w": 9,
        "x": 0,
        "y": 0
      },
      "id": 2,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "table",
          "placement": "bottom"
        },
        "tooltip": {
          "mode": "single"
        }
      },
      "targets": [
        {
          "query": "from(bucket: \"path-trading\")\r\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\r\n  |> filter(fn: (r) => r[\"_measurement\"] == \"path-info\")\r\n  |> filter(fn: (r) => r[\"_field\"] == \"pnlPcnt\")\r\n  |> filter(fn: (r) => r[\"category\"] == \"${OrderSizeCategory}\")  \r\n  |> filter(fn: (r) => r[\"exchange\"] == \"${ExchangeCategory}\")  \r\n  |> sort(columns:[\"StrategyName\"])\r\n  |> aggregateWindow(every: 5s, fn: last, createEmpty: false)\r\n  |> yield(name: \"last\")\r\n  ",
          "refId": "A"
        }
      ],
      "title": "Path Arb",
      "transformations": [],
      "type": "timeseries"
    },
    {
      "datasource": "InfluxDB Cloud",
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "custom": {
            "align": "auto",
            "displayMode": "auto"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": []
      },
      "gridPos": {
        "h": 22,
        "w": 15,
        "x": 9,
        "y": 0
      },
      "id": 4,
      "options": {
        "showHeader": true,
        "sortBy": []
      },
      "pluginVersion": "8.0.3",
      "targets": [
        {
          "query": "from(bucket: \"path-trading\")\r\n  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)\r\n  |> filter(fn: (r) => r[\"_measurement\"] == \"path-info\")\r\n  |> filter(fn: (r) => r[\"_field\"] == \"pnlPcnt\" or r[\"_field\"] == \"path1\" or r[\"_field\"] == \"path2\"or r[\"_field\"] == \"feeForward\" or r[\"_field\"] == \"feeBackward\" or r[\"_field\"] == \"oldestTimestamp\" )\r\n  |> filter(fn: (r) => r[\"category\"] == \"${OrderSizeCategory}\")  \r\n  |> filter(fn: (r) => r[\"strategyName\"] == \"${StrategyName}\")  \r\n  |> sort(columns:[\"_field\"], desc: true)\r\n  |> pivot(\r\n    rowKey:[\"_time\" ],\r\n    columnKey: [\"_field\"],\r\n    valueColumn: \"_value\"\r\n  ) \r\n  |> sort(columns:[\"_time\"], desc: true)",
          "refId": "A"
        }
      ],
      "title": "Pnl Table",
      "transformations": [],
      "type": "table"
    }
  ],
  "refresh": "",
  "schemaVersion": 30,
  "style": "dark",
  "tags": [],
  "templating": {
    "list": [
      {
        "allValue": null,
        "current": {
          "selected": false,
          "text": [
            "Big"
          ],
          "value": [
            "Big"
          ]
        },
        "description": "Order Size Category",
        "error": null,
        "hide": 0,
        "includeAll": false,
        "label": "OrderSizeCategory",
        "multi": true,
        "name": "OrderSizeCategory",
        "options": [
          {
            "selected": true,
            "text": "Big",
            "value": "Big"
          },
          {
            "selected": false,
            "text": "Small",
            "value": "Small"
          }
        ],
        "query": "Big,Small",
        "queryValue": "",
        "skipUrlSync": false,
        "type": "custom"
      },
      {
        "allValue": null,
        "current": {
          "selected": false,
          "text": "Global",
          "value": "Global"
        },
        "description": "ExchangeCategory",
        "error": null,
        "hide": 0,
        "includeAll": false,
        "label": "ExchangeCategory",
        "multi": false,
        "name": "ExchangeCategory",
        "options": [
          {
            "selected": true,
            "text": "Global",
            "value": "Global"
          },
          {
            "selected": false,
            "text": "Simple",
            "value": "Simple"
          }
        ],
        "query": "Global,Simple",
        "queryValue": "",
        "skipUrlSync": false,
        "type": "custom"
      },
      {
        "allValue": null,
        "current": {
          "selected": false,
          "text": "Global-XXX-YYY",
          "value": "Global-XXX-YYY"
        },
        "datasource": "InfluxDB Cloud",
        "definition": "import \"influxdata/influxdb/v1\"\r\nv1.tagValues(\r\n    bucket: v.bucket,\r\n    tag: \"strategyName\",\r\n    predicate: (r) => true,\r\n    start: -1d\r\n)",
        "description": null,
        "error": null,
        "hide": 0,
        "includeAll": false,
        "label": null,
        "multi": false,
        "name": "StrategyName",
        "options": [],
        "query": "import \"influxdata/influxdb/v1\"\r\nv1.tagValues(\r\n    bucket: v.bucket,\r\n    tag: \"strategyName\",\r\n    predicate: (r) => true,\r\n    start: -1d\r\n)",
        "refresh": 2,
        "regex": "",
        "skipUrlSync": false,
        "sort": 0,
        "type": "query"
      }
    ]
  },
  "time": {
    "from": "now-5m",
    "to": "now"
  },
  "timepicker": {},
  "timezone": "",
  "title": "Path Arb",
  "uid": "qRlBb_Z7z",
  "version": 15
}
4

In your first screenshot, you are using a transform to rename one field. Does this not accomplish what you want if you rename the other fields?

5

@mattabrams
Transformation didn’t work as I expected.
I tried “Organize fields” function of transformation to rearrange the order of fileds.
It took field name with meta data like "pnlPcnt {_start=“20210820…” which varies by refreshing data. So it works for current data but it became useless as field name changes.

Below is a json model configuration of transformation.

 "transformations": [
        {
          "id": "organize",
          "options": {
            "excludeByName": {},
            "indexByName": {
              "_time": 0,
              "feeBackward {_start=\"2021-08-28 03:27:34 +0000 UTC\", _stop=\"2021-08-28 03:32:34 +0000 UTC\", category=\"Big\", exchange=\"Global\", orderQty=\"3000\", pair=\"ADA-BTC\", strategyName=\"Global-ADA-BTC\"}": 4,
              "feeForward {_start=\"2021-08-28 03:27:34 +0000 UTC\", _stop=\"2021-08-28 03:32:34 +0000 UTC\", category=\"Big\", exchange=\"Global\", orderQty=\"3000\", pair=\"ADA-BTC\", strategyName=\"Global-ADA-BTC\"}": 3,
              "oldestTimestamp {_start=\"2021-08-28 03:27:34 +0000 UTC\", _stop=\"2021-08-28 03:32:34 +0000 UTC\", category=\"Big\", exchange=\"Global\", orderQty=\"3000\", pair=\"ADA-BTC\", strategyName=\"Global-ADA-BTC\"}": 6,
              "path1 {_start=\"2021-08-28 03:27:34 +0000 UTC\", _stop=\"2021-08-28 03:32:34 +0000 UTC\", category=\"Big\", exchange=\"Global\", orderQty=\"3000\", pair=\"ADA-BTC\", strategyName=\"Global-ADA-BTC\"}": 1,
              "path2 {_start=\"2021-08-28 03:27:34 +0000 UTC\", _stop=\"2021-08-28 03:32:34 +0000 UTC\", category=\"Big\", exchange=\"Global\", orderQty=\"3000\", pair=\"ADA-BTC\", strategyName=\"Global-ADA-BTC\"}": 2,
              "pnlPcnt {_start=\"2021-08-28 03:27:34 +0000 UTC\", _stop=\"2021-08-28 03:32:34 +0000 UTC\", category=\"Big\", exchange=\"Global\", orderQty=\"3000\", pair=\"ADA-BTC\", strategyName=\"Global-ADA-BTC\"}": 5
            },
            "renameByName": {}
          }
        }
      ],
6

Hello,

I am facing the same issue where I want to use custom column name dynamically.
Have you been able to achieve this functionality.

I have posted my issue and details here.

Please share your findings if you have any inputs.

Thanks and Regards,
Bhushan