Customer use case

1. Which users haven't used ODA in more than 90 days
2. Which users have both token and ODA                                                                                                                        

1. Upload the odauserswithtokens.txt file (attached) to the primary Authentication Manager server's /home/rsaadmin directory.
2. If using Filezilla, set up a site as SFTP, use rsaadmin to login.
3. Logon to the Authentication Manager 8.x primary via SSH as rsaadmin (Note: SSH must be enabled via the Operations Console under Administration > Operating System Access).
4. Determine an Authentication Manager 8.x database password as shown in following example.  The output password will be different than the example value shown here):

/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: GrcvNN2FUAsWjyPfhaIsSWvjvZhvtN

5. Execute the following query.  You will be prompted for the database password output in the previous step.

/opt/rsa/am/pgsql/bin/psql -h localhost -p 7050 -d db -U rsa_dba -f ~/odauserswithtokens.txt > ~/odauserswithtokens.html
com.rsa.db.dba.password: GrcvNN2FUAsWjyPfhaIsSWvjvZhvtN​

6. This will direct the odauserswithtokens.html file into the rsaadmin user's home directory /home/rsaadmin.
7. Connect to the Authentication Manager server using a file transfer client, such as Filezilla, and download the odauserswithtokens.html file.

Actual Query

SELECT 
  am_sms_authenticators.last_login_date, 
  am_token.token_type, 
  am_token.serial_number, 
  am_token.token_shutdown_date, 
  am_sms_authenticators.last_updated_on, 
  am_sms_authenticators.sms_enabled_on, 
  am_token.last_updated_on, 
  ims_principal_data.loginuid
FROM 
  rsa_rep.am_principal, 
  rsa_rep.am_sms_authenticators, 
  rsa_rep.am_token, 
  rsa_rep.ims_principal_data
WHERE 
  am_principal.id = am_sms_authenticators.principal_id AND
  am_sms_authenticators.principal_id = am_token.principal_id AND
  am_token.principal_id = ims_principal_data.id;