I need a help here. We need to figure out the list of roles associated with each analyst contacts in CA ServiceDesk. If someone help with the SQL query or atleast the table name from where I can get this information will be very helpful.
You can fetch the details of roles from below query
pdm_extract -f "select * from usp_cnt_role"
the usp_cnt_role table holds the relationship between contact and roles
Hope this helps.
Great! Thanks for the help Maheshwar_Kusuma . I am able to do a slight modification and get my desired list.
SELECT (CNT.first_name + ' ' + CNT.last_name) AS Analyst_Name, USRL.name AS Role_ListFROM ca_contact CNT RIGHT JOIN usp_cnt_role USCNRL ON CNT.contact_uuid=USCNRL.contactLEFT JOIN usp_role USRL ON USRL.id= USCNRL.role_obj
Thanks for your reply.
Glad that I was able to help you.
The query was useful to obtain this information. For better practices this should be enable in Service Desk only for Admin Roles, in order to obtain this information for example an audit in the system.