Query to fetch schedule run status

[NupurSood]

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

[robin_wylie · Accepted solution]

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

[NicholasZejdlik]

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

[NupurSood]

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

[NicholasZejdlik]

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