topic RE: SQL Query - how to find future schedules in Product Forum

SQL Query - how to find future schedules

MarekPawlowski — Fri, 28 Aug 2020 08:30:00 GMT

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

RE: SQL Query - how to find future schedules

MadhurChopra — Sat, 29 Aug 2020 01:55:00 GMT

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
------------------------------

RE: SQL Query - how to find future schedules

MarekPawlowski — Mon, 31 Aug 2020 12:54:00 GMT

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
------------------------------

RE: SQL Query - how to find future schedules

bjwestwo — Mon, 31 Aug 2020 20:41:00 GMT

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
------------------------------

RE: SQL Query - how to find future schedules

bjwestwo — Mon, 31 Aug 2020 20:50:00 GMT

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
------------------------------

RE: SQL Query - how to find future schedules

MarekPawlowski — Thu, 03 Sep 2020 12:01:00 GMT

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
------------------------------

RE: SQL Query - how to find future schedules

bjwestwo — Thu, 03 Sep 2020 13:42:00 GMT

@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
------------------------------

RE: SQL Query - how to find future schedules

NicholasZejdlik — Fri, 04 Sep 2020 14:56:00 GMT

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
------------------------------