Create list of users who have not logged into RSA Authentication Manager 8.x for a specific period of days

[#1]

Article Number

000033182

Applies To

RSA Product Set:  SecurID
RSA Product/Service Type:  Authentication Manager
RSA Version/Condition:  8.x

Issue

This article explains how to generate a report from the command line for users who have not logged in o RSA Authentication Manager 8.x for for a specific period of days.

Task

To log on to the appliance operating system using Secure Shell (SSH), SSH must be enabled

Procedure

1.  In the Operations Console, navigate to Administration > Operating System Access.
2.  In the SSH Settings section, select the checkbox for each NIC on which you want to enable SSH. If you have multiple NICs configured, you can enable SSH on more than one NIC.
3.  Click Save.

Resolution

Following are the command line steps to generate a report for users who have not logged in for a specific number of days to RSA Authentication Manager.

1. Launch an SSH client, such as PuTTY.
2. Login to the primary Authentication Manager server as rsaadmin and enter the operating system password.
3. Navigate to /opt/rsa/am/utils.

login as: rsaadmin
Using keyboard-interactive authentication.
Password: <enter operating system password>
Last login: Wed Oct 16 13:40:28 2019 from jumphost.vcloud.local
RSA Authentication Manager Installation Directory: /opt/rsa/am
rsaadmin@am82p:~> cd /opt/rsa/am/utils

Note that during Quick Setup another user name may have been selected. Use that user name to login.

4. Enter the following commands to get the database password:

rsaadmin@am83p:> /opt/rsa/am/utils/rsautil manage-secrets -a get com.rsa.db.dba.password 
Please enter OC Administrator username: <enter Operations Console administrator name>
Please enter OC Administrator password: <enter Operations Console administrator password>
com.rsa.db.dba.password: ckg2DBtNZLy80TADWcGqdF0NOJygAQ
 

Note that the database password value will be different for each installation of Authentication Manager.

The appropriate method would be to create a read only database user following the steps in 000027335 - Connecting to or querying the database using pgSQL in RSA Authentication Manager 8.x. 

5. Create a text file in /opt/rsa/am/utils with the name UserLastLogin.sql.

rsaadmin@am82p:/opt/rsa/am/utils> touch UserLastLogin.sql
rsaadmin@am82p:/opt/rsa/am/utils> vi UserLastLogin.sql

6. Press i to enter Insert mode.
7. Copy the SQL statement below into the text file:

SELECT loginuid,serial_number,last_login_date FROM am_token_oob,am_token, ims_principal_data WHERE am_token_oob.am_token_id=am_token.id AND am_token.principal_id=ims_principal_data.id AND last_login_date>'2016-05-07'

8. Save changes by pressing Escape then typing wq! and pressing Enter:
9. Use the following command to generate an output file named UserLastLogin.csv using the UserLastLogin.sql script created above:

/opt/rsa/am/pgsql/bin/psql -h localhost -p 7050 -d db -U rsa_dba UserLastLogin.sql -o UserLastLogin.csv

10. When prompted, enter the database password obtained from step 4.
11. Review the file UserLastLogin.csv.   Sample output is shown here:

 loginuid | serial_number | last_login_date
-----------------------------------------------------
smithj    | 000xxxxxxxx1  |  2016-05-02 20:53:23.734
roer      | 000xxxxxxxx2  |  2016-05-06 13:18:56.284
 

Notes

If you need further assistance, please contact RSA Customer Support and reference article 000033182.