CA Service Management

  • 1.  Need query for getting report for servicedesk Users list.

    Posted Jun 06, 2018 08:59 AM

    Hi Team,

    I need details of all contact list in CASDM with specific group , role type ,access type, user id , email id, User name, Location, Organization, Phone Number.Focus on group, roll type, access type.

     

    Regards,

    Jagatjyoti Dash



  • 2.  Re: Need query for getting report for servicedesk Users list.

    Broadcom Employee
    Posted Jun 06, 2018 09:53 AM

    Jagatjyoti........

     

    Please see if the SQL query in the following Community Post assists you in creating the required query:

    SQL query for contact details page 



  • 3.  Re: Need query for getting report for servicedesk Users list.

    Posted Jun 06, 2018 02:45 PM

    Hi Paul,

    About group is it providing the report ?I need access type, group and role for user . Access type and group field is there .Please suggest about group field.

     

    Query:


    SELECT ca_contact.first_name,
    ca_contact.last_name ,
    ca_contact.userid,
    ca_contact.middle_name,
    ca_contact.inactive as Active,
    ca_contact.email_address,
    ca_contact_type.name as ContactType,
    usp_contact.c_acctyp_id,
    acctyp_v2.sym as AccessType,
    usp_role.name as rolename


    FROM ca_contact INNER JOIN ca_contact_type ON ca_contact_type.id = ca_contact.contact_type INNER JOIN usp_contact ON ca_contact.contact_uuid = usp_contact.contact_uuid
    INNER JOIN acctyp_v2 ON usp_contact.c_acctyp_id = acctyp_v2.id INNER JOIN usp_cnt_role ON usp_contact.contact_uuid= usp_cnt_role.contact
    INNER JOIN usp_role ON usp_cnt_role.role_obj = usp_role.id


    where first_name!='' and ca_contact.inactive = '0'

     

     

    Regards,

    Jagatjyoti Dash



  • 4.  Re: Need query for getting report for servicedesk Users list.

    Posted Jun 07, 2018 04:26 AM

    Hi Paul,

    I need Location, Organization,Telephone number,Group fields with this report.

    Can you please suggest how to add.

     

    Regards,

    Jagatjyoti Dash



  • 5.  Re: Need query for getting report for servicedesk Users list.
    Best Answer

    Broadcom Employee
    Posted Jun 07, 2018 04:02 PM

    Try this query:

     

    SELECT ca_contact.first_name,
    ca_contact.last_name ,
    ca_contact.userid,
    ca_contact.inactive as Active,
    ca_contact.email_address,
    ca_contact_type.name as ContactType,
    ca_location.location_name as Location,
    ca_Organization.org_name as Organization,
    acctyp_v2.sym as AccessType,
    usp_role.name as Rolename

    FROM ca_contact INNER JOIN ca_contact_type ON ca_contact_type.id = ca_contact.contact_type INNER JOIN usp_contact ON ca_contact.contact_uuid = usp_contact.contact_uuid
    INNER JOIN acctyp_v2 ON usp_contact.c_acctyp_id = acctyp_v2.id INNER JOIN usp_cnt_role ON usp_contact.contact_uuid= usp_cnt_role.contact
    INNER JOIN usp_role ON usp_cnt_role.role_obj = usp_role.id
    INner JOIN ca_location on ca_contact.location_uuid = ca_location.location_uuid
    Inner Join ca_organization on ca_organization.organization_uuid = ca_contact.organization_uuid



  • 6.  Re: Need query for getting report for servicedesk Users list.

    Broadcom Employee
    Posted Jun 15, 2018 08:54 AM

    dasja02 

    Do you have any additional questions regarding this topic?

     

    If not, please mark one of the provided answers as Correct so that this thread can be closed.