SQL tables to obtain SC user profile info

Products

• CA Service Catalog

Issue/Introduction

Trying to find out what are the tables that are called to generate the User Profile info in SC.

Need to write a SQL query to grab the First Name, Last Name, User ID, Email, Address, Business Units and Roles entries (especially the one designated as Default) and the Business Units and Authorization Levels info. 

How is the Contact info linked to the BU Roles and Authorization Levels?

Contact info should be via ca_contact. Location info should be via ca_location. BU Role should be via usm_contact_domain_role. BU Authorization Level should be via usm_contact_domain.

What table links ca_contact to usm_contact_domain_role?

Environment

Release : 17.3

Component : CA SERVICE CATALOG

Resolution

Objective was to try to generate a report of all the business units contacts are associated with and their set default business unit.

link the ca_contact and usm_contact_domain_role tables based on the userid field