Dr. SIEM

Logger to Logger Data Migration

From time to time, I get into customers that have been using ArcSight Logger for a couple of months as a POC box and once they make their decision to buy the Logger appliance from HP, they are looking for options to migrate data from the POC Logger to the newly purchased Logger without going through archive process that requires a permanent external storage. This post walks you through the steps to have a smooth data migration from one Logger to another.

Step 1: To keep the logs organized, create a new Storage Group with the proper size and retention time.

Figure 1: Setting up the Storage Group

Step 2: In the new Logger, browse to “Configuration | Event Input” section and create a new receiver with the configuration that is demonstrated in Figure 2.

Figure 2: Setting up the CEF TCP Receiver

Step 3: Enable the created the CEF TCP receiver.

Step 4: In the new Logger, browse to “Configuration | Devices” section and create a new Device with the IP address of the POC Logger and the receiver that got created in step 2.

Figure 3: Adding a New Device

Step 5: In the new Logger, browse to “Configuration | Devices” section and create a new Device Group called “Historical Events” and add the relevant devices to the new Device Group.

Figure 4: Defining a Device Group for the Data Migration

Step 6: In the new Logger, browse to “Configuration | Storage” section and create a new Storage Rule that maps the created Device Group in Step 5 to the Storage Group that was created in Step 1.

Figure 5: Setting up the Storage Rule

Step 7: In the old Logger, browse to “Configuration | Event Output” section and create a new forwarder as demonstrated in Figure 6.

Figure 6: Setting up the TCP Forwarder

Step 8: Click Next and then select the proper query and time range to select the events that should be migrated. It is also important that the port matches with what is configured on the new Logger in step 2. Click

Figure 7: Selecting the events that need to be forwarded to the new logger

Step 9: Click on Save and enable the forwarder once you are ready for the data migration.

The data migration will start a few minutes after the forwarder is enabled. Keep in mind that the speed of the data transfer depends on the network bandwidth and also the IOPS rate on the Logger appliance, but on average you can expect about 15,000 events to be transferred every second.

ERROR: java.sql.SQLException: Got minus one from a read call

This error occurs when the connection from the manager to the database is not successful.

To fix this error:

1. Check the status of the database using the following command. The status for the ArcSight database should be “OPEN”.

root# su oracle
oracle$ sqlplus / as sysdba
SQL> select instance_name,status from gv$instance;

2. Check the status of the Oracle listener using the following command. The output should include the Start Date and Uptime if it is running.

cd /usr/local/arcsight/db/bin
./arcdbutil lsnrctl status

3. Check the TCP.INVITED_NODES parameter in the “sqlnet.ora” file (/home/oracle/OraHome11g/network/admin/sqlnet.ora). The manager IP address or host name should be included as a valid host in this parameter.

Log in to sqlplus

To log in to sqlplus you need to SSH to the Oracle server as root and run the following commands:

root# su oracle
oracle$ sqlplus / as sysdba

Query – Explore ArcSight database

oracle$ sqlplus / as sysdba
-- Assumption1: ArcSight DB Name is "arcsight"
-- Assumption2: ArcSight DB Username is "arcsight"
SQL> connect arcsight@arcsight
SQL> select * from tab where TNAME LIKE 'ARC_EVENT%';
SQL> select column_name from all_tab_columns where table_name = 'ARC_EVENT_GEO_LOCATION';

Query – Database start time

select	to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from	v$instance
/

Query – Database size  and available free space

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select	round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,	round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 
	round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,	round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select	bytes
	from	v$datafile
	union	all
	select	bytes
	from 	v$tempfile
	union 	all
	select 	bytes
	from 	v$log) used
,	(select sum(bytes) as p
	from dba_free_space) free
group by free.p
/

 Query – Available space on each tablespace

set linesize 150
column tablespace_name format a20 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total| Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(MB)'
column Pct_Free format 999.99 heading '% Free'
column Chunks_Free format 9999 heading 'No Of Ext.'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
set echo off
PROMPT  FREE SPACE AVAILABLE IN TABLESPACES
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
     sum(a.sumb/1048576) Tot_Free,
     sum(a.sumb)*100/sum(a.tots) Pct_Free,
     sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from
     (select tablespace_name,0 tots,sum(bytes) sumb,
             max(bytes) largest,count(*) chunks
      from dba_free_space a
      group by tablespace_name
     union
      select tablespace_name,sum(bytes) tots,0,0,0
      from dba_data_files
      group by tablespace_name) a
group by a.tablespace_name order by pct_free;

Query – Delete archive logs

oracle$ rman TARGET /
RMAN> delete archivelog all;