How to run a SQL query report on several UserIDs with a wildcard in RSA Authentication Manager 8.x
2 years ago
Originally Published: 2015-06-30
Article Number
000053681
Applies To
RSA Product Set: SecurID
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.x
Issue
Canned reports available in the RSA Authentication Manager Security Console under Reporting > Reports can search for or filter on a specific UserID. They do not have a wildcard ability, so we cannot search for all users whose UserID starts with a group prefix, such as UserID = ABC*.

We found an issue where an Admin API that read from AD was inserting trailing spaces on the UserID.  Therefore, we could not report on this specific UserID (for example, a UserID = 'ABCDEFG ') because the Security Console drops trailing spaces by default. 

This article explains how to circumvent the issue by using a wildcard search on reports generated in the Security Console.
Resolution
Modify the SQL statement below to generate an Authentication Activity report for a UserID that contains a wildcard.  This specific UserID was created with a trailing space, then modified in Security Console so that the trailing space was removed.  Due to this, the Security Console report could not find the UserID with the trailing space unless it was unfiltered.

To run the SQL:
  1. Log in to the RSA Authentication Manager primary using the steps in 000038244 - How to SSH to an RSA Authentication Manager server.
  2. Go to /opt/rsa/am/utils.
  3. Run the command ./rsautil manage-secrets -a get com.rsa.db.dba.password to capture the database user's secret.
  4. Enter the Operations Console administrator's username and password.
  5. Capture the value shown for the com.rsa.db.dba.password for use later.
rsaadmin@am83p:~> cd /opt/rsa/am/utils
rsaadmin@am83p:/opt/rsa/am/utils> ./rsautil manage-secrets -a get com.rsa.db.dba.password
Please enter OC Administrator user name:  <enter Operations Console administrator name>
Please enter OC Administrator password:  <enter Operations Console administrator password>
com.rsa.db.dba.password:  HtZHZz3ohAI9jyZ3yRpq74xJaRi9ba
  1. Go to the pgSQL bin directory.
  2. Connect to the database using the password captured in step 5.
rsaadmin@am83p:/opt/rsa/am/utils> cd ../pgsql/bin
rsaadmin@am83p:/opt/rsa/am/pgsql/bin> ./psql -h localhost -p 7050 -d db -U rsa_dba
Password for user rsa_dba: <enter com.rsa.db.dba.password from above>
psql.bin (9.2.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help
  1. Paste in the SQL query below, replacing the UserID string with your own UserID:

db-# SELECT local_log_time ,instance_id ,client_ip ,server_node_ip ,component_key ,action_key ,action_id ,action_result ,result_key ,actor_id, actor_realm_id ,actor_secdom_id ,actor_idsrc_id ,actor_login_uid ,actor_fname ,actor_lname ,agent_id ,agent_secdom_id ,agent_ip ,agent_name ,agent_type ,authmethod_id ,authmethod_name,arg1 ,arg2 ,arg3 ,arg4 ,arg5 ,arg6 ,arg7 ,arg8 ,arg9 ,arg10 ,more_args FROM rsa_logrep.ims_log_audit_rt WHERE actor_login_uid LIKE 'ABCDEFG%';

Related Articles

Trending Articles

Don't see what you're looking for?