Overview

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.

Prerequisites

Previous Database administration knowledge is strongly recommended

SSH access to Kerio Control

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.

Solution

Transfer current Star DB

1. Login via SSH and verify the presence of star.fdb file in /var/winroute/star folder.
2. Transfer /var/winroute/star folder 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.
   
3. The main star.fdb file will be available in C:\Users\<username>\Documents\star\data directory.

Database .NET explorer installation

For more information, please refer to the 3rd-party guide Open Firebird embedded database file.

1. Download and extract the 32-bit Free and Plus edition of Database .NET
2. Download the Firebird embedded zip file from Firebird downloads
3. Extract the Firebird-2.5.6.27020-0_Win32_embed.zip file and place your data file in this directory
4. Download and install Firebird Superserver
5. Add write permissions to the C:\Program Files (x86)\Firebird directory to your user
6. Run Database.NET, click Connect > Firebird > Open.
   
7. Choose Database file (C:\Users\<username>\Documents\star\data\star.fdb) and Embedded Client Library file (unzipped folder \Firebird-2.5.6.27020-0_Win32_embed)
   
   
   
8. Click Test to verify the Database connection settings. Click Open to view the database itself.
   

Database modification

1. Expand the database tables and locate USER_LIST table.
   
   
2. The user list will be opened.
   
3. 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.

       
   
       
   
       Click Execute.
     

4. Refresh the database to verify the user (win8) was deleted.
   
5. Close the Dabatase.Net application.

Transfer modified Database into Kerio Control

1. Make sure to transfer the original database to a safe place using SCP command.
   
2. Transfer the modified star.fdb (C:\Users\<username>\Documents\star\data\star.fdb) to Kerio Control's /tmp folder.
3. 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
   

Testing

Login to Statistics dashboard and verify the necessary user is no longer shown in the User dropdown.

Important Considerations

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.