Display events in BasicUI

[Dries]

Hi,

For some of my items, I would like to capture and display past events in BasicUI. E.g. I would like to see when someone rang my front doorbell over the past two weeks. This is convenient when I’m on holiday or away for business.

I’ve already found a way to capture the last 10 entries.
BasicUI shows:

How I’ve done it:

When someone presses the frontdoor, I update a “history item”:

// ***************************
// Iemand drukt op de bel aan de voordeur
// ***************************

rule "Gira - Zijdeur - bel"

when
    Item  GiraDCS_Zijdeur changed from OFF to ON
then
	// doing a lot of stuff here
	postUpdate(GiraDCS_History_00, "Zijdeur - " + GiraDCS_Zijdeur.lastUpdate.toDateTime.toString("dd-MM-yy HH:mm"))
end

The rule below stores the last 10 events:

// ***************************
// Display the history of the last 10 events
// ***************************

rule "GiraDCS - History of last 10 events"

when   
    Item GiraDCS_History_00 changed
then
	postUpdate(GiraDCS_History_10, GiraDCS_History_09.state.toString)
	postUpdate(GiraDCS_History_09, GiraDCS_History_08.state.toString)
	postUpdate(GiraDCS_History_08, GiraDCS_History_07.state.toString)
	postUpdate(GiraDCS_History_07, GiraDCS_History_06.state.toString)
	postUpdate(GiraDCS_History_06, GiraDCS_History_05.state.toString)
	postUpdate(GiraDCS_History_05, GiraDCS_History_04.state.toString)
	postUpdate(GiraDCS_History_04, GiraDCS_History_03.state.toString)
	postUpdate(GiraDCS_History_03, GiraDCS_History_02.state.toString)
	postUpdate(GiraDCS_History_02, GiraDCS_History_01.state.toString)
	postUpdate(GiraDCS_History_01, GiraDCS_History_00.state.toString)
	Thread::sleep(100)
	GiraDCS_History_10.label = GiraDCS_History_10.state.toString
	GiraDCS_History_09.label = GiraDCS_History_09.state.toString	
	GiraDCS_History_08.label = GiraDCS_History_08.state.toString
	GiraDCS_History_07.label = GiraDCS_History_07.state.toString
	GiraDCS_History_06.label = GiraDCS_History_06.state.toString
	GiraDCS_History_05.label = GiraDCS_History_05.state.toString
	GiraDCS_History_04.label = GiraDCS_History_04.state.toString		
	GiraDCS_History_03.label = GiraDCS_History_03.state.toString		
	GiraDCS_History_02.label = GiraDCS_History_02.state.toString			
	GiraDCS_History_01.label = GiraDCS_History_01.state.toString
end

However, this approach has some disadvantages:

• I only capture the last 10 events. If I would like to keep track of the last x weeks, I would need a huge amount of items.
• If I would follow the same approach for other things I would like to keep track of (other events), that would again mean building rules like the above and a similar amount of items.
• This is a workaround at best. I think it is more healthy to leverage persistence;

For persistence I use influxdb, visualized by Grafana. However, I haven’t found a way to just display historical values in Grafana in a simple list (with timestamp). It seems Grafana is more for graphs.

Can anyone share some experiences?

Thanks!

[Udo_Hartmann]

I did not persist Text in Influxdb yet, but in Grafana, there is a type of row called Table which should fulfill your requirements. Of course you have to setup the correct range, as Grafana will only query data within the time range.

[Dries]

Thanks! I will take a look in to that. Each time item X changes, I will update a proxy-item with the item name, a timestamp and a status (in a human readable format). I’ll update this post with my findings.

[Dries]

Hi,
I wanted to share the solution I have put in place for my logging.

In short: I have installed MariaDB, and at certain events I generate a log in an SQL table. I visualize everything using the Grafana Table Panel.

The longer story:

I installed MariaDB. I’ve chosen MariaDB since its an opensource-fork of MySQL. MYSQL offered the flexibility I needed of creating own table structures and writing data to it with a simple command line interface.

I also installed PHPMyAdmin. This is not necessary, since I only used it to create my tables and consult table entries when I was troubleshooting. You can do all this using MYSQL commands.

I created my database and tables. Here is one example:

This table contains a log of all my doors and their status (open, closed) with timestamp.
As you can see, I’ve put both the ID (e.g. Status_ID) and the description (e.g. Status) in there. I’m aware that this generates some redundant data. This is because I wanted to show the status description in Grafana, and I’ve always learned to always store ID’s (since descriptions might change).

I’ve created one user (OpenHAB) that could write data to the tables, and another user (Grafana) that has read-access.

I didn’t install the MariaDB persistence service, nor did I install the Exec binding.

You can add records to your SQL table using the following command:

mysql -u OpenHAB -pOpenHABPW -e "INSERT INTO Alarm_Deuren (Timestamp, Status_ID, Status, Contact_ID, Contact) VALUES ('2017-11-07 07:34:13', '1', 'Open', 'Tex_Zone_Z016', 'Frontdoor');" OpenHAB

Now I needed to create a rule that would run that command when one of my doors would open.

// ***************************
// Generic Variables
// ***************************

var mysql_user = "OpenHAB"
var mysql_pw	= "OpenHABPW"
var mysql_db	= "OpenHAB"


rule "Archive Door status"

when   
	Item gDeur received update
then
	// Hack to get at the most recently changed window contact
	Thread::sleep(100) // experiment with this value, the purpose is to give persistence time to save the state so lastUpdate will work properly. You may not need it at all but if the wrong button keeps coming up, make this sleep longer
	var event = gDeur.members.sortBy[lastUpdate].last
	var event_update = event.lastUpdate.toDateTime.toString("yyyy-MM-dd HH:mm:ss")
	var event_status = transform("MAP","tex_contacts.map",event.state.toString)
	var event_label = event.label.toString.split("   ")
	
	var string sql_query = "mysql@@-u@@" + mysql_user + "@@-p" + mysql_pw + "@@-e@@INSERT INTO Alarm_Deuren (Timestamp, Status_ID, Status, Contact_ID, Contact) VALUES ('" + event_update + "', '" + event.state.toString + "', '" + event_status + "', '" + event.name + "', '" + event_label.get(0) + "');@@" + mysql_db
	var String mysql = executeCommandLine(sql_query, 5000)
	if(mysql!="") {
		logInfo("SQL","SQL query to be executed: " + sql_query)
    	        logInfo("SQL","Result SQL query: " + mysql)
	}
end

Some remarks related to my rules:

• All doors are in the group gDeur, so I don’t have to build a rule for each door;
• The @@-signs were needed to separate the commands in executeCommandLine;
• If the command was performed succesfully the variable mysql is empty. In other cases, I write the feedback from MariaDB to the log;

Next, I created some Table Panels using Grafana. Grafana works well with MariaDB, and since you can do data selections based on SQL statements, you can even filter out values that you don’t want in your view:

Then I created HTML files that were pointing to the Grafana table panel:

<!DOCTYPE html>
<html>
<head>
        <meta http-equiv="Content-type" CONTENT="text/html; charset=utf-8">
        <link rel="stylesheet" type="text/css" href="charts.css" />
</head>
    <div class="container">
		<iframe src="http://MYSERVER.org:XXX/dashboard-solo/db/alarm-logging?orgId=1&from=now-1M&to=now&panelId=3&theme=light" frameborder="0"></iframe>
    </div>
  </br>
</html>

The parameters in that URL (from=now-1M&to=now) make sure I only see the data from now untill one month ago.

And in my sitemap, I point to that HTML file:

Text label="Archief - Deuren" icon="deur" {
	Webview url="http://MYSERVER.org:XX/Grafana/Alarm_Deuren.html" height=33
	}

The result:

Mission Accomplished.

Improvements are welcome!

[marco_hoefle (Marco Höfle)]

Isn’t there a way around with creating a separate HTML file? Does webview not work with the Grafana link directly?