SQL query to fetch running automations in control room

[AchyutamMehta]

Hi Team,

Can we fetch the status of the current automation process status using SQL query? For example process xyz running in a control room now and process, abc is in pending status.

What we are interested in fetching is the status of the control room but via SQL directly. 

------------------------------
Achyutam Mehta
Automation Developer
------------------------------

[NicholasZejdlik · Accepted solution]

This should approximate the control room view. The where clause can be adjusted to get specifically the sessions you're looking for.

select top (500)
	s.sessionnumber [ID], 
	p.name [Process],
	rr.name [Resource],
	IsNull(su.username, '[' + su.systemusername + ']') as [User],
	st.description [Status],
	DateAdd(second, s.starttimezoneoffset, s.startdatetime) [Start Time],
	DateAdd(second, s.endtimezoneoffset, s.enddatetime) [End Time],
	s.laststage [Latest Stage],
	DateAdd(second, s.lastupdatedtimezoneoffset, s.lastupdated) [Last Updated]	
from 
	BPASession s 
	left join BPAProcess p on s.processid = p.processid
	left join BPAResource rr on s.runningresourceid = rr.resourceid
	left join BPAUser su on s.starteruserid = su.userid
	left join BPAStatus st on s.statusid = st.statusid 
where 
	st.description not in ('Debugging', 'Archived')
order by
	DateAdd(second, s.starttimezoneoffset, s.startdatetime) desc​

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

[JanPiwowarski]

[BPVSessionInfo] is also possible and unnecessary table joins could be avoided. Something like:

SELECT
si.processname
,format(si.startdatetime,'yyyy-MM-dd HH:mm:ss') as starttime
,isnull(format(si.enddatetime, 'yyyy-MM-dd HH:mm:ss'),'running') as endtime
,si.runningresourcename
,case when si.statusid = 0 then 'Pending'
when si.statusid = 1 then 'Running'
when si.statusid = 2 then 'Terminated'
when si.statusid = 3 then 'Stopped'
when si.statusid = 4 then 'Completed'
when si.statusid = 5 then 'Debugging'
when si.statusid = 6 then 'Archived'
when si.statusid = 7 then 'Stopping'
when si.statusid = 8 then 'Warning' end as 'Status'
,si.sessionnumber
,si.lastupdated
,si.laststage
FROM [BluePrism].[dbo].[BPVSessionInfo] si
where cast(si.startdatetime as date) >= cast(getdate() as date)

------------------------------
Jan Piwowarski
Developer
Commerzbank AG
Europe/London
------------------------------

[John__Carter]

Please be mindful of any additional load you may be adding with external queries. You don't want to affect the performance of your digital workforce with excessive querying of a Production database.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

[EVIPUTI]

@AchyutamMehta Instead of SQL query why dont you try CMD commands to achieve the same ? 
Also i am hoping you are not running these queries on main production database , as you can end up with shutting up the completed prod due to this .

------------------------------
------------------------------
Vipul Tiwari
Senior Process Simplification Developer
Amazon
------------------------------
------------------------------

[NicholasZejdlik]

I disagree; I have been running SQL queries against the Blue Prism production database for a number of years, some of which extract reports and are a little hefty. I don't see a problem with querying the database directly for information. The only caveat is that the database structure may change on newer versions of Blue Prism, although the core tables tend to remain the same. I would only look at query optimization if it is in fact a problem, and I would avoid any hard rule that says not to query the database directly.

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

[AchyutamMehta]

Thanks Vipul,

I am just doing some POC regarding the BP db. I am not running this query frequently running on prod. Could you please suggest how we can achieve the same using cmd?

------------------------------
Achyutam Mehta
------------------------------

[AchyutamMehta]

Thanks Nicholas,

How often we can run query in production in one day? Does it impact prod performance?

------------------------------
Achyutam Mehta
------------------------------

[NicholasZejdlik]

It really depends on the situation, how heavy the queries are, how frequently you run them, what your database server hardware is capable of handling, etc. Personally I wouldn't think too much in depth about it unless you're very frequently running a heavy query. For example, most of the queries I have running against the Blue Prism database are run probably 6-10 times per minute. But these are small queries; things to retrieve the status of robots, check the number of items pending, etc., things that only take one or two logical reads per run. On the other hand, I have dashboard that auto-refreshes every minute, which takes about 35k reads per run, but it entirely unnoticeable performance-wise.

Context is really the deciding factor in determining what you can run and how often. But as the saying goes, premature optimization is the root of all evil. Personally, if I was running either of the queries that myself or Jan came up with, I wouldn't worry about it.

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