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

How to trim the Reporting Engine database in RSA Security Analytics

Issue

How to trim the Reporting Engine database in RSA Security Analytics.

Resolution

Periodically, reports would hang  and no reports will be emailed or visible in the SA under Unified -> Reporting -> View. Once rsasoc_re is restarted, all reports become accessible again.

The primary issue is with the status database growing large in size. As the database grows, the subsequent calls to it takes longer than expected. Overtime multiple queries are getting executed at the same time, which results in blocking of calls and finally queuing of queries in the reporting engine.

In order to trim the database, follow the steps below.

  • Open the database file
  1. # stop rsasoc_re
  2. # mkdir /root/test-sa-db
  3. # cd /root/test-sa-db
  4. # wget http://repo2.maven.org/maven2/com/h2database/h2/1.2.147/h2-1.2.147.jar
  5. # java -cp ./h2-1.2.147.jar org.h2.tools.Shell -url jdbc:h2:file: (see Note)
  • Make changes to status tables
  1. sql> SET REFERENTIAL_INTEGRITY FALSE;
  2. sql> DELETE FROM REPORSTATUS WHERE ENDTIME < 'YYYY-MM-DD HH:MM:SS.NNN';
  3. sql> DELETE FROM RULESTATUS WHERE ENDTIME < 'YYYY-MM-DD HH:MM:SS:NNN';
  4. sql> DELETE FROM CHARTSTATUS WHERE ENDTIME < 'YYYY-MM-SS HH:MM:SS.NNN';
  5. sql> DELETE FROM FORMATSTATUS WHERE ENDTIME < 'YYYY-MM-SS HH:MM:SS.NNN';
  6. sql> DELETE FROM COMPONENT WHERE ENDTIME < 'YYYY-MM-SS HH:MM:SS.NNN';
  7. sql> DELETE FROM ALERTSTATUS WHERE ENDTIME < 'YYYY-MM-SS HH:MM:SS.NNN';
  8. sql> SET REFERENTIAL_INTEGRITY FALSE;
  9. sql> quit
  10. # start rsasoc_re


Notes

The path of the status database can usually be found here: /home/rsasoc/rsa/soc/reporting-engine/statusdb/statusmanager.h2.db

Internal Comments

UserName:shurtj
9/15/2014 10:35:14 PM - Technically Reviewed
Technically reviewed the article and changed its status to Copy Edited. Modified statements to adhere to Primus best practices.


Product Details

RSA Security Analytics
RSA Security Analytics 10.3.0 and above.
INTERNAL ONLY!!!