How to export alerts from the ESA MongoDB for RSA Security Analytics 10.6.x and 11.x

Issue

This article provides supplementary information on how to bulk export alerts from the Event Stream Analysis (ESA) appliances and virtual machines’ MongoDB on Security Analytics 10.6.x and 11.x.
In NW 11 you need to use the "respond-server" mongo database with "mongoexport" command.

Resolution

Note: To perform any of below, you must first SSH into the ESA host

For 10.6.x , Please perform the Steps below;

To export all alerts from the ESA host

 # 
 mongoexport –d esa –c alert –u esa –p esa –out /root/alerts.json

Parameter Syntax (can be seen using `mongoexport --help`:

 -d == MongoDB database instance, for this you would use 'esa'
 
-c == collection within MongoDB DB instance, for this you would use 'alert'
 
-u == username. The default for esa DB is 'esa'
 
-p == password. The default for the esa user of esa DB is 'esa'
 
-out == output filename including path.

In the above example all the alerts will be exported to alerts.json file under the /root folder.

To export alerts by module (rule) name

 # 
 mongoexport -d esa -c alert --out /root/alerts_by_rule.json -u esa -p esa --query '{"module_name": "<rule_name>"}'

All the alerts that were triggered by will be exported to the alerts_by_rule_name.json file under the /root folder.

To export alerts by module_name and time

 # 
 mongoexport -d esa -c alert --out /root/alerts_by_rule_time.json -u esa -p esa --query '{"module_name": "<rule_name>", "time": {$gte: new Date(<time1>), $lt: new Date(<time2>)} }'

All the alerts that triggered by between and will be exported to the alerts_by_rule_time.json file under the /root folder.

Example:

 # 
 mongoexport -d esa -c alert --out /root/alerts_by_rule_time.json -u esa -p esa --query '{ module_name : "Suspicious Login without any activity in windows hosts", "time": { $gt: new Date(1511407954000), $lt: new Date(1511494354000)} }'

Note: The mongo date type is similar a Unix Time/Epoch time, except it is number of milliseconds rather than number of seconds since midnight 1st/January/1970
Reference: https://docs.mongodb.com/manual/core/shell-types/#mongo-shell-date-type

Current date in milliseconds from Epoch (we are adding 3 zeros to output of seconds from Unix Time)

 # 
 echo $(($(date +"%s")*1000))
 
1511494354000

If currently time is 1511494354000 and there are 86,400,000 milliseconds per day, then 24 hours ago would be: 1511494354000 - 86,400,000 = 1511407954000

 # 
 mongoexport -d esa -c alert --out alerts_by_rule_time.json -u esa -p -esa --query '{ module_name : "Suspicious Login without any activity in windows hosts", "time": { $gt: new Date(1511407954000), $lt: new Date(1511494354000)} }'

Other UNIX Epoch timestamp conversations:
Converting from millisecond offset from Epoch date in UTC

 # 
 date -d @$((1511407954000 / 1000))
 
Thu Nov 23 03:32:34 UTC 2017

Converting a particular UTC date as millisecond offset from Epoch date

 # 
 printf '23-Nov-2017 09:00:00' | xargs -I {} date +"%s" --date={}
1511427600

So date, in this case, would be 1511427600000 (add 3 0's to convert from seconds to milliseconds)

For 11.x , Please perform the Steps below;

Export all alerts in the database:

 # mongoexport --db respond-server --collection alert --out /root/allalerts.json -u deploy_admin -p {deploy_admin_password} --authenticationDatabase admin

This will create a file in /root/ named "allalerts.json". However, depending on the size of the alerts collection it would take a considerable amount of time to run.

Export all alerts with specific Alert name received in a given time range. Example all alerts with name XXXXXXXXXXX from to :

 # mongoexport --db respond-server --collection alert --out /root/alerts_by_rule_time.json -u deploy_admin -p {deploy_admin_password} --authenticationDatabase admin --query '{"receivedTime":{$gt:new Date(<time1>),$lt:new Date(<time2>)},"alert.name" : "XXXXXXXXXXX"}'

Export all Alerts for a specific time period

 # mongoexport --db respond-server --collection alert --out /root/alerts_by_rule_time.json -u deploy_admin -p {deploy_admin_password} --authenticationDatabase admin --query '{"receivedTime":{$gt:new Date(<time1>),$lt:new Date(<time2>)}}'

Example :

 # mongoexport --db respond-server --collection alert --out /root/alerts_by_rule_time.json -u deploy_admin -p {deploy_admin_password} --authenticationDatabase admin --query '{"receivedTime":{$gt:new Date(1514764800000),$lt:new Date(1546300800000)}}'

Replace the {deploy_admin_password} with the deploy_admin account password.

The times [ , ] are shown in Epoch format ;
You could get the epoch time for a given date/time through mongo via a one-liner such as -

 # echo "var a = ISODate('2018-01-01T00:00:00Z'); a.getTime()" | mongo admin -u deploy_admin -p {deploy_admin_password} | grep -Evi 'mongo|bye'

Notes

For 10.6.x ;

The syntax to re-import entries into mongo using mongoimport is very similar.

To re-import alerts
1) Stop ESA service from altering DB

 service rsa-esa stop

2a) Import alert collection (without first dropping current contents)

 mongoimport -d esa -c alert -u esa -p esa --file /root/alerts.json --stopOnError

Otherwise:
2b) Drop alert collection and import

 # 
 mongo esa -u esa -p esa
 
TokuMX mongo shell v1.4.2-mongodb-2.4.10
 
connecting to: esa
 
> 
 db.alert.drop()
 
true
 
> 
 exit
 
bye
 
# 
 mongoimport -d esa -c alert -u esa -p esa --file /root/alerts.json --stopOnError

3) Restart ESA service

 service rsa-esa start

Product Details

RSA Product Set: Security Analytics, NetWitness Logs & Packets
RSA Product/Service Type: Event Stream Analysis (ESA)
RSA Version/Condition: 10.6.x and 11.x
Platform: CentOS

Summary

This article describes how to bulk export alerts for MongoDB on ESA hosts (version 10.6.x and 11.x).

Approval Reviewer Queue

RSA NetWitness Suite Approval Queue