Blue Prism: Overview of schedule entries' last run state

blue_prism_schedule_overview.sql

-- Lists all active schedules
-- Tested with Blue Prism version 6.4

-- List of all active schedules including Termination Reason (my company's MSSQL version does not support the STRING_AGG function, so this could be prettier)
SELECT bpaschedule.name, 
       logkolonner.instancetime, 
       logentrykolonner.terminationreason 
FROM   bpaschedule 
       CROSS apply (SELECT TOP 1 bpaschedulelog.scheduleid, 
                                 bpaschedulelog.instancetime, 
                                 bpaschedulelog.id AS scheduleLogID 
                    FROM   bpaschedulelog 
                    WHERE  bpaschedulelog.scheduleid = bpaschedule.id 
                    ORDER  BY bpaschedulelog.instancetime DESC) logkolonner 
       CROSS apply (SELECT DISTINCT bpaschedulelogentry.terminationreason 
                    FROM   bpaschedulelogentry 
                    WHERE  bpaschedulelogentry.schedulelogid = 
                           logkolonner.schedulelogid) logentrykolonner 
WHERE  bpaschedule.retired = 0 
       AND bpaschedule.deletedname IS NULL 
ORDER  BY name ASC 

-- List of all active schedules, not showing Termination Reason
SELECT bpaschedule.name,
       logkolonner.instancetime
FROM   bpaschedule
       CROSS apply (SELECT TOP 1 bpaschedulelog.scheduleid,
                                 bpaschedulelog.instancetime
                    FROM   bpaschedulelog
                    WHERE  bpaschedulelog.scheduleid = bpaschedule.id
                    ORDER  BY bpaschedulelog.instancetime DESC) logkolonner
WHERE  bpaschedule.retired = 0
       AND bpaschedule.deletedname IS NULL
-- ORDER  BY instancetime ASC
ORDER  BY name ASC

-- 10 most recent queue items in each queue - sorted by queue name
SELECT BPAWorkQueue.[name]
      ,[keyvalue]
      ,[status]
      ,[exception]
      ,[exceptionreason]
      ,[loaded]
      ,[completed]
  FROM BPAWorkQueue
       CROSS APPLY (SELECT TOP(10) *
                    FROM BPAWorkQueueItem
                    WHERE BPAWorkQueueItem.queueid = BPAWorkQueue.id
  ORDER BY BPAWorkQueueItem.loaded DESC) wqi
  ORDER BY name ASC

-- most recent queue item in each queue - sorted by loading time
SELECT BPAWorkQueue.[name]
      ,[keyvalue]
      ,[status]
      ,[exception]
      ,[exceptionreason]
      ,[loaded]
      ,[completed]
  FROM BPAWorkQueue
       CROSS APPLY (SELECT TOP(1) *
                    FROM BPAWorkQueueItem
                    WHERE BPAWorkQueueItem.queueid = BPAWorkQueue.id
  ORDER BY BPAWorkQueueItem.loaded DESC) wqi
  ORDER BY loaded DESC