Table Panel: Filter and show data

Hi all,

I’m super new to Grafana but already love it. :slight_smile: So please bear with me.

I have connected Grafana to an Influx DB. One measurement contains a JSON string which data content I want to show in a table. Therefore I use Extract Fields under Transform and then organize the fields. Works quite well.

(Cannot post images here, so I need to describe the structure of the table)

Columns are: Intensity (number), Start, End (both timestamps), Event (text), and Description (text)

Sometimes I have entries where start and end differs, but description is identical. Is there any way to then only show the latest entry? But sometimes I have full duplicates too. Is there any other way to remove duplicates?

Other question:
In another measurement I save how many entries are currently active. e.g. 4. Is it possible to re-use this information to limit the number of entries being queried to be shown in the table? I tried to somehow pull this information into the LIMIT N part, but without success.

Many thanks for helping a beginner :slight_smile:

Johannes

Welcome to the forum. You have some good questions, and I think what you describe is possible, but (in my opinion) it will involve some work on your Influx queries. For Influx, are you using InfluxQL or Flux (these are the two query languages – Grafana supports both). Second, can you post the text of your queries and some sample output?

Thanks! Let me provide some more details:

  • I’m using InfluxQL. btw: for a beginner, but not a computer noob, what do you recommend? Should I switch to Flux right from the start?

  • Query: SELECT "value" FROM "dwd.0.warnings_all" WHERE time >= now() - 24h and time <= now()

  • Output: Screenshot, 2022-02-26 11:52:11 - Paste.Pics

Full panel JSON:

{
  "id": 44,
  "gridPos": {
    "h": 13,
    "w": 24,
    "x": 0,
    "y": 17
  },
  "type": "table",
  "title": "DWD JSON",
  "transformations": [
    {
      "id": "extractFields",
      "options": {
        "format": "auto",
        "replace": true,
        "source": "dwd.0.warnings_all"
      }
    },
    {
      "id": "organize",
      "options": {
        "excludeByName": {
          "altitudeEnd": true,
          "altitudeStart": true,
          "description": false,
          "headline": true,
          "instruction": true,
          "regionName": true,
          "state": true,
          "stateShort": true,
          "type": true
        },
        "indexByName": {
          "altitudeEnd": 12,
          "altitudeStart": 11,
          "description": 5,
          "end": 2,
          "event": 3,
          "headline": 4,
          "instruction": 9,
          "level": 0,
          "regionName": 6,
          "start": 1,
          "state": 7,
          "stateShort": 10,
          "type": 8
        },
        "renameByName": {
          "description": "Beschreibung",
          "end": "Ende",
          "event": "Ereignis",
          "headline": "Details",
          "instruction": "",
          "level": "Schwere",
          "start": "Beginn"
        }
      }
    },
    {
      "id": "convertFieldType",
      "options": {
        "conversions": [
          {
            "destinationType": "time",
            "targetField": "start"
          },
          {
            "dateFormat": "",
            "destinationType": "time",
            "targetField": "end"
          }
        ],
        "fields": {}
      }
    },
    {
      "id": "sortBy",
      "options": {
        "fields": {},
        "sort": [
          {
            "desc": true,
            "field": "Beginn"
          }
        ]
      }
    },
    {
      "id": "filterByValue",
      "options": {
        "filters": [
          {
            "config": {
              "id": "isNull",
              "options": {}
            },
            "fieldName": "Schwere"
          }
        ],
        "match": "any",
        "type": "exclude"
      }
    }
  ],
  "pluginVersion": "8.4.2",
  "fieldConfig": {
    "defaults": {
      "custom": {
        "align": "left",
        "displayMode": "auto",
        "filterable": false
      },
      "mappings": [
        {
          "options": {
            "3": {
              "color": "orange",
              "index": 0,
              "text": "Erhöht"
            }
          },
          "type": "value"
        },
        {
          "options": {
            "from": 0,
            "result": {
              "color": "green",
              "index": 1,
              "text": "Niedrig"
            },
            "to": 2
          },
          "type": "range"
        },
        {
          "options": {
            "from": 4,
            "result": {
              "color": "red",
              "index": 2,
              "text": "Hoch"
            },
            "to": 4
          },
          "type": "range"
        },
        {
          "options": {
            "from": 5,
            "result": {
              "color": "dark-purple",
              "index": 3,
              "text": "Gefahr"
            },
            "to": 20
          },
          "type": "range"
        }
      ],
      "thresholds": {
        "mode": "absolute",
        "steps": [
          {
            "color": "green",
            "value": null
          }
        ]
      },
      "color": {
        "mode": "thresholds"
      }
    },
    "overrides": [
      {
        "matcher": {
          "id": "byName",
          "options": "Beginn"
        },
        "properties": [
          {
            "id": "unit",
            "value": "time: DD.MM.YYYY HH:mm"
          }
        ]
      },
      {
        "matcher": {
          "id": "byName",
          "options": "Ende"
        },
        "properties": [
          {
            "id": "unit",
            "value": "time: DD.MM.YYYY HH:mm"
          }
        ]
      },
      {
        "matcher": {
          "id": "byName",
          "options": "Schwere"
        },
        "properties": [
          {
            "id": "custom.displayMode",
            "value": "color-background-solid"
          },
          {
            "id": "custom.width",
            "value": 100
          },
          {
            "id": "custom.align",
            "value": "center"
          }
        ]
      },
      {
        "matcher": {
          "id": "byName",
          "options": "Ereignis"
        },
        "properties": [
          {
            "id": "custom.width",
            "value": 219
          }
        ]
      },
      {
        "matcher": {
          "id": "byType",
          "options": "time"
        },
        "properties": [
          {
            "id": "custom.width",
            "value": 160
          }
        ]
      }
    ]
  },
  "options": {
    "showHeader": true,
    "footer": {
      "show": false,
      "reducer": [
        "sum"
      ],
      "fields": ""
    },
    "sortBy": [
      {
        "desc": true,
        "displayName": "Ende"
      }
    ]
  },
  "targets": [
    {
      "datasource": {
        "type": "influxdb",
        "uid": "UJIzjoJ7z"
      },
      "groupBy": [],
      "hide": false,
      "measurement": "dwd.0.warnings_all",
      "orderByTime": "ASC",
      "policy": "default",
      "refId": "A",
      "resultFormat": "time_series",
      "select": [
        [
          {
            "params": [
              "value"
            ],
            "type": "field"
          }
        ]
      ],
      "tags": []
    }
  ],
  "datasource": null
}

Thanks for your reply and additional info. Re: Flux, I think it would be worth going that route because it’s more “future proof” and you can do more complex things. Beware that you will likely have to rely on the help from the Influx Community Forum, which is great but not as active as the Grafana forum (where InfluxQL questions come up more regularly). This is changing, however, and if you are still at the very beginning (i.e. you do not have a database with thousands or millions of rows), then it might be worth installing Influx2.1 and using Flux. I (or others) can help you get started with that if you need help.

Re: your first question, just to be clear, if the columns Schwere, Ereignis and Beschreibung were all identical, then you want that row to NOT be displayed, correct? Similarly, if ALL 5 fields were identical, you want that row to NOT be displayed, correct?

Hi @grant2,

Good to know I will definetly look into Flux, as I’m still on a very early stage with Grafana. Building a nice screen for a home tablet right now, but at a very early stage there too. :slight_smile: If I saw it correctly, I can create another data source connection in Grafanatu my Influx DB and then select Flux there as query language. So I can try it out a bit and transition.

First of all, sorry, that the screenshot is in German… thats my native language. :wink: Just to explain it. The data is in one json string which I then transform to the columns. This is the raw data: Screenshot, 2022-02-26 20:51:59 - Paste.Pics (Any idea why I cannot upload images here? :frowning: )

Yes, and your understanding regarding what should be shown is correct. If it would be easier, it would be fine to just ensure that the row based on description is not shown several times Fyi: Those are local weather warnings.

Another question: Any way to filter also based on whether the end date has passed? Does not make sense to show the row if the message is no longer valid.

So I do not mean to keep answering your questions with more questions, but it might save you a lot of time if we get a better understanding of your setup.

What is populating the data into Influx? Are these weather warnings coming from a public API? Do you want / need to save these into Influx? The reason I ask is that Grafana can query a public API (such as weather warnings) and you can skip the database altogether.

1 Like

Don’t worry, questioning things is more than welcome :slight_smile:

I just checked the DWD webpage for more details but at the first look I was not able to find any useful information. Moreover they seem to have discontinued the JSON access a while ago. :frowning:

However I get it via ioBroker and there is a plugin for this, so I asssume it must be accessible somehow. I would also like to keep it saving the information into Influx DB, for historic reasons.

OK. Have you seen this site? The link to the Grafana dashboard looks impressive and you could modify it to your needs. I also like their approach of using Node-RED to “grab” the data and save it to a database. That is simple to do (many people do it and can assist) and since you are controlling it, you can make sure no duplicates are imported, which makes your data “clean” and your facilitates your dashboard building.

Indeed it looks impressive :slight_smile: And I had already found it. However it shows kind of every other data, except the weather warnings. :frowning:

Maybe another approach. I could easly get the number of active warnings into Grafana via JSON access to ioBroker. Is it possible to use this number under LIMIT when pulling the warnings from the influx db?

Hi @johannes2813

So just for fun, I installed the Node that I referenced earlier and set up Node-RED, just to see what I got. The node offers a lot of choices, so I just picked some random choices (e.g. EW-Hamburg). See the output below (right hand pane):

The “elements” that one can add to the output are huge:

Are these the weather warnings that you are looking for? If not, where (on the web) are they? It would seem like Node-RED could get these for you, and then we can set up Node-RED to populate Influx.

To answer your last question, if you set the number of active warnings into Grafana (ideally as a variable), then you could use that value to limit the warnings visible in Grafana. But I feel that may be overcomplicating the situation. I think you can “throttle” the data going into Influx and then Grafana setup would be very straightforward.

Ohh, many thanks for your help here and you trying this out too. But those warnings are not what I’m looking for. I need to try to find out where the ioBroker adapter gets those from. The one’s I’m looking for are more like warnings which are published for e.g., severe weather like snow, storm etc.

Is there a URL where the JSON data containing the weather warnings is served? If yes, then you could try installing the JSON datasource plugin.

I’ll try to find out if there is a JSON stream.

In the meantime I would like to try out the usage of a variable as LIMIT. I tried to create a new variable, but I think my syntax is wrong. At least it gets never filled. The documentation did not really enlighten me either. :frowning:

Can you tell me what I need to put into the Query field to get the information from an Influx DB or from a value provided via the JSON API? The information is available in both.

EDIT:

I found the URL where the JSON file sits: https://www.dwd.de/DWD/warnungen/warnapp/json/warnings.json

@johannes2813 That is awesome. Thanks for finding it.

I put the URL into Node-RED and it grabs EVERYTHING from that JSON file. Do you want EVERY warning, or just those for certain regions? For example, here is a snippet showing Stadt Flensburg and Stadt Kiel:

Node-RED can retrieve ONLY the data you want and then send it to Influx for storage, and then Grafana can display it. I can work something up quickly to show you, but for now, are the yellow fields above the weather warnings that you are seeking?

1 Like

Ok, first of all: Many thanks for your help and enthusiasm :smiley:

And yes, the yellow information is what I want. Perfect it would be to get:
Type, Level, Description, end, start, Event, Headline and filter by regionName

Is there a way to get this information using the JSON Plugin? I tried it by using the link you provided, but did not get anything out of it. :frowning: This would save me also from having to set up a Node-RED

Great idea @johannes2813

Calling Mr. @marcusolsson !!!

Marcus: Can we use your JSON plug in to skip Node-RED & Influx and feed the data from this URL straight into Grafana? I tried but was not able to get the datasource working.

1 Like

Haha love this board :blush::blush:

I ran the data source through a parser and it brought up an error :grimacing: Also when I open it in Firefox, it reports an error.

I’m playing around with the query right now which pulls the data for the table:

I want to ensure that I do not pull any empty fields, however empty in this case means that the field contains {} only. So trying to exclude this. However when I go into the query explorer I still see that the rows with {} still gets pulled.

I also then wanted to limit the results by using the LIMIT field. I was able to create a variable which gets filled by another query. However as soon as I enter something in the LIMIT field, no more data gets pulled.

Looks like it’s failing because it’s not valid JSON data. It starts with warnWetter.loadWarnings({.... As long as it’s valid, it should work :slight_smile:

1 Like