Query for users with tokens and enabled for On-Demand Authentication in RSA Authentication Manager 8.2
Originally Published: 2017-03-22
Last Modified: 2026-11-04
Article Number
000042291
Applies To

RSA Product Set: SecurID
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.x (starting with 8.1)
 

Issue

Customer use case

In order to save ODA licenses we need to know:
  1. Which users haven't used ODA in more than 90 days
  2. Which users have both token and ODA                                                                                                                        
Resolution

E-mail template used for customer:

  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. Obtain an Authentication Manager 8.x database password as shown in red 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
  1. Execute the following query.  You will be prompted for the database password that was output in the previous step; enter it when prompted:
    /opt/rsa/am/pgsql/bin/psql -h localhost -p 7050 -d db -U rsa_dba -f ~/odauserswithtokens.txt > ~/odauserswithtokens.html
    
    Password for user rsa_dba: GrcvNN2FUAsWjyPfhaIsSWvjvZhvtN​
    1. This will direct the odauserswithtokens.html file into the rsaadmin user's home directory /home/rsaadmin.
    2. Connect to the Authentication Manager server using a file transfer client, such as Filezilla, and download the odauserswithtokens.html file.


    Actual Query

    Copy the text in the window below, and save it as  ~/odauserswithtokens.txt

    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;


     

    Alternate Query - Includes ODA Users Without Tokens

    Copy the text in the window below, and save it as  ~/allodausers.txt

    SELECT
        sms.last_login_date,
        tok.token_type,
        tok.serial_number,
        tok.token_shutdown_date,
        sms.last_updated_on AS oda_last_updated_on,
        sms.sms_enabled_on,
        tok.last_updated_on AS token_last_updated_on,
        ipd.loginuid
    FROM rsa_rep.am_sms_authenticators sms
    JOIN rsa_rep.am_principal ap
        ON ap.id = sms.principal_id
    JOIN rsa_rep.ims_principal_data ipd
        ON ipd.id = sms.principal_id
    LEFT JOIN rsa_rep.am_token tok
        ON tok.principal_id = sms.principal_id
    ORDER BY ipd.loginuid, tok.serial_number;

     

    Attachments
    If the attachment does not open when clicked, please refresh the page and try again. You must be logged into view the file(s).