topic RE: Query to fetch schedule run status in Product Forum

Query to fetch schedule run status

NupurSood — Fri, 06 Nov 2020 08:11:00 GMT

Hello

Would someone please be able to suggest a query that can be run on Blue Prism SQL server to get the schedule run status. What we are interested in is something like the report in the Scheduler section of control room but via SQL directly.

Thanks

------------------------------
Nupur Sood
Research Associate
S&P
Asia/Kolkata
------------------------------

RE: Query to fetch schedule run status

robin_wylie — Fri, 06 Nov 2020 15:38:00 GMT

Here is one we use to check for schedules that had errors in the previous 24 hours.
You can modify as you need

select s.Name, SLE.TerminationReason, DATEADD(hour,-12,ASL.starttime) as starttime ,DATEADD(hour,-12, ASL.endtime) as endtime

from [dbo].[BPAScheduleLogEntry] SLE
inner join [dbo].[BPVAnnotatedScheduleLog] ASL
on SLE.[schedulelogid] = ASL.[id]
inner join [dbo].[BPASchedule] S
on S.id = ASL.scheduleid
and entrytime >= DATEADD(hour,-24, GETDATE())
and terminationreason is not null
and entrytype = '5'
and endtime is not null
order by ASL.starttime

------------------------------
Robin Wylie
Senior Analyst
TransAlta
America/Edmonton
------------------------------

RE: Query to fetch schedule run status

NicholasZejdlik — Fri, 06 Nov 2020 15:48:00 GMT

This should roughly duplicate Blue Prism's view. You can add a where clause or having clause to filter the results, and you can pull the full schedule log out by looking up the id on BPAScheduleLogEntry. This will include deleted schedules in the results.

Select 
    L.id,

    Case When S.name Is Not Null Then S.name Else S.deletedname End [Name],

    L.servername,

    Min(Case When E.entrytype = 0 Then E.entrytime Else Null End) [Start_Time],

    Max(Case When E.entrytype = 1 Or E.entrytype = 2 Then E.entrytime Else Null End) [End_Time],

    Min(Case When E.entrytype = 2 Or E.entrytype = 5 Or E.entrytype = 8 Then 1 Else Null End) [Had_Error]

From 
    BPASchedule S 
    Inner Join BPAScheduleLog L On 
        S.id = L.scheduleid 
    Inner Join BPAScheduleLogEntry E
		On 
        L.id = E.schedulelogid

Group By

    L.id,

    Case When S.name Is Not Null Then S.name Else S.deletedname End,

    L.servername

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

RE: Query to fetch schedule run status

NupurSood — Fri, 06 Nov 2020 18:49:00 GMT

Thank you so much for your response.
This is helpful. Are you please able to help me which table stores details on entrytype, just to get perspective on what different values stand for.

Thanks
Nupur

------------------------------
Nupur Sood
Research Associate
S&P
Asia/Kolkata
------------------------------

RE: Query to fetch schedule run status

NicholasZejdlik — Fri, 06 Nov 2020 19:13:00 GMT

I don't think there are any tables that store details on the entry type, but from what I've been able to ascertain, I think it goes like this:
0 = Start of schedule
1 = End of schedule, success
2 = End of schedule, exception
3 = Start of task
4 = End of task, success
5 = End of task, exception
6 = Start of session
7 = End of session, success
8 = End of session, exception

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