User completing Data Verification for batch

[MagnusBrangstru]

Hi, 
I am looking to export the user name of the user, who completes/submits a batch from data verification stage. I am looking to have this data exported to a data item so that I can utilize it in the subsequent process steps.

Any help on this is greatly appreciated!

/Magnus

------------------------------
Magnus Brangstrup
------------------------------

[Ben.Lyons1]

Hi Magnus,

Unfortunately this information is not readily available. But we are in the process of adding new actions to the VBO, so please feel free to add it via the ideas button in the Content menu at the top of the page.

Thanks 

------------------------------
Ben Lyons
Senior Product Specialist - Decipher
SS&C Blue Prism
UK based
------------------------------

[MagnusBrangstru]

Hi Ben, thanks for getting back to me. 

Doesn't the data exist in the decipher database somewhere?

There is a report for data verification which is exportable. Can we query the database somehow instead, and join it with the batch name to get the verifier's username? 

Thanks

------------------------------
Magnus Brangstrup
------------------------------

[dsa206]

Hi Ben,

If you can tell us in which Table can we find the User column in the User Productivity - Verification, then our problem is solved. Thanks!

------------------------------
Dexter Antonio
------------------------------

[Ben.Lyons1]

Hi,

That's not a single table in the database, so it requires a few joins to get the request info.

Prior to sharing this I must advise that running SQL commands against the Decipher database is not supported, and any issues or errors arising with Decipher following this may not be supported.

I recommend running this against the reporting database (if enabled) to minimise the risk and load on your Decipher database.

SELECT SessionTask.BatchId
	  ,Batch.Name 'Batch Name'
      ,UserData.UserName 'UserName'
      ,UserRole.Name 'Role Name'
  FROM [DecipherServerDbReporting].[dbo].[SessionTask]
  LEFT JOIN UserRole On SessionTask.RoleId = UserRole.Id
  LEFT JOIN Batch On SessionTask.BatchId = Batch.Id
  LEFT JOIN UserSession On SessionTask.UserSessionId = UserSession.Id
  LEFT JOIN UserData On UserSession.UserId = UserData.Id
  WHERE SessionTask.Status = 'Finished'
  AND SessionTask.RoleId = 30

You may need to update the RoleId, this is aiming at the Verify role.

This query has not been tested outside of my personal instance and may require additional parameters to best work in your environment.

Thanks

------------------------------
Ben Lyons
Senior Product Specialist - Decipher
SS&C Blue Prism
UK based
------------------------------

[MagnusBrangstru]

Hi Ben, 

Your help is much appreciated, thank you very much! 
We have tested the query in our environment and it appears to return exactly what we are looking for. 
Once again, thank you

Br Magnus

------------------------------
Magnus Brangstrup
------------------------------