I have setup a process that is run hourly (could be more frequent) to look for such issues and send an email about issues occurring in the environment (or other environments). Here are my queries to get the Count of issues where a Warning is being shown for a process run (now update for the process in over 5 minutes - like the as seen on the System Setting screen in Blue Prism).
' ************ Possible Frozen Sessions ************
-- Count of Possible Frozen Sessions (warning status - no stage updates in over 5 minutes)
SELECT COUNT(*) AS RESULT
FROM dbo.BPVSessionInfo INNER JOIN dbo.BPAStatus ON dbo.BPVSessionInfo.statusid = dbo.BPAStatus.statusid
WHERE dbo.BPVSessionInfo.enddatetime IS NULL
AND DATEDIFF(minute,dbo.BPVSessionInfo.lastupdated, SYSDATETIME()) > 5
-- Details
SELECT
dbo.BPVSessionInfo.processname AS Process,
dbo.BPVSessionInfo.starterresourcename AS Resource,
dbo.BPAStatus.description AS Status,
FORMAT(dbo.BPVSessionInfo.startdatetime, 'MM-d-yyyy hh:mm:ss tt') AS [Start Date Time],
FORMAT(dbo.BPVSessionInfo.enddatetime, 'MM-d-yyyy hh:mm:ss tt') AS [End Date Time],
dbo.BPVSessionInfo.laststage AS [Latest Stage],
FORMAT(dbo.BPVSessionInfo.lastupdated, 'MM-d-yyyy hh:mm:ss tt') AS [Stage Started],
DATEDIFF(minute,dbo.BPVSessionInfo.lastupdated, SYSDATETIME()) AS [Minutes Since Last Stage Update]
FROM dbo.BPVSessionInfo INNER JOIN dbo.BPAStatus ON dbo.BPVSessionInfo.statusid = dbo.BPAStatus.statusid
WHERE dbo.BPVSessionInfo.enddatetime IS NULL
AND DATEDIFF(minute,dbo.BPVSessionInfo.lastupdated, SYSDATETIME()) > 5
ORDER BY dbo.BPVSessionInfo.startdatetime DESC, [End Date Time], Resource, Process, Status
------------------------------
Brenton Westwood
Systems Analyst
Southern Company
------------------------------
