This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Migrate Logs to new Appliance

Hi all,

is there a way to migrate all the logs and reportings from an old appliance to a new one?

Thanks for your help.

Cheers,

Fred



This thread was automatically locked due to age.
  • Reporting no, there is no way. For logs (/var/log/), use WinSCP to copy the logs off of the "old" UTM, then to write them onto the "new". On the "new" UTM, be certain to enable Log Archiving (Logging & Reporting > Log Settings > Remote Log File Archives), so you don't have to go through this process again. :)
    __________________
    ACE v8/SCA v9.3

    ...still have a v5 install disk in a box somewhere.

    http://xkcd.com
    http://www.tedgoff.com/mb
    http://www.projectcartoon.com/cartoon/1
  • 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.

  • Hi, David, and welcome to the UTM Community!

    Wow!  Just wow!  Only your second post here, and you've helped us all with questions not ever answered here before.

    One suggestion - instead of using the /tmp directory which is in its own, smaller partition, use the /home directory which is on the largest partition.  That's where Sophos engineers will want you to keep your own stuff like speedtest_cli.py.

    We're glad you're here!

    Cheers - Bob

     
    Sophos UTM Community Moderator
    Sophos Certified Architect - UTM
    Sophos Certified Engineer - XG
    Gold Solution Partner since 2005
    MediaSoft, Inc. USA
  • good day

    I follow the steps indicated to support the base reporting in postgress I am doing in utm 9.411 but it does not work I do not give me error but it does not manage to migrate the reporter

    I detail the steps followed

    Pg_dump -U postgres -C -f / tmp / reporting_backup

    In utm new

    Sudo your postgres psql

    ALTER DATABASE reporting RENAME TO reporting_back

    DROP DATABASE reporting_back

    Psql -d postgres -U postgres -f / tmp / reporting_backup

    Does not give any error but does not show the report

    I have made a copy of the logs / var / log

  • 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/