Clarity Service Management

Expand all | Collapse all

Query to get Roles Associated with Analyst Contacts

Jump to Best Answer
  • 1.  Query to get Roles Associated with Analyst Contacts

    Posted 01-08-2018 08:57 AM

    Hello Team,

     

    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.

     

    Thanks

    ArunavaS



  • 2.  Re: Query to get Roles Associated with Analyst Contacts
    Best Answer

    Posted 01-08-2018 09:38 AM

    Hi Aruna,

     

    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.



  • 3.  Re: Query to get Roles Associated with Analyst Contacts

    Posted 01-08-2018 09:53 AM

    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_List
    FROM ca_contact CNT RIGHT JOIN usp_cnt_role USCNRL ON CNT.contact_uuid=USCNRL.contact
    LEFT JOIN usp_role USRL ON USRL.id= USCNRL.role_obj

     

    Thanks

    Arunava S



  • 4.  Re: Query to get Roles Associated with Analyst Contacts

    Posted 01-08-2018 10:29 AM

    Thanks for your reply.

     

    Glad that I was able to help you.



  • 5.  Re: Query to get Roles Associated with Analyst Contacts

    Posted 12-05-2018 12:46 PM

    Greetings ArunavaS,

     

    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.

     

    Thanks ArunavaS

    Regards,

    Carlos Ramirez