topic Re: How to Retrieve Log Data from Blue Prism Database in Product Forum

How to Retrieve Log Data from Blue Prism Database

emshihab — Tue, 26 Mar 2024 13:59:18 GMT

Hi,

I have manually downloaded log data in CSV format from Blue Prism for the purpose of analysis. I have a plan to collect log data just like CSV data from the database. What query should I write to get the data just like csv?

Note that: I have provided the sample data.

Re: How to Retrieve Log Data from Blue Prism Database

harish.mogulluri — Tue, 26 Mar 2024 14:47:42 GMT

Hello emshihab,
Did you get chance to explore data gateway?, If not check Datagateway
https://bpdocs.blueprism.com/bp-6-7/en-us/dg-introduction.htm

Re: How to Retrieve Log Data from Blue Prism Database

emshihab — Wed, 27 Mar 2024 07:14:40 GMT

Hi @harish.mogulluri,

Thank you for your reply. Actually I want to connect Blue Prism Database and Power BI. I have tried. It worked. But I want the data to be exactly the same pattern as when we export the log from Blue Prism (control room recent activity) manually. I'm not sure which SQL query to write.

Re: How to Retrieve Log Data from Blue Prism Database

sumire — Thu, 28 Mar 2024 01:04:42 GMT

Hello,

I am trying to retrieve data from DB as you are.
I used "Data - OLEDB" to retrieve the DB tables.
I also retrieved it using PowerBI.
I wrote a method to narrow down the retrieved table to only the information I want. I wrote it in Japanese, so please read it with machine translation.
https://qiita.com/Sumire_Neko/items/807fbc2e7d1358dca86c
Sorry I didn't write the article in English, I hope it helps.

Re: How to Retrieve Log Data from Blue Prism Database

asilarow — Thu, 28 Mar 2024 10:53:09 GMT

The simplest query you can make is this:

SELECT [logid]
,[sessionnumber]
,[stageid]
,[stagename]
,[stagetype]
,[processname]
,[pagename]
,[objectname]
,[actionname]
,[result]
,[resulttype]
,[startdatetime]
,[enddatetime]
,[attributexml]
,[automateworkingset]
,[targetappname]
,[targetappworkingset]
,[starttimezoneoffset]
,[endtimezoneoffset]
,[attributesize]
FROM BPASessionLog_NonUnicode
WHERE processname = 'ENTER YOUR PROCESS NAME HERE' and startdatetime >= CAST(GETDATE() as date)

Doing so will basically get you the logs for the given process for today.

You can modify the WHERE clause and change the cast function at the end: "startdatetime >= CAST(GETDATE() as date) " to:

startdatetime >= dateadd(day,datediff(day,1,GETDATE()),0)

to get anything greater than yesterday... or set to however many days backward you want to look at, by changing the number 1 to any other whole number (i.e. 100 for the last 100 days).

Re: How to Retrieve Log Data from Blue Prism Database

LeonardoSQueiroz — Thu, 28 Mar 2024 16:10:17 GMT

Hello emshihab,

Check out this video, it can help and it's very detailed: https://www.youtube.com/watch?v=TGZ-tdyB7Wk&ab_channel=OnlineTutorial

Regards

Re: How to Retrieve Log Data from Blue Prism Database

sumire — Fri, 29 Mar 2024 07:57:41 GMT

I would like to add to @asilarow 's advice.
For example, an item with a data type of GUID, such as [logid], cannot be retrieved as is, so we write it like this:
cast (logid as Char(36)) as logid

However, in the case of PowerBI, GUID type items are also converted to text and retrieved, so there is no need to do this.

Re: How to Retrieve Log Data from Blue Prism Database

emshihab — Tue, 02 Apr 2024 07:52:15 GMT

Thank you all for your suggestion. I will take a look and let you know the feedback.

Re: How to Retrieve Log Data from Blue Prism Database

emshihab — Fri, 10 May 2024 08:12:53 GMT

The problem has been solved. I could extract the data from Database using Blue Prism SQL Server connector.