Unable to run Administrator Activity Report-Raw query needed

Products

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

Issue/Introduction

When we try to run the administrator activity report we receive the error "

Database error while processing the report query. Please try again later.

This is a for a high priority audit, can you please provide the DB query used for this report so we can run it directly.

Environment

Release : 9.1

Component : Strong Authentication -> Admin console

Database - Oracle

Resolution

Below query can be used to run the Administrator activity report directly from Database if Admin console is having some issue and report can not be run from there.

(SELECT    AAL.TIMESTAMP as CREATE_TIME  ,  AAL.ADMINID as ADMINID  ,  ADMINORGNAME as ADMIN_GROUP_NAME  ,  AAL.LOGINIP as LOGINIP  ,  AAL.TRANSACTIONID as TXN_ID  ,  AAL.ACTION as ACTION  ,  AAL.STATUS as ACTION_STATUS  ,  AAL.REASON as REASON  ,  AAL.TARGETUSERID as USERID  ,  TARGETORGNAME as TARGET_GROUP  ,  AAL.RESOURCENAME as RESOURCENAME  ,  AAL.SESSIONID as SESSIONID  ,  AAL.INSTANCEID as INSTANCEID     FROM    ARADMINAUDITTRAIL AAL ,   ARADMINUSER AU ,   (SELECT APR.ROLENAME AS BASEROLENAME, APR.ORDERID AS BASEROLEORDERID, ACR.ROLENAME AS CUSTOMROLENAME, ACR.ORDERID AS CUSTOMROLEORDERID FROM ARADMINPREDEFINEDROLE APR FULL OUTER JOIN ARADMINCUSTOMROLE ACR ON APR.ROLENAME = ACR.ROLENAME) ROLETABLE  where (   (  AAL.ADMINID  like '%%' AND  AAL.ADMINORGNAME in ('DEFAULTORG') AND  AAL.ADMINID = AU.ADMINID AND  AAL.ADMINORGNAME = AU.ORGNAME AND  (AU.ROLENAME = ROLETABLE.BASEROLENAME OR AU.ROLENAME = ROLETABLE.CUSTOMROLENAME) AND  ( ROLETABLE.BASEROLEORDERID >= 2					
						OR
						ROLETABLE.CUSTOMROLEORDERID >= 2 ) AND  AAL.ADMINREFID = (SELECT ADMINREFID FROM ARADMINUSER WHERE ADMINID=AAL.ADMINID AND ORGNAME=AAL.ADMINORGNAME) AND  AAL.TIMESTAMP between 
							to_date('06/01/2022 00:00:00', 'mm/dd/yyyy hh24:mi:ss') 
							AND to_date('07/07/2022 23:59:59', 'mm/dd/yyyy hh24:mi:ss')  ) )   )
							
							union all 
							
							 (SELECT    AAL.TIMESTAMP as CREATE_TIME  ,  AAL.ADMINID as ADMINID  ,  ADMINORGNAME as ADMIN_GROUP_NAME  ,  AAL.LOGINIP as LOGINIP  ,  AAL.TRANSACTIONID as TXN_ID  ,  AAL.ACTION as ACTION  ,  AAL.STATUS as ACTION_STATUS  ,  AAL.REASON as REASON  ,  AAL.TARGETUSERID as USERID  ,  TARGETORGNAME as TARGET_GROUP  ,  AAL.RESOURCENAME as RESOURCENAME  ,  AAL.SESSIONID as SESSIONID  ,  AAL.INSTANCEID as INSTANCEID     FROM    ARADMINAUDITTRAIL_AR AAL ,   ARADMINUSER AU ,   (SELECT APR.ROLENAME AS BASEROLENAME, APR.ORDERID AS BASEROLEORDERID, ACR.ROLENAME AS CUSTOMROLENAME, ACR.ORDERID AS CUSTOMROLEORDERID FROM ARADMINPREDEFINEDROLE APR FULL OUTER JOIN ARADMINCUSTOMROLE ACR ON APR.ROLENAME = ACR.ROLENAME) ROLETABLE  where (   (  AAL.ADMINID  like '%%' AND  AAL.ADMINORGNAME in ('DEFAULTORG') AND  AAL.ADMINID = AU.ADMINID AND  AAL.ADMINORGNAME = AU.ORGNAME AND  (AU.ROLENAME = ROLETABLE.BASEROLENAME OR AU.ROLENAME = ROLETABLE.CUSTOMROLENAME) AND  ( ROLETABLE.BASEROLEORDERID >= 2					
						OR
						ROLETABLE.CUSTOMROLEORDERID >= 2 ) AND  AAL.ADMINREFID = (SELECT ADMINREFID FROM ARADMINUSER WHERE ADMINID=AAL.ADMINID AND ORGNAME=AAL.ADMINORGNAME) AND  AAL.TIMESTAMP between 
							to_date('06/01/2022 00:00:00', 'mm/dd/yyyy hh24:mi:ss') 
							AND to_date('07/07/2022 23:59:59', 'mm/dd/yyyy hh24:mi:ss') ) ))