000029296 - How to export alerts on RSA Security Analytics 10.3.x ESA appliances using PostgreSQL

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000029296
Applies ToRSA Product Set: Security Analytics
RSA Product/Service Type: Event Stream Analysis (ESA)
RSA Version/Condition: 10.3.x
Platform: CentOS
IssueThis article will describe how to bulk export alerts for PostgreSQL ESA appliances (version 10.3.5 and below). This will not work on ESA appliances on version 10.4 and above as they are using a different database type. 
ResolutionTo export alerts from the ESA appliance you must first SSH into the ESA appliance.


Enter the following commands:

psql -h localhost -U esa

##enter the password##


From there you will need to enter the following command, modifying the time and [alert name] (please remove the [ ] surrounding the alert name):


\copy (SELECT * FROM alert WHERE module_name = '[alert name]' AND (time BETWEEN '2014-10-01 00:00:00.00' AND '2014-11-01 00:00:00.00')) TO '/tmp/file.csv' DELIMITER ',' CSV HEADER

Note that the .csv file will be exported to /tmp/file.csv 

To exit the psql command line enter \q


If you are unsure of any of the steps above or experience any issues, contact RSA Support and quote this article number for further assistance.
NotesIf this query is taking too long or hanging you can terminate it by pressing ctrl+c on your keyboard.


You can get the alert name by going to the “Alerting Summary” page of your SA GUI. At the bottom of that page there will be a list of alert names you can query using the command above. Please remove the [] that surround alert name. (e.g. module_name=’Port Scan Horizontal Packet’)


This command can output very large files. We recommend that you use an initial time range of 1-5 minutes and then check the size of the file in the /tmp directory and adjust from there. If you are exporting a large amount of data you may consider attaching an external hard drive and export directly to that.