Risk Authentication/RiskFort Analyze Transactions Report is failing - Provide the raw SQL query to run against the DB

Products

• CA Advanced Authentication - Strong Authentication (AuthMinder / WebFort)
• CA Strong Authentication
• CA Risk Authentication
• CA Advanced Authentication

Issue/Introduction

Errors are observed while generating the Analyze Transactions Report of CA Risk Authentication, What is the query used for this report?

Environment

Release : All supported AA releases

RiskMinder( Arcot RiskFort) CA Risk Authentication

Database : Oracle

Resolution

Here is the query which you can use for the Analyze transactions report.

You can change the OrgName  (AA Organization name) and the dates as per your need.

SELECT RESULT_UNTRUSTEDIP,RESULT_USERDEVICEASSOCIATED,FRAUDSTATUS,LINESPEED,COUNTRY,RESULT_RFCMB_CHNGDEV,DEVICETYPE,USERNAME,RESULT_ADDONRULES,RESULT_ZONE_HOPPING,RF_VERSION,RESULT_EVALCALLOUT,PARENTUSERID,DEVICEIDSTATUS,RESULT_SIGCHECK_NUMTOTAL,RESULT_RFCMB_NEWDEV,ADVICEID,RESULT_TRUSTEDIP,SECAUTHRESULT,MOBILITY_INDEX,RESULT_TRUSTEDAGGREGATOR,RESULT_SIGCHECK,MATCHEDRULE,TXID,RESULT_USER_VELOCITY,EXTENSIBLEELEMENTS,RESULT_EXCEPTION,RESULT_SIGCHECK_NUMMATCH,IP_ROUTINGTYPE,ALERT_STATUS,BROWSER,SCORE,ACCOUNTTYPE,MODEL_SCORE,ORGNAME,STATE,DATELOGGED,RESULT_RFCMB_MATCH,CONNECTIONTYPE,ANONYMIZERTYPE,RESULT_DEVICEIDCHECK,CHANNELNAME,ACCOUNTID,RESULT_RFCMB_FRAUD,RESULT_USERKNOWN,INSTANCEID,RESULT_UNTRUSTEDCOUNTRY,OS,CLIENTIPADDRESS,RESULT_DEVICE_VELOCITY,CITY,ACTION,AGGREGATORNAME,DEVICEIDOUT    FROM ARRFSYSAUDITLOG    WHERE         ORGNAME='ORG'        AND    (DATELOGGED BETWEEN      to_date('10/07/2020 00:00:00', 'mm/dd/yyyy hh24:mi:ss') AND to_date('11/11/2020 23:59:59', 'mm/dd/yyyy hh24:mi:ss') )  AND TXNTYPE = 1  

UNION ALL

SELECT RESULT_UNTRUSTEDIP,RESULT_USERDEVICEASSOCIATED,FRAUDSTATUS,LINESPEED,COUNTRY,RESULT_RFCMB_CHNGDEV,DEVICETYPE,USERNAME,RESULT_ADDONRULES,RESULT_ZONE_HOPPING,RF_VERSION,RESULT_EVALCALLOUT,PARENTUSERID,DEVICEIDSTATUS,RESULT_SIGCHECK_NUMTOTAL,RESULT_RFCMB_NEWDEV,ADVICEID,RESULT_TRUSTEDIP,SECAUTHRESULT,MOBILITY_INDEX,RESULT_TRUSTEDAGGREGATOR,RESULT_SIGCHECK,MATCHEDRULE,TXID,RESULT_USER_VELOCITY,EXTENSIBLEELEMENTS,RESULT_EXCEPTION,RESULT_SIGCHECK_NUMMATCH,IP_ROUTINGTYPE,ALERT_STATUS,BROWSER,SCORE,ACCOUNTTYPE,MODEL_SCORE,ORGNAME,STATE,DATELOGGED,RESULT_RFCMB_MATCH,CONNECTIONTYPE,ANONYMIZERTYPE,RESULT_DEVICEIDCHECK,CHANNELNAME,ACCOUNTID,RESULT_RFCMB_FRAUD,RESULT_USERKNOWN,INSTANCEID,RESULT_UNTRUSTEDCOUNTRY,OS,CLIENTIPADDRESS,RESULT_DEVICE_VELOCITY,CITY,ACTION,AGGREGATORNAME,DEVICEIDOUT    FROM ARRFSYSAUDITLOG_AR    WHERE         ORGNAME='ORG'        AND    (DATELOGGED BETWEEN      to_date('10/07/2020 00:00:00', 'mm/dd/yyyy hh24:mi:ss') AND to_date('11/11/2020 23:59:59', 'mm/dd/yyyy hh24:mi:ss') )  AND TXNTYPE = 1 order by DATELOGGED DESC;

Additional Information

None.