Failed Session Run Details(Yesterday)

Displays session details, by repository and folder, for sessions that started yesterday and failed. This report is the first node in a workflow associated with the Failed Session Statistics (Yesterday) primary report.

Click on the text to copy the query report to your clipboard.


select "SERVER_NAME",
"FIRST_ERROR_MSG",
"FAILED_ROWS",
"LAST_ERROR_CODE",
"SESSION_TIMESTAMP_SUB",
"SUCCESSFUL_SOURCE_ROWS",
"SESSION_INSTANCE_NAME",
"SESSION_TIMESTAMP",
"SUCCESSFUL_ROWS",
"SESSION_LOG_FILE",
"WORKFLOW_NAME",
"ACTUAL_START",
"SUBJECT_AREA",
"FIRST_ERROR_CODE",
"FAILED_SOURCE_ROWS",
"SESSION_NAME",
"ACTUAL_START_SUB",
"MAPPING_NAME",
"LAST_ERROR",
"REPOSITORY_NAME",
"TOTAL_ERR"
from (SELECT  SESSION_REPOSIT_INFO.REPOSITORY_NAME,  TO_CHAR(SESSION_LOG.ACTUAL_START,'DD-MON-YYYY') as ACTUAL_START_SUB,  ALL_SESSIONS.SUBJECT_AREA,  SESSION_LOG.WORKFLOW_NAME,  ALL_SESSIONS.SESSION_NAME,  SESSION_LOG.SESSION_INSTANCE_NAME,  SESSION_LOG.MAPPING_NAME,  SESSION_TASK_INST_RUN.SERVER_NAME,  SESSION_LOG.ACTUAL_START,  SESSION_LOG.SESSION_TIMESTAMP,  SESSION_LOG.SESSION_LOG_FILE as SESSION_LOG_FILE,  SESSION_LOG.FIRST_ERROR_CODE,  SESSION_LOG.FIRST_ERROR_MSG as FIRST_ERROR_MSG,  SESSION_LOG.LAST_ERROR_CODE,  SESSION_LOG.LAST_ERROR as LAST_ERROR,  (SUM(SESSION_LOG.SUCCESSFUL_SOURCE_ROWS)) as SUCCESSFUL_SOURCE_ROWS,  (SUM(SESSION_LOG.FAILED_SOURCE_ROWS)) as FAILED_SOURCE_ROWS,  (SUM(SESSION_LOG.SUCCESSFUL_ROWS)) as SUCCESSFUL_ROWS,  (SUM(SESSION_LOG.FAILED_ROWS)) as FAILED_ROWS,  (SUM(SESSION_LOG.TOTAL_ERR)) as TOTAL_ERR,  (SUM(  CASE    WHEN SESSION_LOG.SESSION_TIMESTAMP IS NOT NULL    THEN (SESSION_LOG.SESSION_TIMESTAMP - SESSION_LOG.ACTUAL_START) * 86400   END)) as SESSION_TIMESTAMP_SUB  FROM  REP_LOAD_SESSIONS ALL_SESSIONS,  REP_SESS_LOG SESSION_LOG,  REP_REPOSIT_INFO SESSION_REPOSIT_INFO,  REP_TASK_INST_RUN SESSION_TASK_INST_RUN  WHERE  (ALL_SESSIONS.SESSION_ID = SESSION_LOG.SESSION_ID AND  ALL_SESSIONS.SESSION_VERSION_NUMBER = SESSION_LOG.TASK_VERSION_NUMBER AND  ALL_SESSIONS.SESSION_ID <> SESSION_REPOSIT_INFO.REPOSITORY_ID AND  SESSION_LOG.SESSION_ID = SESSION_TASK_INST_RUN.TASK_ID AND  SESSION_LOG.INSTANCE_ID = SESSION_TASK_INST_RUN.INSTANCE_ID AND  SESSION_LOG.TASK_VERSION_NUMBER = SESSION_TASK_INST_RUN.TASK_VERSION_NUMBER   AND  SESSION_LOG.WORKFLOW_ID = SESSION_TASK_INST_RUN.WORKFLOW_ID AND  SESSION_LOG.WORKFLOW_VERSION_NUMBER = SESSION_TASK_INST_RUN.VERSION_NUMBER   AND  SESSION_LOG.WORKFLOW_RUN_ID = SESSION_TASK_INST_RUN.WORKFLOW_RUN_ID AND  SESSION_TASK_INST_RUN.TASK_TYPE = 68 ) AND  (TO_CHAR(SESSION_LOG.ACTUAL_START,'DD-MON-YYYY') =  TO_CHAR(SYSDATE-1, 'DD-MON-YYYY') AND  CASE    WHEN SESSION_LOG.RUN_STATUS_CODE <= 9    THEN    CASE     WHEN SESSION_LOG.RUN_STATUS_CODE = 1     THEN 'Succeeded'     ELSE     CASE      WHEN SESSION_LOG.RUN_STATUS_CODE = 2      THEN 'Disabled'      ELSE      CASE       WHEN SESSION_LOG.RUN_STATUS_CODE = 3       THEN 'Failed'       ELSE       CASE        WHEN SESSION_LOG.RUN_STATUS_CODE = 4        THEN 'Stopped'        ELSE        CASE         WHEN SESSION_LOG.RUN_STATUS_CODE = 5         THEN 'Aborted'         ELSE         CASE          WHEN SESSION_LOG.RUN_STATUS_CODE = 6          THEN 'Running'          ELSE          CASE           WHEN SESSION_LOG.RUN_STATUS_CODE = 7           THEN 'Suspending'           ELSE           CASE            WHEN SESSION_LOG.RUN_STATUS_CODE = 8            THEN 'Suspended'            ELSE            CASE             WHEN SESSION_LOG.RUN_STATUS_CODE = 9             THEN 'Stopping'            END           END          END         END        END       END      END     END    END    ELSE    CASE     WHEN SESSION_LOG.RUN_STATUS_CODE = 10     THEN 'Aborting'     ELSE     CASE      WHEN SESSION_LOG.RUN_STATUS_CODE = 11      THEN 'Waiting'      ELSE      CASE       WHEN SESSION_LOG.RUN_STATUS_CODE = 12       THEN 'Scheduled'       ELSE       CASE        WHEN SESSION_LOG.RUN_STATUS_CODE = 13        THEN 'UnScheduled'        ELSE        CASE         WHEN SESSION_LOG.RUN_STATUS_CODE = 14         THEN 'Unknown'         ELSE         CASE          WHEN SESSION_LOG.RUN_STATUS_CODE = 15          THEN 'Terminated'         END        END       END      END     END    END   END = 'Failed' )  GROUP BY  SESSION_REPOSIT_INFO.REPOSITORY_NAME,  TO_CHAR(SESSION_LOG.ACTUAL_START,'DD-MON-YYYY'),  ALL_SESSIONS.SUBJECT_AREA,  SESSION_LOG.WORKFLOW_NAME,  ALL_SESSIONS.SESSION_NAME,  SESSION_LOG.SESSION_INSTANCE_NAME,  SESSION_LOG.MAPPING_NAME,  SESSION_TASK_INST_RUN.SERVER_NAME,  SESSION_LOG.ACTUAL_START,  SESSION_LOG.SESSION_TIMESTAMP,  SESSION_LOG.SESSION_LOG_FILE,  SESSION_LOG.FIRST_ERROR_CODE,  SESSION_LOG.FIRST_ERROR_MSG,  SESSION_LOG.LAST_ERROR_CODE,  SESSION_LOG.LAST_ERROR) "query"
order by "REPOSITORY_NAME", "ACTUAL_START_SUB", "SUBJECT_AREA"