ARD Hub - SQL Statement to pull User information for each project

Products

• CA Agile Requirements Designer

Issue/Introduction

In ARD, is there a SQL statement that we can run to generate a report providing project name and user email against each project and role for each user?

 For example:

Project name	Administrators	Editors	Viewers
Project_Name	User_name@example_domain	User_name@example_domain	User_name@example_domain

 

Telemetry service does show this information. 

Environment

All Supported releases of ARD Hub

Resolution

It is possible to get this information from a SQL query. In the Keycloak database, you can use the following:

select KC.NAME as Project, UE.USERNAME, UE.EMAIL, KR.NAME as Role from keycloak.CLIENT KC JOIN keycloak.KEYCLOAK_ROLE KR ON KC.ID = KR.CLIENT JOIN keycloak.USER_ROLE_MAPPING UR ON UR.ROLE_ID = KR.ID JOIN keycloak.USER_ENTITY UE ON UE.ID = UR.USER_ID WHERE KC.DESCRIPTION = 'project' ORDER BY 2;