Display State as percentage

Grafana
1

Hi!
Could anyone point me in the right direction to writing a query that turns state changes (On/off) into an average of % on over time. This is definitely the deep end for me, so if I am missing something big, or this is a dumb question please let me know where I lack understanding.

Background: I’m an intern developing a front end display for CNC machine metrics, using hassio and grafana. We store metrics on a postgreSQL server within the business. Hassio outputs state “on” and “off”. Below is the basic schema of the Database I am using.

CREATE TABLE events (
event_id INTEGER NOT NULL,
event_type VARCHAR(32),
event_data TEXT,
origin VARCHAR(32),
time_fired DATETIME,
created DATETIME,
PRIMARY KEY (event_id)
)
CREATE INDEX ix_events_event_type ON events (event_type)
CREATE TABLE recorder_runs (
run_id INTEGER NOT NULL,
start DATETIME,
“end” DATETIME,
closed_incorrect BOOLEAN,
created DATETIME,
PRIMARY KEY (run_id),
CHECK (closed_incorrect IN (0, 1))
)
CREATE TABLE states (
state_id INTEGER NOT NULL,
domain VARCHAR(64),
entity_id VARCHAR(64),
state VARCHAR(255),
attributes TEXT,
event_id INTEGER,
last_changed DATETIME,
last_updated DATETIME,
created DATETIME,
PRIMARY KEY (state_id),
FOREIGN KEY(event_id) REFERENCES events (event_id)
)

2

If you mean something along the lines of “availibility over X amount of time”:

You can use the “functions” like average(1h) on the metric you want to display. also use the function scale(100) so it displays as a percentage. Now You can use the Singlestat Panel to display the value in a small panel on your dashboard.

1 Like
3

Sounds like it’s along the lines of what I need, although where would that go in here? AFAIK at the moment this uses the count of state “off” within the states column of the “state” table. I know this is more of a graphite issue, but if someone could point me in the direction of a basic graphite tutorial that would be great

SELECT
__timeGroupAlias(last_changed,__interval),
count(state)
FROM states
WHERE
$__timeFilter(last_changed) AND
state = ‘off’
GROUP BY 1
ORDER BY 1