When it's needed to clean up User statistics, for example, remove non-existent or redundant users from the Statistics and Reporting, a specific procedure needs to be applied.
Kerio Control stores statistical data using the Firebird database (star.fdb file), which can be opened using Database viewer tools.
This article describes the process how to achieve such a requirement using Database.Net explorer and Windows PC. In this example, we will be removing a user with win8 username.
Previous Database administration knowledge is strongly recommended
Important: the statistical data for the time you spent modifying star.fdb will be lost. Make sure to execute this procedure outside of business hours.
Transfer current Star DB
- Login via SSH and verify the presence of star.fdb file in
/var/winroute/starfolder to your local PC using SCP command.
The entire folder is being transferred in case the restoration/roll-back to the original state is needed.
- The main star.fdb file will be available in
Database .NET explorer installation
For more information, please refer to the 3rd-party guide Open Firebird embedded database file.
- Download and extract the 32-bit Free and Plus edition of Database .NET
- Download the Firebird embedded zip file from Firebird downloads
- Extract the
Firebird-22.214.171.124020-0_Win32_embed.zipfile and place your data file in this directory
- Download and install Firebird Superserver
- Add write permissions to the
C:\Program Files (x86)\Firebirddirectory to your user
- Run Database.NET, click Connect > Firebird > Open.
- Choose Database file (
C:\Users\<username>\Documents\star\data\star.fdb) and Embedded Client Library file (unzipped folder
- Click Test to verify the Database connection settings. Click Open to view the database itself.
- Expand the database tables and locate
- The user list will be opened.
- Click New Query and write a database query to delete the necessary user with a specific username:
delete from USER_LIST where username = 'win8'
Important: replace win8 with the necessary username.
- Refresh the database to verify the user (win8) was deleted.
- Close the Dabatase.Net application.
Transfer modified Database into Kerio Control
- Make sure to transfer the original database to a safe place using SCP command.
- Transfer the modified star.fdb (
C:\Users\<username>\Documents\star\data\star.fdb) to Kerio Control's
- Execute the following commands:
mount -o rw,remount /
mv /tmp/star.fdb /var/winroute/star/data/star.fdb
/etc/boxinit.d/60winroute restart- this command will restart Kerio Control
Login to Statistics dashboard and verify the necessary user is no longer shown in the User dropdown.
You need to have Online Access with All Data enabled to view all user stats.
The user is being deleted ONLY from Statistical reports. It is still persistent in Webadmin.
Refer to Managing User accounts if the user needs to be removed.