000027735 - Generate a Report on Users with Tokens and Fixed Passcodes

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000027735
Applies ToRSA Authentication Manager 8.0
RSA Authentication Manager 8.1
rsa_rep.AM_TOKEN
IssueGenerate a Report on Users with Tokens and Fixed Passcodes
RSA Authentication Manager 8.x only has a report template called 'Administrators with Fixed Passcode'.
Resolution

Currently an administrator can run reports on Administrators with Fixed Passcodes but there is no report template for Users with Fixed Passcodes.


 


Customers wishing to run reports where there is no template present in the RSA Security Console will need to write their own code using the RSA Authentication Manager SDK or contact RSA Professional Services who can arrange to write either the code or required SQL statement to generate a report for the report requirement.


 


Please note: RSA Customer Support is also interested in knowing the types of reports customers want, where they do not exist in the current RSA product,  so a Request for Enhancement (RFE) can be raised which will be reviewed by RSA Product Management to determine if is can be incorporated in the next release of the RSA product.


AM-11028


 


As an example; here is an SQL statement that will report on users from the rsa_rep.AM_TOKEN table where a serial number is present against a loginuid. The output will report on users with a fixed passcode and these users are shown with the serial number being blank however where a user has a token assigned and a fixed passcode the report will only show the user's loginuid and token serial number.


 


Steps:



  

1.


  

  

Logon to the virtual appliance (RSA Authentication Manager 8.1) with the rsaadmin account (where SSH session has been enabled via the RSA Operations Console).


  

 


  

  

2.


  

  

Navigate to the /opt/rsa/am/utils folder


  

 


  

  

3.


  

  

Create a shell script called ?SQL.sh? using an editor (such as vi)


  

 


  

  

4.


  

  

Substitute <OC_Admin_ID> and <OC_Admin_Password> with your correct values into the script shown below:


  

 


  

     

 


     

STRG=`/opt/rsa/am/utils/rsautil manage-secrets -a get com.rsa.db.dba.password -u <OC_Admin_ID> -p <OC_Admin_Password>`


     

# echo $STRG


     

PGPASSWORD=`echo $STRG | cut -d' ' -f2` export PGPASSWORD


     

# echo $PGPASSWORD


     

. /opt/rsa/am/utils/rsaenv


     

/opt/rsa/am/pgsql/bin/psql -h localhost -p 7050 -d db -U rsa_dba


     

 


     

  

 


  

..and copy and paste the amended script into the editor on your virtual appliance. Save the new shell script called SQL.sh.


  

 


  

 


  

Uncommenting the echo lines in the script reveals the parameters allowing for troubleshooting (should it be needed).


  

 


  

Example:


  

     

 


     

rsaadmin@am81p:/opt/rsa/am/utils> ./SQL.sh


     

com.rsa.db.dba.password: vDBh1Rb005S7nX9t304v8jy3eHFFGI


     

vDBh1Rb005S7nX9t304v8jy3eHFFGI


     

psql.bin (9.2.4)


     

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)


     

Type "help" for help.


     

 


     

db=#


     

 


     

  

 


  

 


  

  

5.


  

  

Update the permissions on the new shell script using the chmod command e.g. chmod 755 SQL.sh


  

 


  

  

6.


  

  

Run the new shell script SQL.sh to allow for database access..


  

 


  

Example:


  

     

 


     

rsaadmin@am81p:/opt/rsa/am/utils> ./SQL.sh


     

psql.bin (9.2.4)


     

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)


     

Type "help" for help.


     

 


     

db=#


     

 


     

  

 


  

 


  

  

7.


  

  

Now we can run SQL statements to review table information in the authentication manager database..


  

 


  

SQL statement


  

 


  

select amt.serial_number as SERIAL_NUMBER, ipd.loginuid, amt.token_type, amt.lost_mode, amt.token_shutdown_date, amt.pin_type, amt.token_code_length, amt.is_enabled FROM rsa_rep.IMS_PRINCIPAL_DATA ipd LEFT JOIN rsa_rep.AM_TOKEN amt ON amt.PRINCIPAL_ID = ipd.ID  LEFT JOIN rsa_rep.AM_SMS_AUTHENTICATORS sms ON sms.PRINCIPAL_ID=ipd.ID LEFT JOIN rsa_rep.AM_PRINCIPAL amp ON ipd.ID =amp.ID  LEFT JOIN RSA_REP.IMS_IDENTITY_SOURCE iis ON ipd.IDENTITY_SRC_ID = iis.ID LEFT JOIN RSA_REP.IMS_PRINCIPAL_LOGIN_DATE ipld ON amp.id = ipld.principal_id LEFT JOIN RSA_REP.IMS_SECURITY_DOMAIN isd ON isd.id = ipd.owner_id where not (amp.IS_STATIC_PASSWORD_SET =FALSE and amt.serial_number is null and sms.SMS_ENABLED_ON is null AND right(ipd.authenticator_bit_flags,1)='0');


  

 


  

Example:


  

     

 


     

rsaadmin@am81p:/opt/rsa/am/utils> ./SQL.sh


     

psql.bin (9.2.4)


     

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)


     

Type "help" for help.


     

 


     

db=# select amt.serial_number as SERIAL_NUMBER, ipd.loginuid, amt.token_type, amt.lost_mode, amt.token_shutdown_date, amt.pin_type, amt.token_code_length, amt.is_enabled FROM rsa_rep.IMS_PRINCIPAL_DATA ipd LEFT JOIN rsa_rep.AM_TOKEN amt ON amt.PRINCIPAL_ID = ipd.ID  LEFT JOIN rsa_rep.AM_SMS_AUTHENTICATORS sms ON sms.PRINCIPAL_ID=ipd.ID LEFT JOIN rsa_rep.AM_PRINCIPAL amp ON ipd.ID =amp.ID  LEFT JOIN RSA_REP.IMS_IDENTITY_SOURCE iis ON ipd.IDENTITY_SRC_ID = iis.ID LEFT JOIN RSA_REP.IMS_PRINCIPAL_LOGIN_DATE ipld ON amp.id = ipld.principal_id LEFT JOIN RSA_REP.IMS_SECURITY_DOMAIN isd ON isd.id = ipd.owner_id where not (amp.IS_STATIC_PASSWORD_SET =FALSE and amt.serial_number is null and sms.SMS_ENABLED_ON is null AND right(ipd.authenticator_bit_flags,1)='0');


     

 


     

serial_number | loginuid  | token_type | lost_mode | token_shutdown_date | pin_type | token_code_length | is_enabled


     

---------------+-----------+------------+-----------+---------------------+----------+-------------------+------------


     

000212085011  | rhui      |          4 |         0 | 2014-03-31 00:00:00 |        0 |                 8 | t


     

              | rsatest   |            |           |                     |          |                   |


     

000233022516  | mbell     |          9 |         0 | 2017-03-31 00:00:00 |        0 |                 6 | t


     

000146455504  | mbell     |          4 |         0 | 2015-02-28 00:00:00 |        0 |                 8 | t


     

000146455508  | adeguzman |          4 |         0 | 2015-02-28 00:00:00 |        0 |                 8 | t


     

000233022517  | mwbadmin  |          9 |         0 | 2017-03-31 00:00:00 |        0 |                 6 | t


     

              | tuser     |            |           |                     |          |                   |


     

000212085036  | cchen     |          4 |         0 | 2014-03-31 00:00:00 |        0 |                 8 | t


     

(8 rows)


     

db=#


     

 


     

  

 


  

** users with a fixed passcode assigned show up with no serial number or token_type; in this example rsatest and tuser are two users with fixed passcodes **


  

 


  

Exiting db=#


  

 


  

Use the ?\q? sequence to return to the command line


  

 


  

Example:


  

     

 


     

db=# \q


     

rsaadmin@am81p:/opt/rsa/am/utils>


     

 


     

  

 


  

 


  

 


NOTE: If you are having difficulty contacting RSA Professional Services then please contact your local RSA Sales representative or contact RSA Customer Support so contact details can be forwarded to RSA Professional Services for your requirement.


 


Contact information for RSA Customer Support is located at URL http://www.emc.com/support/rsa/contact/index.htm.


 

Notesfor a solution to create an on-screen output, see A67945
 
Legacy Article IDa65025

Attachments

    Outcomes