InfluxDB+Grafana persistence and graphing

I´m also having trouble with openhab 3.x and a fresh grafana install via openhabian.

Frontail log is telling me

[ERROR] [org.influxdb.impl.BatchProcessor    ] - Batch could not be sent. Data will be lost

Grafana DB check is all green when saving.

I have the same Problems as @Rar9. It seems that influxdb and grafana-server dont start at boot.
After booting grafana isnt reachable via openhabianip:3000 until I run sudo systemctl start grafana-server.service

I also have the same error as @Rar9 until I start influxdb manually via sudo systemctl start influxdb.service

I also tried sudo systemctl enable grafana-server.service and sudo systemctl enable influxdb.service but after a reboot it seems neither influxdb nor grafana is startet.

This sounds more like a openHABian issue than a Grafana/InfluxDB issue.

I think you might be better off creating a new thread in this forum.

And maybe check the system logs to see if there are any hints about your issue.

Already did that here: Influxdb and grafana dont start at boot if anybody has the same problems. Still searching for a solution

Hello

I am already using grafana+influxdb to store & plot some data I receive in MQTT. However, I’m persisting data on the fly.
I would like to store data I am retrieving the previous day which is valid for the next day.

More details: I am doing HTTP request which returns price of energy for the next day. Hour by hour.
I would like to store this data in influxdb and show it in grafana.

Can you give me some hint on how to perform this?
Below is an example of the data:

[{"start_date":"2021-04-15T00:00:00+02:00","end_date":"2021-04-16T00:00:00+02:00","updated_date":"2021-04-14T13:07:28+02:00","values":[{"start_date":"2021-04-15T00:00:00+02:00","end_date":"2021-04-15T01:00:00+02:00","value":13963.5,"price":68.27},{"start_date":"2021-04-15T01:00:00+02:00","end_date":"2021-04-15T02:00:00+02:00","value":13738.5,"price":63.38},{"start_date":"2021-04-15T02:00:00+02:00","end_date":"2021-04-15T03:00:00+02:00","value":14532.8,"price":64.78},{"start_date":"2021-04-15T03:00:00+02:00","end_date":"2021-04-15T04:00:00+02:00","value":13673.5,"price":59.71},{"start_date":"2021-04-15T04:00:00+02:00","end_date":"2021-04-15T05:00:00+02:00","value":12933.3,"price":61.16},{"start_date":"2021-04-15T05:00:00+02:00","end_date":"2021-04-15T06:00:00+02:00","value":13123.2,"price":65.81},{"start_date":"2021-04-15T06:00:00+02:00","end_date":"2021-04-15T07:00:00+02:00","value":12878.7,"price":82.9},{"start_date":"2021-04-15T07:00:00+02:00","end_date":"2021-04-15T08:00:00+02:00","value":13515.2,"price":105.05},{"start_date":"2021-04-15T08:00:00+02:00","end_date":"2021-04-15T09:00:00+02:00","value":13679.3,"price":118},{"start_date":"2021-04-15T09:00:00+02:00","end_date":"2021-04-15T10:00:00+02:00","value":16229.8,"price":99.8},{"start_date":"2021-04-15T10:00:00+02:00","end_date":"2021-04-15T11:00:00+02:00","value":18072.6,"price":82},{"start_date":"2021-04-15T11:00:00+02:00","end_date":"2021-04-15T12:00:00+02:00","value":18687.3,"price":75.16},{"start_date":"2021-04-15T12:00:00+02:00","end_date":"2021-04-15T13:00:00+02:00","value":18954.9,"price":70.67},{"start_date":"2021-04-15T13:00:00+02:00","end_date":"2021-04-15T14:00:00+02:00","value":17284.3,"price":69},{"start_date":"2021-04-15T14:00:00+02:00","end_date":"2021-04-15T15:00:00+02:00","value":16575.1,"price":63.72},{"start_date":"2021-04-15T15:00:00+02:00","end_date":"2021-04-15T16:00:00+02:00","value":15835.6,"price":60.55},{"start_date":"2021-04-15T16:00:00+02:00","end_date":"2021-04-15T17:00:00+02:00","value":15132.9,"price":57.77},{"start_date":"2021-04-15T17:00:00+02:00","end_date":"2021-04-15T18:00:00+02:00","value":14813.8,"price":61.18},{"start_date":"2021-04-15T18:00:00+02:00","end_date":"2021-04-15T19:00:00+02:00","value":14891.6,"price":70.14},{"start_date":"2021-04-15T19:00:00+02:00","end_date":"2021-04-15T20:00:00+02:00","value":13748.8,"price":82.89},{"start_date":"2021-04-15T20:00:00+02:00","end_date":"2021-04-15T21:00:00+02:00","value":13506.9,"price":82.9},{"start_date":"2021-04-15T21:00:00+02:00","end_date":"2021-04-15T22:00:00+02:00","value":14100.4,"price":78},{"start_date":"2021-04-15T22:00:00+02:00","end_date":"2021-04-15T23:00:00+02:00","value":14465.2,"price":72.25},{"start_date":"2021-04-15T23:00:00+02:00","end_date":"2021-04-16T00:00:00+02:00","value":15946.5,"price":68.58}]}]

What is stopping you? You can’t store data for the future in persistence. But it’s just data, you know if you look at “yesterday’s” data it is about tomorrow.

Previous dscussion -

1 Like

What panel did you use to have a graph behind the gauge?
I’am looking for this option, but cant find it.

I couldn’t remember what its called but on looking at my setup I get this:

image

If I migrate the graph disappears. So, I kept the depreciated panel as it still works.

I think you are looking for “Show Spark Lines”.

1 Like

I don’t have the option for ‘Spark lines’.
Could it be that this doesn’t work in 7.5 anymore?

I am sorry!

I thought I already migrated to the new Gause panel. :grinning_face_with_smiling_eyes:

But it looks like I did not:

image

1 Like

I’m still using OpenHAB 2.5.12 with the dynamic ‘Webview’ Element with JavaScript library (openhab-grafana) by @wborn.
Today I tried upgrading Grafana from 7.5.7 to 8.0.1, but had to note, that this breaks the setup and I only see a 404 graphic instead of the expected diagrams.

Downgrading to 7.5.7 works around this issue.

Does anybody get openhab-grafana working with Grafana 8? What do I have to change or modify to get this running?

Greetings
Roland

1 Like

Hello Roland,
I read some grafana documentation and saw something.

During the upgrade the grafana config has changed maybe?
Check this Configuration | Grafana Labs
After my upgrade from 7.5.3. to 8.0.1 it is “allow_embedding = false” … BUT I don’t know how it was before.

Cheers,
Patrick

1 Like

Thank you for the hint, but /etc/grafana/garafana.ini is unchanged on my system (since 2019) and contains the line “allow_embedding=true”.

It seems, that something goes wrong when accessing dashboard-solo/db/, which does not longer redirect to d-solo// but returns a 404, but it seems, that only starring on the apache proxy logs doesn’t help, since I don’t understand, what happens in openhab-grafana.js…

I encountered the same problem after upgrading to Grafana 8.x. This seems to be caused by a deprecated API.

The 8.0.0 release notes (https://grafana.com/docs/grafana/latest/release-notes/release-notes-8-0-0/)state, that the /dashboard-solo/db/ API was deprecated in 5.0.0 and is removed with 8.0.0

As a workaround you can embed the panel directly, but you will loose the functionality of the library by @wborn, which I like very much :

 Webview url="http://openhab.mydomain.internal:9080/grafana/d/000000012/technikraum?from=now-48h&to=now&orgId=1&viewPanel=4&kiosk=1&theme=light" height=15

I am not sure, if the library could generate correctly links for the new API, when providing the details as parameter. Somehow odd is, that the dashboard is specified twice in the link, once by the uuid (000000012) and once by its name (technikraum). Probably this can not mapped to parameters of the current library.

I managed to get it working with Grafana 8, with only small changes to the library from @wborn

Here is, what I changed in the library (the version from the /web-src/ directory of the Github repository):

Changed the paths in function OHG_DEFAULTS():

...
panelPath: "/d/"
renderPanelPath: "/d/"
...

Renamed the ‘panelId’ in function GrafanaPanel()

...
key: "viewPanel"
...

Added the kiosk mode to the URL in function updateFrameSourceURL()
After the loop for (var uvKey in urlVars):

url += "&" + "kiosk=1";

As reference I added my version of the library below

In the sitemap only the dashboard parameter has to be changed, either to only the uid or to uid/name of the dashboard, e.g.:

Webview url="/static/grafana/grafana.html?dashboard=000000009&from=now-48h&to=now&panel=4" height=12

You can find the uid e.g. when viewing the dashboard as json in the export menu.

As I know nothing about Javascript or Html, this probably is not the best way to change the library and it will most probably not work in all use cases, but my wife is happy again :wink: Maybe it is a starting point for someone who actually knows what he does.

Here the full library. I also defined the urlPrefix, according to my installation, in the library, probably this is not needed when correctly defined in the user configuration file :slight_smile:

/**
 * openHAB Grafana panel utilities
 *
 * Updates the source URL of Grafana <iframe> panels using openHAB server side events (SSE).
 * OHSubscriber is based on the javascript of the openHAB Basic UI by Vlad Ivanov.
 *
 * @author Wouter Born
 */

/* exported addGrafanaPanel, GrafanaPanel */
/* eslint-env browser */
/* eslint no-undef:2 */
/* eslint no-new:0 */
/* eslint no-underscore-dangle:0 */

"use strict";

var
    OHG_DEFAULTS = {
        // library
        debug: "false",
        render: "false",
        refresh: "0",
        // OH sitemap
        sitemap: "default",
        // Grafana URL
        urlPrefix: "http://server.domain.internal:9080/grafana",
        panelPath: "/d/",
        renderPanelPath: "/d/",
        // Grafana panel parameters
        from: "now-1d",
        to: "now",
        theme: "light",
        // Grafana render panel parameters
        width: "auto",
        height: "auto"
    };

function queryParams(param) {
    if (!param) {
        return {};
    }

    var
        match,
        url = param.toString(),
        queryIndex = url.indexOf("?"),
        query = queryIndex !== -1 ? url.substring(queryIndex + 1) : "",
        re = /([^&=]+)=?([^&]*)/g,
        decode = function (s) { return decodeURIComponent(s.replace(/\+/g, " ")); },
        result = {};

    do {
        match = re.exec(query);
        if (match) {
           result[decode(match[1])] = decode(match[2]);
        }
    } while (match);

    return result;
}

var
    urlParams = queryParams(window.location),
    parentUrlParams = queryParams(window.parent.location.href);

function resolveParam(params, name) {
    if (params !== undefined && params[name] !== undefined) {
        return params[name];
    } else if (urlParams !== undefined && urlParams[name] !== undefined) {
        return urlParams[name];
    } else if (parentUrlParams !== undefined && parentUrlParams[name] !== undefined) {
        return parentUrlParams[name];
    } else {
        return OHG_DEFAULTS[name];
    }
}

function OHSubscriber(params) {
    var
        p = params,
        initialized = false,
        initializedListeners = [],
        items = {},
        subscription = {
            id: "",
            page: resolveParam(p, "w"),
            sitemap: resolveParam(p, "sitemap")
        };

    this.addItemListener = function(itemName, listener) {
        if (typeof listener !== "function") {
            throw new Error("addItemListener 'listener' is not a function");
        }
        if (items[itemName] !== undefined) {
            if (!items[itemName].listeners.includes(listener)) {
                items[itemName].listeners.push(listener);
            }
        } else {
            items[itemName] = {listeners: [listener], value: undefined};
        }
    };

    this.addInitializedListener = function(listener) {
        if (typeof listener !== "function") {
            throw new Error("addInitializedListener 'listener' is not a function");
        }
        initializedListeners.push(listener);
    };

    this.isInitialized = function() {
        return initialized;
    };

    function ajax(params) {
        var
            p = params,
            type = typeof p.type !== "undefined" ? p.type : "GET",
            data = typeof p.data !== "undefined" ? p.data : "",
            headers = typeof p.headers !== "undefined" ? p.headers : {},
            request = new XMLHttpRequest();

        request.open(type, p.url, true);

        for (var h in headers) {
            request.setRequestHeader(h, headers[h]);
        }

        request.onload = function() {
            if (request.status < 200 || request.status > 400) {
                if (typeof p.error === "function") {
                    p.error(request);
                }
                return;
            }
            if (typeof p.callback === "function") {
                p.callback(request);
            }
        };
        request.onerror = function() {
            if (typeof p.error === "function") {
                p.error(request);
            }
        };
        request.send(data);

        return request;
    }

    function uninitializedItemNames() {
        var result = [];
        for (var itemName in items) {
            if (items[itemName].value === undefined) {
                result.push(itemName);
            }
        }
        return result;
    }

    function areAllItemsInitialized() {
        return uninitializedItemNames().length === 0;
    }

    function updateInitialized() {
        if (!initialized && areAllItemsInitialized()) {
            initialized = true;
            for (var i = 0; i < initializedListeners.length; i++) {
                initializedListeners[i]();
            }
        }
    }

    function updateItem(itemName, value) {
        if (items[itemName].value === value) {
            return;
        }

        items[itemName].value = value;

        var itemListeners = items[itemName].listeners;
        for (var i = 0; i < itemListeners.length; i++) {
            var listener = itemListeners[i];
            listener(itemName, value);
        }

        updateInitialized();
    }

    function ChangeListenerEventsource(subscribeLocation) {
        var
            _t = this;

        _t.navigate = function(){};
        _t.source = new EventSource(subscribeLocation);
        _t.source.addEventListener("event", function(payload) {
            if (_t.paused) {
                return;
            }

            var
                data = JSON.parse(payload.data),
                itemName = data.item.name,
                value;

            if (items[itemName] === undefined) {
                return;
            }

            if (
                (typeof(data.label) === "string") &&
                (data.label.indexOf("[") !== -1) &&
                (data.label.indexOf("]") !== -1)
            ) {
                var
                    pos = data.label.indexOf("[");

                value = data.label.substr(
                    pos + 1,
                    data.label.lastIndexOf("]") - (pos + 1)
                );
            } else {
                value = data.item.state;
            }

            updateItem(itemName, value);
        });
    }

    function ChangeListenerLongpolling() {
        var
            _t = this;

        function applyChanges(response) {
            try {
                response = JSON.parse(response);
            } catch (e) {
                return;
            }

            function walkWidgets(widgets) {
                widgets.forEach(function(widget) {
                    if (widget.item === undefined) {
                        return;
                    }

                    var
                        itemName = widget.item.name,
                        value = widget.item.state;

                    if (items[itemName] !== undefined) {
                        updateItem(itemName, value);
                    }
                });
            }

            if (response.leaf) {
                walkWidgets(response.widgets);
            } else {
                response.widgets.forEach(function(frameWidget) {
                    walkWidgets(frameWidget.widgets);
                });
            }
        }

        function start() {
            var
                cacheSupression = Math.random().toString(16).slice(2);

            _t.request = ajax({
                url: "/rest/sitemaps/" + subscription.sitemap + "/" + subscription.page + "?_=" + cacheSupression,
                headers: {"X-Atmosphere-Transport": "long-polling"},
                callback: function(request) {
                    applyChanges(request.responseText);
                    setTimeout(function() {
                        start();
                    }, 1);
                },
                error: function() {
                    // Wait 1s and restart long-polling
                    setTimeout(function() {
                        start();
                    }, 1000);
                }
            });
        }

        start();
    }

    function ChangeListener() {
        var
            _t = this;

        _t.startSubscriber = function(response) {
            var
                responseJSON,
                subscribeLocation,
                subscribeLocationArray;

            try {
                responseJSON = JSON.parse(response.responseText);
            } catch (e) {
                return;
            }

            if (responseJSON.status !== "CREATED") {
                return;
            }

            try {
                subscribeLocation = responseJSON.context.headers.Location[0];
            } catch (e) {
                return;
            }

            subscribeLocationArray = subscribeLocation.split("/");
            subscription.id = subscribeLocationArray[subscribeLocationArray.length - 1];

            if ("EventSource" in window) {
                ChangeListenerEventsource.call(_t, subscribeLocation +
                    "?sitemap=" + subscription.sitemap +
                    "&pageid=" + subscription.page);
            } else {
                ChangeListenerLongpolling.call(_t);
            }
        };

        ajax({
            url: "/rest/sitemaps/events/subscribe",
            type: "POST",
            callback: _t.startSubscriber
        });
    }

    function ValuesInitializer() {
        var
            _t = this;

        _t.valueHandler = function(response) {
            var
                responseJSON;

            try {
                responseJSON = JSON.parse(response.responseText);
            } catch (e) {
                return;
            }

            updateItem(responseJSON.name, responseJSON.state);
        };

        var itemNames = uninitializedItemNames();
        for (var i = 0; i < itemNames.length; i++) {
            ajax({
                url: "/rest/items/" + itemNames[i],
                type: "GET",
                callback: _t.valueHandler
            });
        }

        updateInitialized();
    }

    function initialize() {
        if (subscription.page === undefined) {
            subscription.page = subscription.sitemap;
        }

        document.addEventListener("DOMContentLoaded", function() {
            subscription.valuesInitializer = new ValuesInitializer();
            subscription.changeListener = new ChangeListener();
        });
    }

    initialize();
}

var ohSubscriber = new OHSubscriber();

function GrafanaPanel(params) {
    var
        p = params,
        refreshTimerId = undefined,
        resizeTimerId = undefined,
        frame = resolveParam(p, "frame"),
        urlPrefix = resolveParam(p, "urlPrefix"),
        panelPath = resolveParam(p, "panelPath"),
        renderPanelPath = resolveParam(p, "renderPanelPath"),
        libVars = {
            debug: {
                value: resolveParam(p, "debug"),
                itemName: resolveParam(p, "debugItem"),
                itemFunction: params.debugItemFunction
            },
            render: {
                value: resolveParam(p, "render"),
                itemName: resolveParam(p, "renderItem"),
                itemFunction: params.renderItemFunction
            },
            refresh: {
                value: resolveParam(p, "refresh"),
                itemName: resolveParam(p, "refreshItem"),
                itemFunction: params.refreshItemFunction
            }
        },
        urlVars = {
            dashboard: {
                value: resolveParam(p, "dashboard"),
                itemName: resolveParam(p, "dashboardItem"),
                itemFunction: params.dashboardItemFunction
            },
            from: {
                key: "from",
                value: resolveParam(p, "from"),
                itemName: resolveParam(p, "fromItem"),
                itemFunction: params.fromItemFunction
            },
            to: {
                key: "to",
                value: resolveParam(p, "to"),
                itemName: resolveParam(p, "toItem"),
                itemFunction: params.toItemFunction
            },
            panel: {
                key: "viewPanel",
                value: resolveParam(p, "panel"),
                itemName: resolveParam(p, "panelItem"),
                itemFunction: params.panelItemFunction
            },
            theme: {
                key: "theme",
                value: resolveParam(p, "theme"),
                itemName: resolveParam(p, "themeItem"),
                itemFunction: params.themeItemFunction
            },
            width: {
                key: "width",
                value: resolveParam(p, "width"),
                itemName: resolveParam(p, "widthItem"),
                itemFunction: params.widthItemFunction
            },
            height: {
                key: "height",
                value: resolveParam(p, "height"),
                itemName: resolveParam(p, "heightItem"),
                itemFunction: params.heightItemFunction
            }
        };

    function updateFrameSourceURL() {
        var debug = libVars.debug.value;
        var render = libVars.render.value;
        var refresh = libVars.refresh.value;

        var iframe = document.getElementById(frame);
        var idocument = iframe.contentWindow.document;

        var url = urlPrefix;
        url += render === "true" ? renderPanelPath : panelPath;
        url += urlVars.dashboard.value;

        var firstParameter = true;
        for (var uvKey in urlVars) {
            var key = urlVars[uvKey].key;
            var value = urlVars[uvKey].value;

            if (key === "width") {
                value = render === "false" ? undefined : (value === "auto" ? idocument.body.clientWidth : value);
            } else if (key === "height") {
                value = render === "false" ? undefined : (value === "auto" ? idocument.body.clientHeight : value);
            }

            if (key !== undefined && value !== undefined) {
                url += (firstParameter ? "?" : "&") + key + "=" + value;
                firstParameter = false;
            }
        }
        url += "&" + "kiosk=1";


        if (render === "true") {
            // append cache busting parameter
            url += "&cacheBuster=" + Date.now();
        }
        // update frame content
        if (debug === "true") {
            idocument.open();
            idocument.write("<a href=\"" + url + "\">" + url + "</a>");
            idocument.close();
        } else if (render === "true") {
            var htmlUrl = url.replace(renderPanelPath, panelPath);
            idocument.open();
            idocument.write("<style>body{margin:0px}p{margin:0px}</style>");
            idocument.write("<p style=\"text-align:center;\"><a href=\"" + htmlUrl + "\"><img src=\"" + url + "\"></a></p>");
            idocument.close();
        } else if (document.getElementById(frame).src !== url) {
            // replace the URL so changes are not added to the browser history
            iframe.contentWindow.location.replace(url);
        }

        // schedule/cancel rendered image refresh
        if (render === "true" && refresh > 0) {
            clearTimeout(refreshTimerId);
            refreshTimerId = setTimeout(updateFrameSourceURL, refresh);
        } else if (refreshTimerId !== undefined) {
            clearTimeout(refreshTimerId);
        }
    }

    function updateFrameOnResize() {
        if (libVars.render.value === "true" && (urlVars.width.value === "auto" || urlVars.height.value === "auto")) {
            clearTimeout(resizeTimerId);
            resizeTimerId = setTimeout(updateFrameSourceURL, 500);
        } else {
            clearTimeout(resizeTimerId);
        }
    }

    function updateVarsOnItemUpdate(vars, itemName, value) {
        for (var key in vars) {
            if (vars[key].itemName !== undefined && vars[key].itemName === itemName) {
                if (vars[key].itemFunction) {
                    value = vars[key].itemFunction(value);
                }
                vars[key].value = value;
            }
        }
    }

    function onItemUpdated(itemName, value) {
        updateVarsOnItemUpdate(libVars, itemName, value);
        updateVarsOnItemUpdate(urlVars, itemName, value);

        if (ohSubscriber.isInitialized()) {
            updateFrameSourceURL();
        }
    }

    function assertPropertyDefined(name, value) {
        if (value === undefined) {
            throw new Error("Property '" + name + "' is undefined");
        }
    }

    function assertVarsDefinedOrSubscribeToOH(vars) {
        for (var key in vars) {
            var itemName = vars[key].itemName;
            if (itemName !== undefined) {
                ohSubscriber.addItemListener(itemName, onItemUpdated);
            } else if (vars[key].value === undefined) {
                throw new Error("Property '" + key + "' requires a default value or itemName to obtain the value from");
            }
        }
    }

    function initialize() {
        assertPropertyDefined("frame", frame);
        assertPropertyDefined("urlPrefix", urlPrefix);
        assertPropertyDefined("panelPath", panelPath);
        assertPropertyDefined("renderPanelPath", renderPanelPath);

        assertVarsDefinedOrSubscribeToOH(libVars);
        assertVarsDefinedOrSubscribeToOH(urlVars);

        ohSubscriber.addInitializedListener(updateFrameSourceURL);
        window.addEventListener("resize", updateFrameOnResize);
    }

    initialize();
}

var grafanaPanels = [];

function createGuid()
{
    return "xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx".replace(/[xy]/g, function(c) {
        var r = Math.random()*16|0, v = c === "x" ? r : (r&0x3|0x8);
        return v.toString(16);
    });
}

function addGrafanaPanel(uniqueId, params) {
    if (uniqueId === undefined) {
        uniqueId = createGuid();
    }

    var div = document.createElement("div");
    div.id = "panel-" + uniqueId + "-container";
    div.className = "panel-container";
    document.body.appendChild(div);

    var frame = document.createElement("iframe");
    frame.id = "panel-" + uniqueId + "-frame";
    frame.className = "panel-frame";
    frame.scrolling = "no";
    div.appendChild(frame);

    if (params === undefined) {
        params = {};
    }

    params.frame = frame.id;

    var panel = new GrafanaPanel(params);
    grafanaPanels.push(panel);
}
2 Likes

would you create a Pull Request for this @ GitHub - wborn/openhab-grafana: JavaScript utilities and examples for using Grafana with openHAB
as i am also no JavaScript guru, but i would prefer to hopefully get it into the code base for a new release

I thought about that, but I would prefer to @wborn opinion first. As I wrote, that probably ist not the best way to fix things… I will look into it, but this could take a few days.

ok fine with that.
I just asked, as for me the pull request would be a good place to discuss your proposed changes, as you have a good diff and commenting functionality @GitHub

I created this pull request: https://github.com/wborn/openhab-grafana/pull/28

I hope, this is OK, I am not familiar with the Github workflow.