ArcSight: Manager – Troubleshooting

 ArcSight  No Responses »
Sep 202012
 
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.

QRadar: Importing a Trusted SSL Certificate via a PFX File

 QRadar  No Responses »
Sep 202012
 

As QRadar needs both public and private key to be in clear-text format, you should follow the steps below to extract the keys from a pfx file:

1. The following command exports the private key and saves it in “key.pem”.

# openssl pkcs12 -in filename.pfx -nocerts -out key.pem

2. The following command exports the public key and saves it in “cert.pem”.

# openssl pkcs12 -in filename.pfx -clcerts -nokeys -out cert.pem

3. The following command removes the passphrase from the private key.

# openssl rsa -in key.pem -out server.key

Once these steps are done, “server.key” can be imported in QRadar as a private key, and “cert.pem” can be used as the public key.

ArcSight: Oracle DB – Troubleshooting

 ArcSight  No Responses »
Sep 202012
 
ERROR: TNS-00584: Valid node checking configuration error

This error occurs when there is an invalid or unreachable hostname in the TCP.INVITED_NODES list in the “sqlnet.ora” file (/home/oracle/OraHome11g/network/admin/sqlnet.ora).

To fix this error:

  1. Check the nodes in the sqlnet.ora file to make sure they are all reachable; OR
  2. Comment out the TCP.VALIDNODE_CHECKING parameter in the “sqlnet.ora” file.

ArcSight: Oracle DB – Useful Commands

 ArcSight  No Responses »
Sep 202012
 
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;