User Activity report query for Advanced AUthentication

Products

• CA Strong Authentication
• CA Advanced Authentication
• CA Risk Authentication

Issue/Introduction

I need to migrate AA prod data from oracle 12c DB to 19c DB. I noticed one table ARUDSUSERAUDITLOG has a very large size of data(27G). Can I omit this table during the data migration? what is the impact? How can I run the query on older DB?

Environment

Release : 9.1

Component :Strong Authentication

Risk Authentication

DB: Oracle

Resolution

This is the query which is run against the DB to get the User Activity Report. If there is a need to run it against the Old DB you can run it to get the report.

(SELECT    A.TXTIMESTAMP as CREATE_TIME  ,  A.USERID as USERID  ,  A.ACCOUNTTYPE as ACCOUNT_TYPE  ,  A.ACCOUNTID as ACCOUNTID  ,  A.OPERATIONNAME as REQ_TYPE  ,  ORGNAME as ORGNAME  ,  A.RETURNSTATUS as ACTION_STATUS  ,  A.UDSTXID as TXN_ID  ,  A.REASON as REASON  ,  A.CALLERIP as CLIENT_IP_ADDRESS  ,  A.CLIENTTXID as CALLERID     FROM    ARUDSUSERAUDITLOG A  where (   (  ( A.USERID is null OR A.USERID  like '%%' ) AND  A.ORGNAME in ('DEFAULTORG') AND  A.TXTIMESTAMP between 
       to_date('03/28/2022 00:00:00', 'mm/dd/yyyy hh24:mi:ss') 
       AND to_date('03/29/2022 23:59:59', 'mm/dd/yyyy hh24:mi:ss')  )    ))

union all

(SELECT    A.TXTIMESTAMP as CREATE_TIME  ,  A.USERID as USERID  ,  A.ACCOUNTTYPE as ACCOUNT_TYPE  ,  A.ACCOUNTID as ACCOUNTID  ,  A.OPERATIONNAME as REQ_TYPE  ,  ORGNAME as ORGNAME  ,  A.RETURNSTATUS as ACTION_STATUS  ,  A.UDSTXID as TXN_ID  ,  A.REASON as REASON  ,  A.CALLERIP as CLIENT_IP_ADDRESS  ,  A.CLIENTTXID as CALLERID     FROM    ARUDSUSERAUDITLOG_AR A  where (   (  ( A.USERID is null OR A.USERID  like '%%' ) AND  A.ORGNAME in ('DEFAULTORG') AND  A.TXTIMESTAMP between 
       to_date('03/28/2022 00:00:00', 'mm/dd/yyyy hh24:mi:ss') 
       AND to_date('03/29/2022 23:59:59', 'mm/dd/yyyy hh24:mi:ss')  )    ))