How to Retrieve Log Data from Blue Prism Database

[emshihab]

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.

[harish.mogulluri]

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

[emshihab]

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.

[sumire]

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.

[asilarow]

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

[LeonardoSQueiroz]

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

[sumire]

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.

[emshihab]

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

[emshihab · Accepted solution]

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