Grafana - gap when no data in mysql

Question

I created mysql database with data about CPU, RAM, disk usage , etc… I created graph but I noticed that when there is no new data, the last value is displayed. That means that if I used 50% of CPU and than turn server down, grafana will still displaying 50% of usage.

This is my query in grafana:

SELECT
  UNIX_TIMESTAMP(date) as time_sec,
  cpu as value,
  'server' as metric
FROM server

This is how my query in my monitoring.sh looks like:

INSERT INTO database 
VALUES ('$datetime', $cpu_percentage, $mem, $swap, $load1, $load5, $load15, $disk)

With values example:

INSERT INTO database 
VALUES ('2018-03-25 14:05:41', 9.69, 56.65, 0.00, 1.52, 1.72, 1.82, 20)

I would like to have a gap when no new value appears, because e.g. the server is down.

Is there a way how to configure grafana to reach this state? Is that even possible?

I am using Grafana v5.0.3.

EDITED:

"lines": true,
      "linewidth": 1,
      "links": [],
      "nullPointMode": "null",
      "percentage": false,
      "pointradius": 0.5,
      "points": false,
      "renderer": "flot",
      "seriesOverrides": [],
      "spaceLength": 10,
      "stack": false,
      "steppedLine": false,
      "targets": [
        {
          "alias": "",
          "format": "time_series",
          "rawSql": "SELECT\n  UNIX_TIMESTAMP(date) as time_sec,\n  cpu as value,\n  'server' as metric\nFROM server\n\n",
          "refId": "A"
        },
        {
          "alias": "",
          "format": "time_series",
          "rawSql": "SELECT\n  UNIX_TIMESTAMP(date) as time_sec,\n  cpu as value,\n  'raspberrypi' as metric\nFROM raspberrypi\n\n",
          "refId": "B"
        },
        {
          "alias": "",
          "format": "time_series",
          "rawSql": "SELECT\n  UNIX_TIMESTAMP(date) as time_sec,\n  cpu as value,\n  'dellpc' as metric\nFROM dellpc\n\n",
          "refId": "C"
        }
      ],
      "thresholds": [
        {
          "colorMode": "critical",
          "fill": true,
          "line": true,
          "op": "gt",
          "value": 70
        }
      ],
      "timeFrom": null,
      "timeShift": null,
      "title": "CPU usage",
      "tooltip": {
        "shared": true,
        "sort": 0,
        "value_type": "individual"
      },
      "transparent": true,
      "type": "graph",
      "xaxis": {
        "buckets": null,
        "mode": "time",
        "name": null,
        "show": true,
        "values": []
      },
      "yaxes": [
        {
          "format": "percent",
          "label": "Usage",
          "logBase": 1,
          "max": null,
          "min": null,
          "show": true
        },
        {
          "format": "short",
          "label": null,
          "logBase": 1,
          "max": null,
          "min": null,
          "show": false
        }
      ]
    },
    {
      "aliasColors": {
        "dellpc": "#0a50a1",
        "raspberrypi": "#962d82",
        "test": "#fce2de"
      },
      "bars": false,
      "dashLength": 10,
      "dashes": false,
      "datasource": null,
      "decimals": 2,
      "fill": 1,
      "gridPos": {
        "h": 11,
        "w": 12,
        "x": 12,
        "y": 0
      },
      "id": 6,
      "legend": {
        "alignAsTable": true,
        "avg": true,
        "current": true,
        "hideEmpty": false,
        "hideZero": false,
        "max": true,
        "min": true,
        "show": true,
        "total": false,
        "values": true
      },

Did you try to use null value "connected" in the panel configuration? since you dont have null values, this would ignore the gap and connect the non-zero values. — Rebeca Maia, Mar 26 '18 at 19:14
The default in Grafana is to show a gap for just this situation which is why the default value of the Null Value field is null. Can you show a screenshot of your graph? Also, noticing that you have no time filter for your queries - that has nothing to do with your issue but means you are returning more data than you are viewing on your graph. — Daniel Lee, Apr 03 '18 at 08:45

score 3 · Answer 1 · edited Aug 28 '19 at 11:53

3

try the below query

select $__timegroup(date,'24h',0) as time, cpu as value,'server' as metric
FROM server
group by $__timegroup(date,'24h',0)

edited Aug 28 '19 at 11:53

Suraj Rao

29,388
11
94
103

answered Aug 28 '19 at 11:36

Varada Shashi Kumar

31
3

This query, added "Change: "nullPointMode": "null" to "nullPointMode": "null as zero"" should be the accepted answer. – Sylvain Oct 25 '20 at 05:03

score 0 · Answer 2 · answered Mar 26 '18 at 06:53

0

Change: "nullPointMode": "null" to "nullPointMode": "null as zero"

answered Mar 26 '18 at 06:53

IIIIIIIIIIIIIIIIIIIIII

3,958
5
45
70

2

It is not working because I have no null value in the database table. – dorinand Mar 26 '18 at 07:18
Have you tried [this](https://stackoverflow.com/questions/12449899/returning-a-value-even-if-no-result) answer? But instead of 'not found' return **'0'** ? Also check [this issue](https://github.com/grafana/grafana/issues/8655) which says your query should: >"query needs to group by time and return null/0 for missing data points" – IIIIIIIIIIIIIIIIIIIIII Mar 26 '18 at 07:52

Grafana - gap when no data in mysql

2 Answers2