List processes and objects

[PvD_SE]

Hi folks,

I need to produce a list of the following:

• All of our processes
• All of our objects
• All environment variables

For each item, I need the latest date it was modified. I assume the easiest way to get this data would be to access the BP database by SQL.

While I am very familiar with SQL in other environments, I have no clue as to how to access the BP database, or which tables and columns to get the data from.

My questions:

• How do I access the BP database with SQL from a BP process/object?
• What tables in the BP database contain data such as
  • Process/object name?
  • Folder?
  • Date last modified?
• Are there any significant table changes between versions 6.5 and 6.10?

Happy coding!
---------------
Paul
Sweden

[ewilson]

Hi @PvD_SE,

When it comes to the BP database, I generally use SQL Server Management Studio or Microsoft's BCP utility (if I'm trying to export a lot of data from it). If you want to connect to it from a process then you'll probably want to make use of the Data - SQL Server VBO​.

The process/VBO information is mostly contained in a table named <schema>.BPAProcess (ex. dbo.BPAProcess). Here's what it looks like:


The ProcessType column tells you whether it's a process or an object, the name column gives you the name, and the lastmodifieddate columns will give you the date that it was last changed/saved. However, this table doesn't give you the folder/group info.

There's an existing view in the database, which may work better for you, named BPVGroupedProcessObjects. This table contains the folder name, the process/object name, their IDs, and last modified information. Here's what it looks like:


So I think querying the BPVGroupedProcessObject view may work best for you, but if you wanted to work with the BPAProcess table you would then need to perform a join with the BPAGroup (contains details of the actual folders/groups like groupid and name) and BPAGroupProcess (contains groupid and processid) table in order to map processes/objects to folders.

Regarding the structure of the database between 6.5 and 6.10, there are some differences, but I don't think there's anything significant related to the tables you would need to query to gather the information you're looking for.

Cheers,
Eric

[devneetmohanty07]

Hi @PvD_SE,

As you stated perfectly, the approach to get this list in a precise way would be to go through the SQL database. The SQL database can be accessed using the following business object from DX Exchange: ​Function for Data - SQL Server - 10.0.0

For this VBO to work, you would need some information in order to establish a connection first such as Server Name and related username and password if you are using SQL Authentication approach otherwise if you are using Windows Authentication approach they are not needed and you can leave the username & password fields blank. You can check about that from a relevant database team if your organisation has that otherwise if you have the direct access then you can view the same from you SQL Server Management Studio application.

To use the VBO you must first set the connection details via the Set Connection action with the below parameters:


In order to know the details that you asked below are the tables which you need to refer to:

• All of our processes - BPAProcess
• All of our objects - BPAProcess
• All environment variables - BPAEnvironmentVar

In order to get the processes, objects and environment variables first you can use the below SQL query in your Get collection action:

For fetching list of processes:

SELECT P.name,P.description,P.version,P.createdate,P.lastmodifieddate, G.name AS [Folder Name]
FROM BPAProcess P INNER JOIN BPAGroupProcess GP ON P.processid = GP.processid INNER JOIN BPAGroup G ON GP.groupid = G.id
WHERE ProcessType = 'P'

For fetching list of objects:

SELECT P.name,P.description,P.version,P.createdate,P.lastmodifieddate, G.name AS [Folder Name]
FROM BPAProcess P INNER JOIN BPAGroupProcess GP ON P.processid = GP.processid INNER JOIN BPAGroup G ON GP.groupid = G.id
WHERE ProcessType = 'O'

For fetching list of environment variables:

SELECT name, datatype, value, description
FROM BPAEnvironmentVar

Explanation:

Environment Variables are stored in one table only are pretty straightforward to fetch from BPAEnvironmentVar table.

However, for BPAProcess we have a 'ProcessType' column which indicates whether the stored record is for a process or an object since we have both processes and objects stored under one table which are categorized by either the value 'O' or 'P' for the 'ProcessType' column in this table.

When we consider the folder groups to be also picked up, they reside in a separate table called as BPAGroup and the mapping for each group to a process resides in BPAGroupProcess table so what I am essentially doing is matching the 'processid' field from BPAProcess table to any reference in BPAProcessGroup table and then from there I again create a reference to BPAGroup table using 'groupid' field this time.


Coming to the last part ideally few features were introduced that I can recall directly such as queue snapshots and error screenshots between these versions I think so you may have additional tables and fields to incorporate those. Maybe someone from Blue Prism team might have a document handy to capture all these details.

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[PvD_SE]

@ewilson @devneetmohanty07 Thanks for your swift and ​​complete replies. Much appreciated.

[SuhasDhongade1]

I Know its old thread but I have a question around the App Modeller elementes.

I want to make sure no object keep any value for "Web Page Address" element. 

what will be the DB query or any alternate solution to find that? ( Navigating manually is big no)  

[PvD_SE]

Hi Suhas,

Don't know about the SQL, but as an alternative you could make a release including all of your objects and then scan the XML for 'https' or for the element you mention.

Happy coding!
---------------
Paul
Sweden

[ewilson]

@Suhas Dhongade 

I don't recall the actual element name in the XML, but if you export a VBO with an app model and that element populated you'll be able to find it by looking at the XML in a text editor. From there, you can craft a SQL query to run against the database. Something like this:

SELECT [dbo].[BPAProcess].[processxml] FROM [dbo].[BPAProcess] WHERE ([dbo].[BPAProcess].[ProcessType] = 'O' AND [AttributeID] <> 1) AND ([processxml] LIKE 'INSERT ELEMENT NAME HERE');

Cheers,

Eric

[SuhasDhongade1]

Thank  Paul and Eric,

@ewilson Thank you for query however that gives me minimal details.

At the end, I was able to capture the attribute and object names, but I was unable to capture the precise element name in which the attribute was present.

DROP TABLE IF EXISTS #tempWorkQueueItem1;

SELECT
    CONVERT(XML, P.processxml) AS 'xmldata' INTO #tempWorkQueueItem1
from
    bpaprocess P
WHERE
    processtype = 'O'
    and P.NAME like '% YOUR OBJECT NAME %'
SELECT
    *
from
    (
        SELECT
       [xmldata].query(N'(//element)') AS [AllElemets],
            [xmldata].value(N'(//element/@name)[1]', 'nvarchar(max)') AS [ElementName],
            [xmldata].query(
                N'(//attribute[@name="wPageAddress"]/ProcessValue[@value !=""])'
            ) as [WebAddress]
        FROM
            #tempWorkQueueItem1		
    ) T
where
    [WebAddress] is not null
    and cast([WebAddress] as varchar(max)) != ''
 
DROP TABLE #tempWorkQueueItem1