000029221 - How to extract system user information from the RSA Security Analytics server

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support on Aug 22, 2019
Version 5Show Document
  • View in full screen mode

Article Content

Article Number000029221
Applies ToRSA Product Set: Security Analytics
RSA Product/Service Type: Security Analytics Server, Security Analytics UI
RSA Version/Condition: 10.3.x, 10.4.x, 10.6.x
Platform: CentOS
O/S Version: EL6
IssueAt this time, the ability to export user information for system users into a readable format for auditing purposes is not available within the Security Analytics.
TasksThis article will provide steps that explain how to query the underlying h2 database for the Security Analytics to list the user's table to the console and into a file.
ResolutionTo have the ability to query the h2 database on the Security Analytics server, you must first obtain the h2-1.2.147.jar tool (http://repo1.maven.org/maven2/com/h2database/h2/1.2.147/h2-1.2.147.jar) and transfer it to the /var/lib/netwitness/uax/db directory using your preferred file transfer agent such as WinSCP or FileZilla.

Alternatively, if the Security Analytics server appliance has Internet access, you can obtain the tool using the wget command with the instructions below.
  1. Connect to the appliance via SSH as the root user.
  2. Navigate to the appropriate directory with the following command:  cd /var/lib/netwitness/uax/db
  3. Download the tool by issuing the following command:  wget http://repo1.maven.org/maven2/com/h2database/h2/1.2.147/h2-1.2.147.jar

The tool is placed in the /var/lib/netwitness/uax/db directory so that it can be easily accessible when needing to access the h2 database for the Security Analytics UI.  However, using the tool against the active database instance requires the jettysrv service to be stopped, which will prevent access to the user interface.  As an alternative, follow the steps below to create a copy of the database against which to query using the tool, which will allow the jettysrv service to remain running.
  1. Create a temporary directory for the operation with the following command:  mkdir /tmp/h2
  2. Issue the two commands below to copy the active database and the h2-1-2-147.jar tool to the new directory.

    cp /var/lib/netwitness/uax/db/platform.h2.db /tmp/h2
    cp /var/lib/netwitness/uax/db/h2-1.2.147.jar /tmp/h2

  3. Navigate to the new directory with the following command:  cd /tmp/h2
  4. Issue the command below to access the SQL prompt for the copied h2 database.

    java -cp ./h2-1.2.147.jar org.h2.tools.Shell -url jdbc:h2:file:platform

  5. To output the list of users to the console, issue the query below at the sql prompt to display the contents of the USERS table within the database.

    SELECT * FROM USERS;

Although the output is not cleanly formatted it can be copied and pasted into a text file so that it can be viewed more easily.

  1. To output the list of users to a file in the current directory, issue the query below at the sql prompt to display the contents of the USERS table within the database.

    CALL CSVWRITE('user.csv','SELECT * FROM USERS');


The output will be formatted in a CSV file formatted as follows:

"ID","CREATEDBY","DATECREATED","DATEUPDATED","UPDATEDBY","DESCRIPTION","DISABLED","EMAILADDRESS","EXPIRED","EXPIRYDATE","FORCEPASSWORDCHANGE","ISEMAILNOTIFICATION","LOCKED","LOGIN","NAME","PASSWD","PASSWORDEXPIRYNOTIFIED","USEDIGEST","LASTSUCCESSFULLOGINTIMESTAMP"
"5","admin","2017-05-03 17:59:58.114","2017-05-03 19:03:05.548","SecOpsUser","Sec Ops User for Case 962252","FALSE","none@rsa.com","FALSE",,"FALSE","FALSE","FALSE","SecOpsUser","SecOps User","Q/TDlvDfSila3zzYnUUkk+1L++MfVYY2jf3VYvAQ6ffwpWWHKHRX7g==","FALSE","FALSE","2017-05-03 19:03:05.548"



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.
NotesThe screenshot below provides a demonstration of the procedure mentioned above.

Demonstration of the procedure.

Attachments

    Outcomes