SQL Query

[NarendraArvapal]

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

[VivekGoel]

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

[NarendraArvapal]

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

[CoryLavigne]

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

[PabloSarabia]

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

[Satish1414]

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