With configured persistence OpenHAB writes permanently new item states in the data base. That means the data base is permanently growing and it comes a time that you may want to know hoe your data base looks like.

I wrote the python script dbStatistics.py which is showing all items in your data base in an CSV file, which can be opened by Excel. The script uses SQL commands and should work with all SQL databases but is currently only tested with my MariaDB data base. You can find this script in GitHub here: openHABdbTools.

With the script dbStatistics.py you can create a CSV table which contains the data of all your items and their data base index, how much entries they have in the data bases and the date and time of the oldest and newest entry in your DB. You can run this script from command line. All the configuration is read from dbtools.ini. The created file is called openHABdbStat.csv and can be found in the configured root path (as standard is `/etc/openhab2/data/`).

The script does the following:

• Request all items over the REST API from the localhost openHAB installation
• Read all entries from the openHAB data base
• Add all active items seen on REST API to the table and check how many data is in the data base for each item
• Finally add also the items from the database to the table which are not active in openHAB any more (old deleted or renamed items)

You can copy this script to /etc/openhab2/scripts and give him execution rights with „chmod +x *.py“.

You also need an configuration file dbtools.ini. This file is used to configure your database access and the number of days which you want to keep in your database:

[root]
#root path for all created files
path=/etc/openhab2/data/
#uncomment this when you have a german Excel Version. default is ','
delimiter=;

[dbconfig]
#host address of the db server:
host=localhost      	
#openHAB table:
table=openhab   
#openHAB data base user:
user=openhab	
#openHAB data base password
password=openhab		

Adapt [dbconfig] to your data base configuration. In [root] you can set the location where the CSV file shall be created.

Note:
Excel versions for different countries load CSV files different. if you use a English Excel version the default delimiter ‚,‘ works fine. For German Excel versions you use better ‚;‘. this can be set with the delimiter= value.

You can call the script from the command line now. It makes some time consuming SQL and python operations and prints for all the items the number of entries which are found in the database.

[.[455].[3132].[7].[287].[4149].[90999].[90642].[90641].
...
.[62302].[4].[0].#not in configuration#.[0].[2].[28].[8].[0].[0].
...
.[0].[0].[0]]
Number of unused tables in data base (tables without active item in openHAB) is X

After that you can check your generated openHABdbStat.csv. Should look like this:

At the end of the file you find the items which are only existing in your data base but not in openHAB any more. This can be because you deleted them from your openHAB item configuration. In the CSV file you can identify them because all values like label or type have content -.-

Would be nice to get feedback if the script is also working for your installation and which persistence configuration you use.