Skip to content
  • There are no suggestions because the search field is empty.

How to backup and restore a NetWitness Malware Analysis database when reimaging the appliance

Issue

How to backup and restore Malware Analysis database when reimaging the appliance due to RMA replacement or reimaging.

This article is for internal use only.

Tasks

If customer requests to keep MA analysis data throughout the change, use the steps below to backup and restore the database.


Resolution

To backup the database

  1. SSH to the MA server.
  2. Run the following commands to stop the services:
# stop rsaMalwareDevice
# service postgresql-9.1 stop
  1. Navigate to the correct directory and tar the database:
# cd /var/lib/pgsql/9.1 
# tar cvfz MApgsql.tar.gz data
  1. Backup the .gz file onto a different server.
  2. Using the attached documentation, reimage the appliance.

To restore the database

  1. After reimaging the server, run the following commands to stop the services:
# stop rsaMalwareDevice
# service postgresql-9.1 stop
  1. Move the .gz file created above back to the newly reimaged server.
  2. Navigate to the correct directory and tar the database:
# cd /var/lib/pgsql/9.1 
# tar cvfz MApgsql.tar.gz data
  1. Copy back the gz file to /var/lib/pgsql
  2. Remove the data directory:
# rm -rf data
  1. Untar the backup
# tar xvfz MApgsql.tar.gz
  1. Change the file permissions, making sure to double check the ownership and permission /var/lib/pgsql and /var/lib/pgsql/9.1. They should be postgres:postgres 700 as well
# chown postgres:postgres data (D)
# chmod 700 data
  1. Restart services:
# service postgresql-9.1 start 
# start rsaMalwareDevice
 
  1.  
  2. (If required, DB schema will be migrated to later schema version automatically)

Notes

Note:  This solution only works for MA standalone service (VM/Physical) not for MA Co-Lo on SA server.

DB Data Directory

As MA utilizes PostgreSQL for saving analysis result, cold backup of the spectrum database can restore the current database.  The PostgreSQL data directory is /var/lib/pgsql/9.1/data.

DB Schema Migration

The database has schema version and it can be checked from /var/lib/rsamalware/spectrum/logs/spectrum.log.Whenever the MA service starts, the schema version is logged onto the spectrum log.  

For example, MA version 10.3.5, Schema Version : 10.3.0.4:
2016-06-07 10:22:46,500 [main] INFO com.googlecode.flyway.core.command.DbMigrate - Current version of schema "public": 10.3.0.4
2016-06-07 10:22:46,503 [main] INFO com.googlecode.flyway.core.command.DbMigrate - Schema "public" is up to date. No migration necessary.
MA version 10.5.0 and 10.6.0, Schema Version : 10.4.1.0
2016-06-09 16:46:24,663 [main] INFO com.googlecode.flyway.core.command.DbMigrate - Current version of schema "public": 10.4.1.0
2016-06-09 16:46:24,666 [main] INFO com.googlecode.flyway.core.command.DbMigrate - Schema "public" is up to date. No migration necessary.

Schema Migration

PostgreSQL backup from a lower MA version to a higher MA version is available as well. In this case, the DB schema migration will be done automatically when starting MA service. It is associated with the com.googlecode.flyway.code log module. With debug level of the module turned on, you will see more detail from the spectrum.log:
 
2016-06-09 16:07:13,294 [main] INFO com.googlecode.flyway.core.command.DbMigrate - Current version of schema "public": 10.3.0.4
2016-06-09 16:07:13,296 [main] INFO com.googlecode.flyway.core.command.DbMigrate - Migrating schema "public" to version 10.4.0.0
2016-06-09 16:07:13,343 [main] INFO com.googlecode.flyway.core.command.DbMigrate - Migrating schema "public" to version 10.4.0.1
2016-06-09 16:07:13,364 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Dropping EventAvVendor table...
2016-06-09 16:07:13,378 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Dropping filescoresummary tablle...
2016-06-09 16:07:13,414 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Adding 'displaydata' column to files table
2016-06-09 16:07:13,416 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Migrating displaydata from filescore table to files table....
2016-06-09 16:07:13,452 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Adding 'type' column to influences table
2016-06-09 16:07:13,459 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Adding 'event_id' column to influences table
2016-06-09 16:07:13,465 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Adding 'fileentry_id' column to influences table
2016-06-09 16:07:13,470 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Adding 'discriminator' column to influences table
2016-06-09 16:07:13,478 [main] WARN db.migration.V10_4_0_1__MigrateInfluences - Start of migrating influences. PLEASE NOTE: Depending on the
size of your data, this migration might take awhile. Please be patient.
2016-06-09 16:07:13,479 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Migrating NextGen's influences....
2016-06-09 16:07:13,491 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Finished with NextGen's influences
2016-06-09 16:07:13,492 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Migrating Static's influences....
2016-06-09 16:07:13,532 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Finished with Static's influences
2016-06-09 16:07:13,532 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Migrating Community's influences....
2016-06-09 16:07:13,545 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Finished with Community's influences
2016-06-09 16:07:13,545 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Migrating Sandbox's influences....
2016-06-09 16:07:13,547 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Finished with Sandbox's influences
2016-06-09 16:07:13,553 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Creating index on influences.iocKey....
2016-06-09 16:07:13,938 [main] INFO db.migration.V10_4_0_1__MigrateInfluences - Finished creating index on influences.iocKey....
2016-06-09 16:07:13,967 [main] INFO com.googlecode.flyway.core.command.DbMigrate - Migrating schema "public" to version 10.4.0.2
2016-06-09 16:07:13,979 [main] INFO db.migration.V10_4_0_2__RemoveTables - Removing groupscores_influences table....
2016-06-09 16:07:13,986 [main] INFO db.migration.V10_4_0_2__RemoveTables - Finished removing groupscores_influences table....
2016-06-09 16:07:14,008 [main] INFO db.migration.V10_4_0_2__RemoveTables - Removing filescore table....
2016-06-09 16:07:14,013 [main] INFO db.migration.V10_4_0_2__RemoveTables - Finished removing filescore table....
2016-06-09 16:07:14,021 [main] INFO db.migration.V10_4_0_2__RemoveTables - Removing eventscore table....
2016-06-09 16:07:14,022 [main] INFO db.migration.V10_4_0_2__RemoveTables - Finished removing eventscore table....
2016-06-09 16:07:14,029 [main] INFO db.migration.V10_4_0_2__RemoveTables - Removing groupscores table....
2016-06-09 16:07:14,032 [main] INFO db.migration.V10_4_0_2__RemoveTables - Finished removing groupscores table....
2016-06-09 16:07:14,067 [main] INFO com.googlecode.flyway.core.command.DbMigrate - Migrating schema "public" to version 10.4.1.0
2016-06-09 16:07:14,078 [main] INFO db.migration.V10_4_1_0__AlterNextGenQuery - Changing size on 'filter' column to 2048
2016-06-09 16:07:14,110 [main] INFO com.googlecode.flyway.core.command.DbMigrate - Successfully applied 4 migrations to schema "public"
(execution time 00:00.862s).
 

How to access PostgreSQL

To access PostgresSQL, open an SSH session to the server and run the commands below:
# su postgres
bash-4.1$ psql spectrum
psql (9.1.8)
Type "help" for help.

spectrum=# show data_directory;
     data_directory      
-------------------------
 /var/lib/pgsql/9.1/data
(1 row) 

spectrum=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------------------------------+----------+----------
public | alerttemplateentity | table | spectrum
public | alerttemplateentity_alertthresholdcriteria | table | spectrum
public | alerttemplateentity_iockeysrequired | table | spectrum
public | alerttemplateentity_metarequired | table | spectrum
public | alertthresholdcriteria | table | spectrum
public | documentconfig | table | spectrum
public | events | table | spectrum
public | fileavvendor | table | spectrum
public | files | table | spectrum
public | hashvalue | table | spectrum
public | hibernate_sequence | sequence | spectrum
public | influences | table | spectrum
public | meta | table | spectrum
public | metavalue | table | spectrum
public | nextgenprecache | table | spectrum
public | ondemandjobentry | table | spectrum
public | ondemandnextgenrequest | table | spectrum
public | ondemandnextgenschedule | table | spectrum
public | persistent_logins | table | spectrum
public | positions | table | spectrum
public | privatemeta | table | spectrum
public | qrtz_blob_triggers | table | spectrum
public | qrtz_calendars | table | spectrum
public | qrtz_cron_triggers | table | spectrum
public | qrtz_fired_triggers | table | spectrum
public | qrtz_job_details | table | spectrum
public | qrtz_locks | table | spectrum
public | qrtz_paused_trigger_grps | table | spectrum
public | qrtz_scheduler_state | table | spectrum
public | qrtz_simple_triggers | table | spectrum
spectrum=# \q
bash-4.1$

Product Details

RSA Product Set: Security Analytics, NetWitness
RSA Product/Service Type: Malware Analysis
RSA Version/Condition: 10.3.x, 10.4.x, 10.5.x, 10.6.x
Platform: CentOS
O/S Version: 6

Approval Reviewer Queue

Technical approval queue