ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

User Activity report query for Advanced AUthentication

book

Article ID: 238094

calendar_today

Updated On:

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')  )    ))