topic RE: SQL Query in Product Forum

SQL Query

NarendraArvapal — Thu, 21 Nov 2019 10:18:00 GMT

Hi Team,

How to get the Schedule name, Process name, Resource name and Work Item details by using SQL Query from Blueprism Data base.
------------------------------
Narendra Arvapalli
RPA developer
Genpact
Asia/Kolkata
------------------------------

RE: SQL Query

vivek.kumar.goel — Thu, 21 Nov 2019 11:12:00 GMT

Table Name for Schedule: [BPASchedule]
Table Name for Processes: [BPAProcess]
Table Name for Resource Names:[BPAResource]
Table Name for Work Items: Combination of [BPAWorkQueueItem] and [BPAWorkQueue]

------------------------------
Vivek Goel
RPA Architect
Asia/Singapore
+6594554364
https://www.rpatools.com/
------------------------------

RE: SQL Query

NarendraArvapal — Thu, 21 Nov 2019 13:24:00 GMT

Thank you for your response. My requirement is to get Process Name,Resource Name, Resource Status, Start time, End time, Exception reason, Schedule Name. Queue name & No.of Pending items in the Queue from a sql Query. Can you please suggest an query to get all those details in a table.

I am able to find relationship between [BPASession] and {[BPAProcess] OR [BPAResource]}
but i am unable to find relationship between [BPASession] and {[BPASchedule] OR [BPAWorkQueueItem] OR [BPAWorkQueue]}

Below is the query from which I have received some details.

Select TOP 10 BPAResource.name AS System,BPAProcess.name AS [Process Name],BPASession.startdatetime AS [Start Time],BPASession.enddatetime AS [StopTime],BPAStatus.description AS [Status],BPAResource.status AS [Resource Status]
from BPASession INNER JOIN BPAResource ON BPASession.starterresourceid = BPAResource.resourceid AND
BPASession.runningresourceid=BPAResource.Resourceid INNER Join BPAProcess On BPASession.processid=BPAProcess.processid
INNER Join BPAStatus ON BPASession.statusid=BPAStatus.statusid

------------------------------
Narendra Arvapalli
RPA developer
Genpact
Asia/Kolkata
------------------------------

RE: SQL Query

CoryLavigne — Fri, 22 Jul 2022 20:58:00 GMT

Has anyone found a solution for this? I am having the same issue.

Thank you,
Cory Lavigne

------------------------------
Cory Lavigne
Staff Analyst
Gap Inc.
America/Denver
------------------------------

RE: SQL Query

PabloSarabia — Mon, 25 Jul 2022 08:27:00 GMT

Hi @Cory Lavigne

For the three first fields, here is the query:

SELECT SCHEDULE.name as [Schedule name]
	, PROCESS.name AS [Process name]
	, TASKSESSION.resourcename AS [Resource Name]

FROM BPASchedule SCHEDULE
	INNER JOIN BPATaskSession TASKSESSION ON SCHEDULE.initialtaskid = TASKSESSION.taskid
	INNER JOIN BPAProcess PROCESS ON TASKSESSION.processid = PROCESS.processid

But, I don't understand the part of "Work Item".

What you want to get out is that work item was done by which schedule?

Hope this helps you!

See you in the Community, bye 🙂

------------------------------
Pablo Sarabia
Solution Manager & Architect
Altamira Assets Management
Madrid
------------------------------

RE: SQL Query

Satish1414 — Mon, 25 Jul 2022 12:42:00 GMT

Hi Narendra,

You can pull the report from the queue
Step 1: Select any queue item from the respective queue
Step2:Select all
Step3:Right Click and select current view as report from DB

The above report produces the resource name and all
Hope this helps

Regards,
Satish Gunturi
Senior Consultant
Ignite IPA Pvt Ltd.

------------------------------
Satish Gunturi
------------------------------