SQL Query - how to find future schedules

[MarekPawlowski]

Hi,
I was able to find that BPAScheduleLog holds schedule that already started, but which table in DB hold the "calendar" of future schedules

[MadhurChopra]

Hey Marek,

Are you looking to figure out when schedules will be triggered? If so, BPAScheduleTrigger is where you can find all available schedules. If you are specifically looking to filter only future schedules, you will need to write a query to join information from BPASchedule and BPAScheduleTrigger. 

Hope this helps!

Regards.

------------------------------
Madhur Chopra
Sr. Product Consultant
Blue Prism - Professional Services
America/Los_Angeles
------------------------------

[MarekPawlowski]

Thanks for hint, it is simple but looks like this
SELECT TOP (1000)
[scheduleid]
,[name]
,[period]
,[startdate]
,[enddate]
,[startpoint]
,[endpoint]
FROM [BPAScheduleTrigger] JOIN [BPASchedule] ON (BPAScheduleTrigger.scheduleid = BPASchedule.id)

where startdate > '2020-08-30'
order by startdate desc

------------------------------
Marek Pawlowski
RPA Developer
Digital Workforce
Europe/Warsaw
------------------------------

[BrentonWestwood]

I have wondered how to generate the information the same as the Control - Timetables information such as the default 'Today & Tomorrow'.   I ran the query as above and there was missing data and no data for schedules that are hourly.    There were no future runs in the results either.   Are we sure about the query?   I ran this to only look for non-retired schedules:    

SELECT
[scheduleid],
[name],
[period],
[startdate],
[enddate],
[startpoint],
[endpoint]
FROM [BPAScheduleTrigger] JOIN [BPASchedule] ON (BPAScheduleTrigger.scheduleid = BPASchedule.id)
WHERE [BPASchedule].retired = 0
ORDER BY [startdate] DESC, [name]

------------------------------
Brenton Westwood
Systems Analyst
Southern Company
------------------------------

[BrentonWestwood]

Note that this query appears to show the past runs but I do not see any future runs in the BPAScheduleTrigger table.   I do see the past runs in the Log table such as using this query:  

SELECT [scheduleid]
,[name]
,[instancetime]
,[firereason]
,[servername]
,[heartbeat]
FROM [BPAScheduleLog] JOIN [BPASchedule] ON (BPAScheduleLog.scheduleid = BPASchedule.id)
WHERE [BPASchedule].retired = 0
ORDER BY [instancetime] DESC, [name]

------------------------------
Brenton Westwood
Systems Analyst
Southern Company
------------------------------

[MarekPawlowski]

To be honest I have the same on another environment....
Dont know how to deal with it, not everything is showing or not even one future schedule...

------------------------------
Marek Pawlowski
RPA Developer
Digital Workforce
Europe/Warsaw
------------------------------

[BrentonWestwood]

@Madhur Chopra ​Is there some stored procedure that needs to be called to generate a list of future schedule runs like is shown in the Control Room - Timetables schedule such as the default "Today and Tomorrow" timetable?   I looked in the Programmability folder on the SQL Server for the Blue Prism database using a SA account and did not see one but it would seem there must be one or some code used to generate a future schedule list.   Please let us know how we may be able to generate such a list because it would be cool.   😉

------------------------------
Brenton Westwood
Systems Analyst
Southern Company
------------------------------

[NicholasZejdlik]

I ran SQL profiler while opening Control Room; it seems the time table is not being calculated via SQL. Blue Prism queries the BPAScheduleTrigger table, and I believe it's calculating the Timetables view based on that.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------