Generate structured user/role/permission reports

[Walter.Koller]

Hi,

I need to generate reports to easily provide the information what user have what roles and which permissions.

The easiest to read would be probably a table with rows as roles and users as columns and a X to mark the spot who has what.
Then a similar report with rows showing permissions and columns show the roles.
Or rows as columns and columns as rows, this will not make a big difference.

I generated one of the built in reports (v6.4) but this is just (at best semi-structured) list and has to prepared first to do anything with the data. (btw is there a way to defined my own reports to be generated?).

I tried to query the DB but for some reasons the joins between BPAUsers, BPAUserRoles, ... don't return any results.

Any hints are much apprecieated,
Thanks

------------------------------
Walter Koller
Solution Manager
Erste Group IT International GmbH
Europe/Vienna
------------------------------

[EmersonF]

Hey @Walter Koller the most you can get in the DB was this information, to be able to pull it from the Bank we would need to map all types of ID and transcribe it in a new table with a foreign key, but see if this helps you.

/****** Script do comando SelectTopNRows de SSMS  ******/
SELECT 
  U.[username] as 'Username', 
  U.[passwordexpirydate] 'Password Validate', 
  U.[useremail] 'Email', 
  CASE WHEN U.[isdeleted] = 0 THEN 'User active' ELSE 'No active' END 'Active', 
  U.[lastsignedin] 'Last Login', 
  UR.[name] 'Name Role' 
FROM 
  [Blue Prism].[dbo].[BPAUser] U 
  Inner Join [BPAUserRoleAssignment] URA ON U.userid = URA.userid 
  Inner Join [BPAUserRole] UR ON URA.userroleid = UR.id 
WHERE 
  isdeleted = 0 
  AND username IS NOT NULL
​

​
​

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
+55 (081) 98886-9544
If my answer helped you? Mark as useful!
------------------------------

[Walter.Koller]

Thanks a lot for the quick reply.

The problem with those queries is: I only get one user (it happens to be my own user but I am not sure if this is because of AD authentication or because I am BP system administrator). But we have hmm maybe 50+ users in this environment and they are not part of the query result.
I could not figure out why this query does not work.

------------------------------
Walter Koller
Solution Manager
Erste Group IT International GmbH
Europe/Vienna
------------------------------

[mkumar407]

Hey Walter,

Please try this to get list of users with roles and permissions:
Select z.username,z.[passwordexpirydate],z.[lastsignedin], A.name as [role],e.name as [permission_name], c.name [permission] from dbo.BPAUser Z
join dbo.BPAUserRoleAssignment Y on z.userid = Y.userid
join dbo.BPAUserRole A on Y.userroleid = A.id
join dbo.BPAUserRolePerm B on A.id = B.userroleid
join dbo.BPAPerm C on B.permid = C.id
join dbo.BPAPermGroupMember D on c.id = d.permid
join dbo.BPAPermGroup E on d.permgroupid=e.id
where z.username IS NOT NULL
order by z.username

best regards!

------------------------------
Manish Kumar
------------------------------

[EmersonF]

Got it, can you see the users behind the BP?


------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
+55 (081) 98886-9544
If my answer helped you? Mark as useful!
------------------------------

[EmersonF]

Thanks for this info, i can make the query completed now!

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
+55 (081) 98886-9544
If my answer helped you? Mark as useful!
------------------------------

[Walter.Koller]

Thanks a lot for the full query with all the information I would need.

The problem is, I still can only see the result for one single user out of 135 rows in BPAUser.
But for this user the result seems to be complete.

I have tried the query on our 6.4 and 6.9 repositories but the result is always the same: the same one single user.

This user happens to be my user, with System Administrator rights.
Today I am using another login and still get the same results. So this cannot be related to my Windows account.
Also there are two System Administrators, so user role cannot be the reason either.

Most of our users are organized in groups in BP, in case users in groups might need special treatment.
But there are about 20 users not being in any group but not shown in the results.
The one single user is in a group.

Thanks a lot for your help so far

Edit: we are using SSO for BP user authentication and don't have any user with manual logins. 


------------------------------
Walter Koller
Solution Manager
Erste Group IT International GmbH
Europe/Vienna
------------------------------

[mkumar407]

Hey Walter,  I'm happy to help.

Seems for SSO login there is some different mapping and unfortunately I'm unable to link ssogroup from BPAUserRole with BPAUser🙁.


------------------------------
Manish Kumar
------------------------------

[Walter.Koller]

BPAUserRole.ssogroup refers to the AD Group that authorizes the user

select * from BPAUserRoleAssignment
returns 5 rows (with 135 users and 97 user roles)

When reading your comment and while writings this... I am starting to be afraid and I am thinking there is no link between user and assigned user role in the database at all. BP queries the assignments directly from AD when needed.
BluePrism Team, could this be true?

It would make sense to have always the most current and accurate data in use and not being depending on when the assignments have been updated the last time.
​

------------------------------
Walter Koller
Solution Manager
Erste Group IT International GmbH
Europe/Vienna
------------------------------

[mkumar407]

Let's wait for expert advise from Blue Prism Team.

------------------------------
Manish Kumar
------------------------------