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.

Summary

This rule watches for new user accounts that are deleted on Windows servers. It will be triggered in case of having a user account deleted on a Windows server.

Rule Conditions

There is a set of conditions that need to be satisfied in order to trigger this rule. These conditions are different in Windows 2003 and 2008 as illustrated in Figures 1 and 2.

Figure 1: Windows 2003 – User Account Deleted

Figure 2: Windows 2008 – User Account Deleted

Rule Aggregation

This rule detects the single event of an account deletion in Windows. As a result, this rule does not require event aggregation and will be triggered for every single event that meets the rule conditions. However, the following fields should be added in the list of identical fields for aggregation to ensure the correlated event will preserve these values.

Rule Actions

Once this rule triggers, it executes the following actions as indicated in Figure 3:

Figure 3: Rule Actions

1. Update the data fields’ value in the correlated event that is generated by the rule. The following table illustrates the data fields that will be modified in the correlated event.

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;