How to extract the existing Schedules for each bots from BP databases?

[KalaiventhanLAK]

​​​Hi Team,

We need a data in below format to extract from BP Database (as already scheduled in BP Control room for an existing bots)
Can you please hep us, what is the syntax/Script/query to extract from BP database.

Opp name   Username   Machine hostname   Start time    End time   Frequency


Regards
Kalaiventhan

[John__Carter]

You can get an overview of scheduled tasks with a query such as this. Note this query excludes all but minutely and hourly schedules (unittype).

SELECT
s.[name] AS schedule,
tk.[name] AS task,
p.[name] AS process,
ts.resourceName AS [resource],
tr.[period] * CASE tr.unittype WHEN 6 THEN 60 WHEN 1 THEN 60*60 ELSE 0 END AS frequencyMinutes,
tr.startdate,
DATEPART(minute, tr.startdate) AS startTimeOffsetMinutes,
CONVERT(varchar, DATEADD(s, tr.startpoint, 0), 114) AS activePeriodStartTime,
CONVERT(varchar, DATEADD(s, tr.endpoint, 0), 114) AS activePeriodEndTime
FROM BPASchedule s
INNER JOIN BPAScheduleTrigger tr ON s.ID=tr.scheduleID
INNER JOIN BPATask tk ON s.ID = tk.scheduleID
INNER JOIN BPATaskSession ts ON tk.ID = ts.taskID
INNER JOIN BPAProcess p ON ts.processID = p.processID
WHERE tr.usertrigger=1 AND tr.mode=1 AND tr.unittype in (1, 6)
ORDER BY s.[name], tk.[name​]

And something like this can give you information on how sessions ran.

SELECT sessionnumber, processname, runningresourcename,  startdatetime, enddatetime, t.[description] AS [status]
FROM BPVSessioninfo s
INNER JOIN BPAStatus t ON s.statusid=t.statusid
ORDER BY sessionnumber DESC​

[EVIPUTI]

AutomateC.exe /sso /viewschedreport { days } { date } /dbconnname { database } /format csv 

This should give you a report directly when called from CMD.