How To Get A Report For User, Tenant, Robot, Roles, and Permissions Using A SQL Query In The Orchestrator database?

News Knowledge Base
, , ,
1

How to get a report for User, Tenant, Robot, Roles, and Permissions using a SQL query in the Orchestrator database?

Issue Description: How to get a report for User, Tenant, Robot, Roles, and Permissions using a SQL query in the Orchestrator database?

Resolution:

Note: First, try to achieve the requirement by the available Orchestrator Rest API endpoints (https://ORCHESTRATOR_HOSTNAME/swagger/index.html). It is suggested to get the help of a Database expert as UiPath never recommends any DB interaction.

Use and re-adapt the below query script based on the internal requirements (in below example the applied permissions are checked for Packages and Libraries for a particular username which have active Robots):

SELECT UsersTable.UserName, UsersTable.EmailAddress AS 'User Email Address', UserRolesTable.UserId, 

CASE WHEN UsersTable.IsActive = 1 THEN 'Active'

ELSE 'Inactive' END AS 'User is Active',

CASE WHEN UsersTable.IsDeleted = 0 THEN 'No'

ELSE 'Yes' END AS 'User is Deleted',

CASE WHEN Roles.TenantId IS NULL THEN 'Host'

    WHEN Roles.TenantId = 1 THEN (SELECT [TenancyName] from [uipath].[dbo].[Tenants] WHERE [Id]=1)

    WHEN Roles.TenantId = 2 THEN (SELECT [TenancyName] from [uipath].[dbo].[Tenants] WHERE [Id]=2)

    WHEN Roles.TenantId = 3 THEN (SELECT [TenancyName] from [uipath].[dbo].[Tenants] WHERE [Id]=3)

    WHEN Roles.TenantId = 4 THEN (SELECT [TenancyName] from [uipath].[dbo].[Tenants] WHERE [Id]=4)

    WHEN Roles.TenantId = 5 THEN (SELECT [TenancyName] from [uipath].[dbo].[Tenants] WHERE [Id]=5)

    WHEN Roles.TenantId = 6 THEN (SELECT [TenancyName] from [uipath].[dbo].[Tenants] WHERE [Id]=6)

    WHEN Roles.TenantId = 7 THEN (SELECT [TenancyName] from [uipath].[dbo].[Tenants] WHERE [Id]=7)

    WHEN Roles.TenantId = 8 THEN (SELECT [TenancyName] from [uipath].[dbo].[Tenants] WHERE [Id]=8)

    WHEN Roles.TenantId = 9 THEN (SELECT [TenancyName] from [uipath].[dbo].[Tenants] WHERE [Id]=9)

    WHEN Roles.TenantId = 10 THEN (SELECT [TenancyName] from [uipath].[dbo].[Tenants] WHERE [Id]=10)

ELSE CONVERT(varchar(10),Roles.TenantId) END AS 'Tenant Name',

RobotsTable.Name AS 'Robot Name', RobotsTable.UserName AS 'Robot UserName',

Roles.DisplayName AS 'Role Name', Roles.Id AS 'Role ID', 

PermissionsTable.Name AS 'Permission',

CASE WHEN PermissionsTable.IsGranted = 0 THEN 'No'

ELSE 'Yes' END AS 'Permission is Granted',

CASE WHEN Roles.[IsStatic] = 0 THEN 'No'

ELSE 'Yes' END AS 'Role is Static',

CASE WHEN Roles.[IsDefault] = 0 THEN 'No'

ELSE 'Yes' END AS 'Role is Default',

CASE WHEN Roles.[IsDeleted] = 0 THEN 'No'

ELSE 'Yes' END AS 'Role is Deleted'

FROM [uipath].[dbo].[UserRoles] AS UserRolesTable

INNER JOIN [uipath].[dbo].[Roles] AS Roles ON UserRolesTable.RoleId=Roles.Id 

INNER JOIN [uipath].[dbo].[Permissions] As PermissionsTable ON UserRolesTable.RoleId=PermissionsTable.RoleId

INNER JOIN [uipath].[dbo].[Users] AS UsersTable ON UsersTable.Id=UserRolesTable.UserId

INNER JOIN [uipath].[dbo].[Robots] AS RobotsTable ON UsersTable.Id=RobotsTable.UserId

AND ((PermissionsTable.Name LIKE '%Packages%' OR PermissionsTable.Name LIKE '%Librar%')) AND UsersTable.UserName='USERNAME' AND (RobotsTable.IsDeleted='0' AND RobotsTable.Enabled='1')

ORDER BY UserRolesTable.UserId, PermissionsTable.[Name]

Example Results:

Note: After the results are extracted in an Excel file, its data records may be filtered and then perform troubleshooting for the Robot's permissions.