Overview

When there is a need to review the statistical data in a non-standard format, for example, CSV instead of PDF, Kerio Control statistics can be accessed through internal files.

Save as PDF using the Print feature

The STaR data is saved in /var/winroute/star folder. Afterward, the database values can be converted into the required format.

This article provides details on how to view and export User Statistical data.

Prerequisites

Download and unzip Firebird-2.0.7.13318-0 compressed package

Download and install SQL Manager Lite for InterBase and Firebird (freeware version)

Enable SSH access in Kerio Control Webadmin > Status > System Health

Solution

SSH tunnel

Firebird accepts a connection on localhost only, SSH tunnel is required.

1. Connect to the server using command line ssh (change 10.10.10.1 to the IP address of the Kerio server):
   ssh -f -N -L 3050:localhost:3050 root@10.10.10.1
   
   
2. (Alternative) Connect to the server using Putty, in Connection → SSH → Tunnels, and add new forwarded port:
   
   

   Source port	3050
   Destination	localhost:3050

   Click Add.

SQL Manager config

1. Launch SQL Manager.
2. Right-click in the left pane (Databases) → Register Host… and fill in the form:
   
   

   Host name	localhost
   Protocol	TCP/IP
   Password	masterkey
   Client library	Path to the fbclient.dll for Firebird 2.0

   
   
3. Do not change anything else, Next, Finish.
4. Right-click on the newly created item in the left pane → Register Database..., select the host, Next, Next, fill in the form:
   
   

   Database name	/var/winroute/star/data/star.fdb
   Charset	UNICODE_FSS (clicking “Get default” should do it for you)

   
   
5. Click Finish.
6. Now you should be able to view the configured STaR database.
   

Viewing data

For example, you need to review the data In-Out for the day, hour, month, or week per user basis.

Open DATAINOUT_D (or DATAINOUT_H and so on) and USER_LIST tables.

USER_ID is a db relationship connection in both tables.

Important: you need to switch to the Data tab in order to view the stats.

Exporting data

To export content from the tables into a CSV format, the only tool available is the EMS Data Export for Interbase/Firebird. However, this one is a paid app in order to export all of the data without limitation. The unregistered version is only limited to 20% of rows.