Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Blue Prism: Overview of schedule entries' last run state
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment