query to give workflow statistics like source count,target count,start time and end time of each sessions

I have one workflow which contain five sessions. I am looking for a query by using informatica repository tables/views which give me output like below. I am not able to get a query which give me desired result. workflow-names session-names source-count target-count session-start time session-end time.

Solution

If you have access to Repository metadata tables, then you can use below query

Metadata Tables used in query:

OPB_SESS_TASK_LOG
OPB_TASK_INST_RUN
OPB_WFLOW_RUN

Here the Repository user is INFA_REP, and workflow name is wf_emp_load.

SELECT w.WORKFLOW_NAME,
       t.INSTANCE_NAME,
       s.SRC_SUCCESS_ROWS,
       s.TARG_SUCCESS_ROWS,
       t.START_TIME,
       t.END_TIME
FROM INFA_REP.OPB_SESS_TASK_LOG s
INNER JOIN INFA_REP.OPB_TASK_INST_RUN t 
  ON s.INSTANCE_ID=t.INSTANCE_ID
AND s.WORKFLOW_RUN_ID=t.WORKFLOW_RUN_ID
INNER JOIN INFA_REP.OPB_WFLOW_RUN w 
  ON w.WORKFLOW_RUN_ID=t.WORKFLOW_RUN_ID
WHERE w.WORKFLOW_RUN_ID =
    (SELECT MAX(WORKFLOW_RUN_ID)
     FROM INFA_REP.OPB_WFLOW_RUN
     WHERE WORKFLOW_NAME='wf_emp_load')
ORDER BY t.START_TIME

Output

+---------------+---------------+------------------+-------------------+--------------------+--------------------+
| WORKFLOW_NAME | INSTANCE_NAME | SRC_SUCCESS_ROWS | TARG_SUCCESS_ROWS |     START_TIME     |      END_TIME      |
+---------------+---------------+------------------+-------------------+--------------------+--------------------+
| wf_emp_load   | s_emp_load    |               14 |                14 | 10-JUN-18 18:31:24 | 10-JUN-18 18:31:26 |
| wf_emp_load   | s_emp_revert  |               14 |                14 | 10-JUN-18 18:31:27 | 10-JUN-18 18:31:28 |
+---------------+---------------+------------------+-------------------+--------------------+--------------------+