000030691 - How to run a SQL query report on several UserIDs with a wildcard

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

Article Content

Article Number000030691
Applies ToRSA Product Set: SecurID
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.1.0
Platform: Linux
Platform (Other): null
O/S Version: Suse Linux
Product Name: null
Product Description: null
IssueSecurity console - Reporting - Reports has the ability to search for or filter on a specific UserID, but does not have a Wildcard ability, so we cannot search for all users whose UserID starts with a group prefix, e.g. UserID = ETF*.
Specifically we had a case where and Admin API that read from AD was inserting trailing spaces onto the UserID, so we could not evern report on this specific UserID, e.g. UserID = 'ETFAXDK ' because the Security Console drops trailing spaces by default. Would like ability to wildcard search/filter on Reports generated in the Security Console
ResolutionWait for RFE AM-29316
NotesModify the following SQL statement for an Authentication Activity Custom report for a UserID with a WildCard.  This specific UserID was created with a trailing space, then modified in Security Console so that Trailing Space was removed, so the Security Console report could not find the UserID with the trailing space unless it was unfiltered.
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 'ETFDLRG%';