How to run a SQL query for Authentication Manager 8.0 or 8.1 and write the output to a file for support
Originally Published: 2013-12-10
Last Modified: 2023-10-06
Article Number
Applies To
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.x
Issue
Resolution
To access the PostgreSQL shell
- Login as rsaadmin via SSH or console.
- Navigate to /opt/rsa/am/utils.
- Capture the database password string, entering the Operations Console administrator and password when prompted.
- Connect to the Postgres database.
login as: rsaadmin Using keyboard-interactive authentication. Password: <enter the operating system password> Last login: Mon Oct 17 12:11:02 2016 from jumphost.vcloud.local RSA Authentication Manager Installation Directory: /opt/rsa/am rsaadmin@am81p:~> cd /opt/rsa/am/utils rsaadmin@am81p:/opt/rsa/am/utils> ./rsautil manage-secrets -a get com.rsa.db.dba.password Please enter OC Administrator username: <enter the Operations Console admin name> Please enter OC Administrator password: <enter the Operations Console admin password> com.rsa.db.dba.password: rSKD5bGguLGNL9uGvFWnJoxIcHJah2 rsaadmin@am81p:/opt/rsa/am/utils> cd ../pgsql/bin rsaadmin@am81p:/opt/rsa/am/pgsql/bin> ./psql -h localhost -p 7050 -d db -U rsa_dba Password for user rsa_dba: <enter the com.rsa.db.dba.password string from above> psql.bin (9.2.4) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. db=#
-
Run SELECT queries from the db# pompt
- After connecting to the database with the ./psql -h localhost -p 7050 -d db -U rsa_dba command, run a SELECT statement such as:
SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data WHERE loginuid = '<user_id>';
- Output will be as follows:
db=# SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data WHERE loginuid = 'batgirl';
exuid | loginuid | identity_src_key
-------+----------+----------------------------------
| batgirl | bc23d5571e02a8c0188ce386ceceb107
(1 row)
-
Run SELECT queries from rsautil and write output to a file
Alternatively, connect to Postgres with the –c option to specify psql is to execute the given command string and write output to a file named from_command.txt.
- In this case, the connection string will be as follows: ./psql -h localhost -p 7050 -d db -U rsa_dba -c 'SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data;' -o /tmp/from_command.txt.
- To view the results, open the from_command.txt file in a text editor.
- Use WinSCP or FileZilla in SFTP mode to copy the file from the Authentication Manager server.
rsaadmin@am81p:/opt/rsa/am/pgsql/bin> ./psql -h localhost -p 7050 -d db -U rsa_dba -c 'SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data;' -o /tmp/from_command.txt
Password for user rsa_dba: <enter the com.rsa.db.dba.password string from above>
rsaadmin@am81p:/opt/rsa/am/pgsql/bin> cat /tmp/from_command.txt
exuid | loginuid | identity_src_key
--------------------------------------------------+---------------+------------------------------------------------------------------------
| trustedapp | Trusted-Application-User-ID
| @PROXYUSER@ | 3ea0de93db9110ac0022a056ce26e519
\d0\7f\2c\88\55\7f\7b\45\86\8b\c3\f8\c0\b8\29\86 | Administrator | cn=administrator,cn=users,dc=2k8r2-vcloud,dc=local
| Tester | e44634f11e02a8c01cfc97bd22810e05
| toolbartest | a349e8e11e02a8c01b9337b66617857c
\5b\2d\df\53\fd\22\b9\4c\89\b8\d7\af\d2\cf\3a\91 | rsa | cn=rsa exchange,cn=users,dc=2k8r2-vcloud,dc=local
| support | e3ce6e521e02a8c019ae43f3cdc74442
| sspbind | ae7fa5671e02a8c0190a18d5060395f3
| amisbind | c609f3d61e02a8c01af118800bab6d03
| batgirl | bc23d5571e02a8c0188ce386ceceb107
| hdadmin | 2487f56a1e02a8c019bbf4b2adb9867a
| rsaadmin | 8afc03281e02a8c019d98c0ba37a25f1
| amistest | 82c1c3c71e02a8c019e8209cf88ecc39
| flash | f5cd613f1e02a8c01bbdea96db85a104
| admin | 000000000000000000001000d0022000
| ironman | 57b7789d1e02a8c01aa7f991f4e29841
| wonderwoman | 6afa85b61e02a8c01ac55e493e91a743
| supergirl | c08893061e02a8c01ae85d2b4c0b6b76
| arachne | 27dafbc11e02a8c01affe7bdcb458c26
| blackwidow | d693c0171e02a8c01b0474a160365f06
| darkstar | 357f92be1e02a8c01af8be52bf681c8c
(21 rows)
rsaadmin@am81p:/opt/rsa/am/pgsql/bin>
Run SELECT queries from a file and write output to a file
The third option is to put the SELECT statement in a file, then run the file with the –f option to read commands from the file defined in the command, rather than from standard input.
- Create a file named listusers.sql.
- Put the command 'SELECT exuid, loginuid, identity_src_key FROM rsa_rep.ims_principal_data;' in the listusers.sql file and save it.
- Navigate to /opt/rsa/am/pgsql/bin.
- Run the database query from the command line, as follows.
- The output /tmp/from_file.txt has a list of all registered Authentication Manager users; that is those who have a token or a fixed passcode assigned, or have registered for Risk Based Authentication (RBA) or On-Demand Authentication (ODA or ODT) or with their security questions in the Self-Service Console.
rsaadmin@am81p:/opt/rsa/am/pgsql/bin> ./psql -h localhost -p 7050 -d db -U rsa_dba -f /opt/rsa/am/utils/listusers.sql -o /tmp/from_file.txt Password for user rsa_dba: <enter the com.rsa.db.dba.password string from above> rsaadmin@am81p:/opt/rsa/am/pgsql/bin> cat /tmp/from_file.txt loginuid | exuid | identity_src_key ---------------+--------------------------------------------------+------------------------------------------------------------------------ trustedapp | | Trusted-Application-User-ID @PROXYUSER@ | | 3ea0de93db9110ac0022a056ce26e519 Administrator | \d0\7f\2c\88\55\7f\7b\45\86\8b\c3\f8\c0\b8\29\86 | cn=administrator,cn=users,dc=2k8r2-vcloud,dc=local Tester | | e44634f11e02a8c01cfc97bd22810e05 toolbartest | | a349e8e11e02a8c01b9337b66617857c rsa | \5b\2d\df\53\fd\22\b9\4c\89\b8\d7\af\d2\cf\3a\91 | cn=rsa exchange,cn=users,dc=2k8r2-vcloud,dc=local support | | e3ce6e521e02a8c019ae43f3cdc74442 sspbind | | ae7fa5671e02a8c0190a18d5060395f3 amisbind | | c609f3d61e02a8c01af118800bab6d03 batgirl | | bc23d5571e02a8c0188ce386ceceb107 hdadmin | | 2487f56a1e02a8c019bbf4b2adb9867a rsaadmin | | 8afc03281e02a8c019d98c0ba37a25f1 amistest | | 82c1c3c71e02a8c019e8209cf88ecc39 flash | | f5cd613f1e02a8c01bbdea96db85a104 admin | | 000000000000000000001000d0022000 ironman | | 57b7789d1e02a8c01aa7f991f4e29841 wonderwoman | | 6afa85b61e02a8c01ac55e493e91a743 supergirl | | c08893061e02a8c01ae85d2b4c0b6b76 arachne | | 27dafbc11e02a8c01affe7bdcb458c26 blackwidow | | d693c0171e02a8c01b0474a160365f06 darkstar | | 357f92be1e02a8c01af8be52bf681c8c (21 rows) rsaadmin@am81p:/opt/rsa/am/pgsql/bin>
Related Articles
Data runs show error: Exception while executing filter query RSA Identity Governance & Lifecycle 6.8 and above Number of Views Guidelines for writing Report Chart SQL Queries in RSA Identity Governance & Lifecycle Number of Views Collector reports ORA-12805: parallel query server died unexpectedly, in RSA Identity Governance & Lifecycle Number of Views How to list RSA SecurID tokens updated and assigned in last X days using custom SQL query Number of Views 'ORA-00942: Table or View does not exist' error when referencing a custom table/view in a report query in RSA Identity Gov… Number of Views
Trending Articles
Artifacts to gather in RSA Identity Governance & Lifecycle How to manipulate imported RSA SecurID Software Token(s) on an iPhone or iPad device How to Update the Root (Server) and Client Certificates in RSA Identity Governance & Lifecycle Troubleshooting AFX Connector issues in RSA Identity Governance & Lifecycle How to Download and Reinstall the AFX Server Archive in RSA Governance & Lifecycle
Don't see what you're looking for?
