Failed Session Statistics(Started Yesterday)

This query will displays the total number of sessions that started yesterday and resulted in failed statuses by repository by folder. A session can fail due to read login failure or custom select statement error.

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


select "ACTUAL_START",
"RUN_STATUS_CODE",
"REPOSITORY_NAME",
"INSTANCE_ID",
"SUBJECT_AREA",
"WORKFLOW_NAME"
from (SELECT  SESSION_REPOSIT_INFO.REPOSITORY_NAME,  TO_CHAR(SESSION_LOG.ACTUAL_START,'DD-MON-YYYY')    AS "ACTUAL_START",  ALL_SESSIONS.SUBJECT_AREA,  SESSION_LOG.WORKFLOW_NAME,  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                   AS RUN_STATUS_CODE,  (COUNT(   CASE    WHEN SESSION_LOG.RUN_STATUS_CODE = 3    THEN SESSION_LOG.INSTANCE_ID   END)) AS INSTANCE_ID  FROM  REP_LOAD_SESSIONS ALL_SESSIONS,  REP_SESS_LOG SESSION_LOG,  REP_REPOSIT_INFO SESSION_REPOSIT_INFO  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  (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,  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) "query"
order by "REPOSITORY_NAME", "ACTUAL_START"
>/code>