Create a table panel that doesn't use the time variable

Grafana
1

Basically, what I’m trying to do is create a table containing the top ten hosts as sorted by incoming (and outgoing) traffic of the past five minutes.

Using the SQL ‘SELECT "bits_incoming" FROM "fastnetmon_default_rp"."hosts_traffic" WHERE time >= now() - 5m GROUP BY "host" LIMIT 10’ gets me the right data, but when using this as the base for a table panel I get this output for every half-second interval in the time range specified, which is very much not what I want.

Is there a way to tell it to stop grouping by time intervals at all, or am I using the wrong type of graph here?

2

For clarification, here’s the full JSON of the panel as I’ve got so far:

{
  "type": "table",
  "title": "Highest traffic",
  "gridPos": {
    "x": 0,
    "y": 9,
    "w": 12,
    "h": 9
  },
  "id": 6,
  "datasource": "Fastnetmon",
  "targets": [
    {
      "refId": "A",
      "policy": "fastnetmon_default_rp",
      "resultFormat": "table",
      "orderByTime": "ASC",
      "tags": [],
      "groupBy": [
        {
          "type": "tag",
          "params": [
            "host"
          ]
        }
      ],
      "select": [
        [
          {
            "type": "field",
            "params": [
              "bits_incoming"
            ]
          }
        ]
      ],
      "measurement": "hosts_traffic",
      "limit": 10
    }
  ],
  "styles": [
    {
      "type": "hidden",
      "pattern": "Time",
      "alias": "Time",
      "dateFormat": "YYYY-MM-DD HH:mm:ss"
    },
    {
      "unit": "short",
      "type": "number",
      "alias": "",
      "decimals": 2,
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "colorMode": null,
      "pattern": "/.*/",
      "thresholds": []
    }
  ],
  "transform": "table",
  "pageSize": null,
  "showHeader": true,
  "columns": [],
  "scroll": true,
  "fontSize": "100%",
  "sort": {
    "col": 2,
    "desc": true
  },
  "links": [],
  "timeFrom": "5m",
  "timeShift": null,
  "hideTimeOverride": true,
  "repeat": null,
  "description": "(Does not work correctly yet)"
}
3

Sounds strange. Grafana does not do any automatic group by time - your data source is responsible for that. Are you using mysql?

If you are on a newer version of Grafana, you can check the result returned by your sql query:

image
image2441×808 71.2 KB

4

It’s an influxdb data source fed by fastnetmon. I’ll see what I can get back, but every time I switch out of edit mode it fills in a time variable anyway.

EDIT: Yeah, looks like influx automatically adds a time factor:

{
“xhrStatus”: “complete”,
“request”: {
“method”: “GET”,
“url”: “api/datasources/proxy/2/query”,
“params”: {
“db”: “fastnetmon”,
“q”: “SELECT “bits_incoming” FROM “hosts_traffic” WHERE time >= now() - 5m GROUP BY “host” LIMIT 10”,
“epoch”: “ms”
},
“data”: null,
“precision”: “ms”
},
“response”: {
“results”: [
{
“statement_id”: 0,
“series”: [
{
“name”: “hosts_traffic”,
“tags”: {
“host”: “89.20.64.1”
},
“columns”: [
“time”,
“bits_incoming”
],
“values”: [
[
1522319818000,
168
],

(… etc. It’s a huge dataset, which is why I wanted the limit to begin with)

5

Oh, I didn’t see any mention of Influxdb in your question. It’s a different story if you are using InfluxDB - thought you were writing sql.

There is a data source setting - Min time interval:

image

There is also one per panel:

image
image906×165 10.5 KB

There is also a Format As field - have you chosen Time Series or Table there?

6

I originally went with “table” because that’s what I thought I needed.

I’ve been poking at it more and this is almost what I wanted:

{
  "columns": [
    {
      "text": "Avg",
      "value": "avg"
    }
  ],
  "datasource": "Fastnetmon",
  "fontSize": "100%",
  "gridPos": {
    "h": 9,
    "w": 12,
    "x": 0,
    "y": 0
  },
  "hideTimeOverride": false,
  "id": 4,
  "links": [],
  "pageSize": 10,
  "scroll": true,
  "showHeader": true,
  "sort": {
    "col": 1,
    "desc": true
  },
  "styles": [
    {
      "alias": "Time",
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "pattern": "Time",
      "type": "date"
    },
    {
      "alias": "Data in",
      "colorMode": null,
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "link": false,
      "pattern": "Avg",
      "thresholds": [],
      "type": "number",
      "unit": "decbits"
    },
    {
      "alias": "Host",
      "colorMode": null,
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "dateFormat": "YYYY-MM-DD HH:mm:ss",
      "decimals": 2,
      "pattern": "Metric",
      "thresholds": [],
      "type": "string",
      "unit": "short"
    },
    {
      "alias": "",
      "colorMode": null,
      "colors": [
        "rgba(245, 54, 54, 0.9)",
        "rgba(237, 129, 40, 0.89)",
        "rgba(50, 172, 45, 0.97)"
      ],
      "decimals": 2,
      "pattern": "/.*/",
      "thresholds": [],
      "type": "number",
      "unit": "short"
    }
  ],
  "targets": [
    {
      "alias": "[[tag_host]]",
      "groupBy": [
        {
          "params": [
            "host"
          ],
          "type": "tag"
        }
      ],
      "hide": false,
      "limit": "",
      "measurement": "hosts_traffic",
      "orderByTime": "ASC",
      "policy": "default",
      "query": "SELECT \"bits_incoming\" FROM \"hosts_traffic\" WHERE $timeFilter GROUP BY \"host\" LIMIT 10",
      "rawQuery": false,
      "refId": "A",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "bits_incoming"
            ],
            "type": "field"
          },
          {
            "params": [
              "10"
            ],
            "type": "top"
          }
        ]
      ],
      "tags": []
    }
  ],
  "timeFrom": "5m",
  "timeShift": null,
  "title": "Top 10 data in",
  "transform": "timeseries_aggregations",
  "type": "table"
}

Unfortunately no matter what I try it seems hell bent on giving me every host in the list that had any activity in the defined timespan – which could be hundreds or potentially thousands.

Aside from the fact that I’m only interested in the top ten, having that many entries auto-refreshing every five seconds with the other panels would likely kill my browser.

7

Sounds like a complicated query. You want to group by host (which is one query) and then limit that to the top 10 which is another query. If you are using InfluxDB 1.2 or higher you can use subqueries:

If that is not what you are looking for - might be worth asking on the InfluxDB community site (as this is not a Grafana problem).