Null Value In Termination Reason from SQL Query

[mradul.agrawal]

Dear Expert,

I am trying to get the 'Terminated Data' from BluePrism with ExceptionMessage using SQL Query but, I am getting 'Null' and 'Unexpected Termination of Session' in ExceptionMessage Column. 

Before is the Query I have used. 

SELECT
si.processname
,format(si.startdatetime,'yyyy-MM-dd HH:mm:ss') as starttime
,isnull(format(si.enddatetime, 'yyyy-MM-dd HH:mm:ss'),'running') as endtime
,si.runningresourcename
,case when si.statusid = 2 then 'Terminated'end as 'Status'
,si.sessionnumber
,si.lastupdated
,si.laststage
,si.exceptionmessage
FROM [BluePrism].[dbo].[BPVSessionInfo] si
where si.statusid = 2 and cast(si.startdatetime as date) = DATEADD(DAY, -1, cast(getdate() as date)) order by processname

Can anyone help why I am getting 'Null' Value in exception message or any other query Should I try ?

------------------------------
Mradul Agrawal
------------------------------

[chrispotvin]

Hi Mradul, 

Try joining the SessionInfo table on the SessionLog table. I would caution that running this query in a dashboard as a live connection to the production database will have unintended consequences to the Blue Prism application. If this query is intended to just pull a report, then ensure that you run it during off hours. The number of rows and data that the SessionLog table can contain is massive.  Often times the Exception if it is an internal Exception will appear in the result of the session log. 

Something like this:

SELECT
si.processname
,format(si.startdatetime,'yyyy-MM-dd HH:mm:ss') as starttime
,isnull(format(si.enddatetime, 'yyyy-MM-dd HH:mm:ss'),'running') as endtime
,si.runningresourcename
,case when si.statusid = 2 then 'Terminated'end as 'Status'
,si.sessionnumber
,si.lastupdated
,si.laststage
,si.exceptionmessage
,sl.result
,sl.exceptionmessage
FROM [BluePrism].[dbo].[BPVSessionInfo] si
JOIN [BluePrism].[dbo].[BPVSessionLog_NonUnicode] sl
ON si.sessionnumber = sl.sessionnumber
where si.statusid = 2 and cast(si.startdatetime as date) = DATEADD(DAY, -1, cast(getdate() as date)) order by processname

------------------------------
Christopher Potvin
Senior RPA Developer
SS&C Blue Prism
Canada
------------------------------

[mradul.agrawal]

Hi Chris,

Thanks for your suggestion

I tried to execute the Query using JOIN from SessionLogs But, I got Invalid Column Error .

"Msg 207, Level 16, State 1, Line 12
Invalid column name 'exceptionmessage'. 

I checked the Database for 'BPASessionlog_NonUnicode and there is no column assigned with ''exceptionmessage'. 

When I remove the Syntax [,sl.exceptionmessage], I am able to execute query but, Again I am getting 'NULL' value in Result Column and Exceptionmessage. 

I tried to check the entire data from BPASession and I noticed that there also I am getting 'NULL' value in ExceptionType, Exceptionmessage, Result.

------------------------------
Mradul Agrawal
------------------------------