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.
Download and unzip Firebird-22.214.171.12418-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
Firebird accepts a connection on localhost only, SSH tunnel is required.
- 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 email@example.com
- (Alternative) Connect to the server using Putty, in Connection → SSH → Tunnels, and add new forwarded port:
Source port 3050 Destination localhost:3050
SQL Manager config
- Launch SQL Manager.
- 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
- Do not change anything else, Next, Finish.
- 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)
- Click Finish.
- Now you should be able to view the configured STaR database.
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.
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.