search cancel

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

book

Article ID: 203168

calendar_today

Updated On:

Products

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

Issue/Introduction

We are seeing errors while generating the Analyze Transactions Report of CA Risk Authentication, we would like to know the query used underneath and run it directly on the DB and see if it returns the results.

Environment

Release : All supported AA releases

RiskMinder( Arcot RiskFort) CA Risk Authentication

Cause

N/A

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='DEFAULTORG'        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='DEFAULTORG'        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.