Migrate Logs to new Appliance

I actually did figure out a way to migrate the logs and the reports from an old server to a new  one. The logs are simple to migrate by just copying all of the files under the /var/log directory. Migrating the reports is a little more difficult. I discovered that Sophos UTM utilizes POSTGRES SQL, so I researched how to back up the database and perform the restore. You can get a list of the databases on the UTM server with the following command: psql -U postgres -c '\l+' , which will show you a database titled 'reporting'. You can then backup the reporting database with the following command: pg_dump -U postgres -C -f /tmp/reporting_backup reporting . Once you have this backup, copy it to the new server. Now you can rename the reporting database with the command 'ALTER DATABASE reporting RENAME TO reporting_back'. Now you can drop the database on the new server by using 'DROP DATABASE reporting_back' and now restore the database from the old server with the following command: psql -d postgres -U postgres -f /tmp/reporting_backup . You may need to revoke privileges on the current database and bump the current connections of the SOPHOS application if you receive an error. Revoke privileges with 'REVOKE CONNECT ON DATABASE reporting FROM PUBLIC, reporting;' and drop any current connections with the following command:

SELECT
    pg_terminate_backend(pid)
FROM
    pg_stat_activity
WHERE
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'reporting'
    ;

You may or may not need to restart your server before you can see the restored reports on your new server. This definitely worked for me and hopefully will work for you as well. I also was able to restore all of the old data and any new data you may have already started reporting on the new server by merging the data  I dumped from the new servers database (Pull the data out of a dump file you create on the new server with a text editor) and appending that data into the dump file from the old server (again using a text editor) and then restoring this merged data.

I have been unhappy with the content of UTM's built-in reports, so I built my own reporting solution in SQL.  But that requires a cumbersome process of downloading the logs, unpacking the zip file, then reloading into SQL.   The log files are in a difficult format to parse, so it took some time to develop the text-parsing tools and figure out how to perform the load with acceptable performance.  But it has not been lost on me that all of this data is already in a database somewhere inside UTM.

With the magic you have done, have you found a way to connect to PostGres remotely to perform custom reporting?

I have been unhappy with the content of UTM's built-in reports, so I built my own reporting solution in SQL.  But that requires a cumbersome process of downloading the logs, unpacking the zip file, then reloading into SQL.   The log files are in a difficult format to parse, so it took some time to develop the text-parsing tools and figure out how to perform the load with acceptable performance.  But it has not been lost on me that all of this data is already in a database somewhere inside UTM.

With the magic you have done, have you found a way to connect to PostGres remotely to perform custom reporting?

I hadn't looked into using a third-party reporting tool since I've been using Sophos iView (It has free licensing for users without much use, like at home). I do use MS SQL Server Reporting Services at work though, and I know you can utilize this with SQL Server Express (No licensing fees). I did a quick search to see if you can setup a connector in SSRS and found that you can. You might want to check out this link since it will show you how to get SSRS connected to PostGres. Once you have the connector setup, it's just a matter of creating your own custom reports in SSRS.  http://blog.i-m-code.com/2013/06/03/connect-ssrs-to-postgresql-database/