Symantec Access Management

Hi

May I kindly get assistants with the query below. Can the query be simplified in order to pull open cases from the Database?

The purpose of this exercise it to get the same amount of open cases from the Admin console as well as the database (Daily Statistics).

CA query found in the logs

Select /*+ index ( arrfcaseauditlog ARRFCASEAUDITLOG_DTCREATED_IDX ) */   SUM( case when prevcasestatus in(0,1) and casestatus=3 THEN 1 ELSE 0 END) as CASESOPENICH,   SUM( case when casestatus = 4 and workflow = 4 THEN 1 ELSE 0 END) as CASESCLOSEDICH,   SUM( case when workflow = 4 THEN 1 ELSE 0 END) as ACTIVITYCOUNTICH,   to_char(DTCREATED  +   120/1440,'MM/dd/yyyy - Dy') as PERIOD   from arrfcaseauditlog where    (DTCREATED BETWEEN      to_date('05/31/2018 22:00:00', 'mm/dd/yyyy hh24:mi:ss') AND to_date('06/30/2018 21:59:59', 'mm/dd/yyyy hh24:mi:ss') )   AND ORGNAME=?    AND txid is null group by to_char(DTCREATED  +   120/1440,'MM/dd/yyyy - Dy') order by PERIOD    

My query

SELECT COUNT(DISTINCT(USERNAME)) FROM ARRFCASEAUDITLOG

WHERE DTCREATED > TO_DATE ('2017-06-20 00:00', 'YYYY-MM-DD HH24:MI') AND DTCREATED < TO_DATE ('2017-06-20 23:59', 'YYYY-MM-DD HH24:MI')

AND CASESTATUS = 2;

My query does not return the same number of open cases compared to the admin console.

 Regards;

Neo

Hi Neo, 

There may be a chance that the AUDIT log might have been archived by log trimming tool or rows deleted by dbprune. when you ran the query. There must be a table name arrfcaseauditlog_AR in your arcotDB did you check that table.

thanks

awijit

Hi Awijit,

I had a look in the arrfcaseauditlog_ar table and the data I was looking for was not there.

The data is usually archived after 2 to 3 months from the arrfcaseauditlog table.

Regards;

Neo

hi Neo, if your query is still not working then i would suggest to open a support case and this requires investigation and testing.