PeopleSoft Process Monitoring Script

--------------------------------------------------------------------------------------------------
/*ALL UNIQUE PROCESSES IN THE LAST 30 DAYS THAT RAN LONGER THAN 30 MINUTES NOT INCLUDING RPTBOOK*/
--------------------------------------------------------------------------------------------------
SELECT
A.PRCSINSTANCE,
A.PRCSTYPE,
A.PRCSJOBNAME,
A.PRCSNAME,
B.PRCSJOBSEQ,
A.SERVERNAMERQST,
A.BEGINDTTM,
A.RUNSTATUS,
C.XLATLONGNAME,
CASE TO_CHAR(A.RUNSTATUS)
  WHEN '9' THEN LTRIM(TO_CHAR(A.ENDDTTM - A.BEGINDTTM), '+00000000')
ELSE
  LTRIM(TO_CHAR(SYSDATE - A.BEGINDTTM), '+00000000')
END AS "RUNTIME",
A.RUNCNTLID,
A.OPRID
FROM PSPRCSRQST A, PS_PRCSJOBITEM B, PSXLATITEM C
WHERE 1=1
AND A.PRCSJOBNAME = B.PRCSJOBNAME (+)
AND A.PRCSNAME = B.PRCSNAME (+)
AND A.BEGINDTTM > SYSDATE - 30
AND A.ENDDTTM - A.BEGINDTTM > '0 0:30:0.000000'
AND C.FIELDNAME = 'RUNSTATUS'
AND A.RUNSTATUS = C.FIELDVALUE
AND A.PRCSNAME <> 'RPTBOOK'
AND A.PRCSTYPE <> 'PSJob'
ORDER BY A.PRCSINSTANCE DESC, A.ENDDTTM-A.BEGINDTTM DESC
;
--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
/*CURRENTLY RUNNING PROCESSES AND HOW LONG IT'S BEEN RUNNING*/
--------------------------------------------------------------------------------------------------
SELECT A.PRCSINSTANCE,
A.PRCSTYPE,
A.PRCSJOBNAME,
A.PRCSNAME,
B.PRCSJOBSEQ,
A.SERVERNAMERQST,
C.XLATLONGNAME,
A.BEGINDTTM,
CASE TO_CHAR(A.RUNSTATUS)
  WHEN '9' THEN LTRIM(TO_CHAR(A.ENDDTTM - A.BEGINDTTM), '+00000000')
ELSE
  LTRIM(TO_CHAR(SYSDATE - A.BEGINDTTM), '+00000000')
END AS "RUNTIME", 
A.RUNCNTLID,
A.OPRID
FROM PSPRCSRQST A, PS_PRCSJOBITEM B, PSXLATITEM C
WHERE 1=1
AND A.PRCSJOBNAME = B.PRCSJOBNAME (+)
AND A.PRCSNAME = B.PRCSNAME (+)
AND A.RUNSTATUS = 7
AND C.FIELDNAME = 'RUNSTATUS'
AND A.RUNSTATUS = C.FIELDVALUE
ORDER BY RUNTIME DESC
;
--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
/*DISTINCT PROCESSES THAT RAN OVER 30 MINUTES*/
--------------------------------------------------------------------------------------------------
SELECT DISTINCT(A.PRCSNAME)
FROM PSPRCSRQST A, PS_PRCSJOBITEM B, PSXLATITEM C
WHERE 1=1
AND A.PRCSJOBNAME = B.PRCSJOBNAME (+)
AND A.PRCSNAME = B.PRCSNAME (+)
AND A.BEGINDTTM > SYSDATE - 30
AND A.ENDDTTM - A.BEGINDTTM > '0 0:30:0.000000'
AND A.PRCSTYPE <> 'PSJob'
AND C.FIELDNAME = 'RUNSTATUS'
AND A.RUNSTATUS = C.FIELDVALUE
;
--------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------
/*NUMBER OF TIMES THAT A PROCESS RAN OVER 30 MINUTES*/
--------------------------------------------------------------------------------------------------
SELECT A.PRCSNAME, COUNT(A.PRCSNAME)
FROM PSPRCSRQST A, PS_PRCSJOBITEM B, PSXLATITEM C
WHERE 1=1
AND A.PRCSJOBNAME = B.PRCSJOBNAME (+)
AND A.PRCSNAME = B.PRCSNAME (+)
AND A.BEGINDTTM > SYSDATE - 30
AND A.ENDDTTM - A.BEGINDTTM > '0 0:30:0.000000'
AND C.FIELDNAME = 'RUNSTATUS'
AND A.RUNSTATUS = C.FIELDVALUE
GROUP BY A.PRCSNAME
ORDER BY COUNT(A.PRCSNAME) DESC
;
--------------------------------------------------------------------------------------------------


--------------------------------------------------------------------------------------------------
/*CURRENTLY RUNNING PROCESSES AND HOW LONG IT'S BEEN RUNNING*/
--------------------------------------------------------------------------------------------------
SELECT A.PRCSINSTANCE, A.PRCSTYPE, A.PRCSJOBNAME, A.PRCSNAME, C.XLATLONGNAME, B.PRCSJOBSEQ, A.SERVERNAMERQST, A.BEGINDTTM, SYSDATE - A.BEGINDTTM AS "RUNTIME", A.RUNCNTLID, A.OPRID
FROM PSPRCSRQST A, PS_PRCSJOBITEM B, PSXLATITEM C
WHERE 1=1
AND A.PRCSJOBNAME = B.PRCSJOBNAME (+)
AND A.PRCSNAME = B.PRCSNAME (+)
--AND A.RUNSTATUS = 7
AND C.FIELDNAME = 'RUNSTATUS'
AND A.RUNSTATUS = C.FIELDVALUE
ORDER BY RUNTIME DESC;
--------------------------------------------------------------------------------------------------