/build/static/layout/Breadcrumb_cap_w.png

Is it possible to print the FULL_NAME from a Custom 'User' type field selection?

We have a queue that we use for onboarding new employees. In this queue there is a custom 'User' type field, from which the submitter can select an existing user whose access the new employee's should mirror. We use a rule to send emails to distribution lists so that the appropriate new accounts can be created. I would like to have this email include the selection from the custom 'User', so recipients know whose access they should mirror. 

Using the following SQL returns the user's ID, but I need the FULL_NAME. However, I'm not sure how to define and call this information. Any assistance would be greatly appreciated!


--other fields

HD_TICKET.CUSTOM_FIELD_VALUE7 as VALUE7,


0 Comments   [ + ] Show comments

Answers (1)

Posted by: KevinG 3 years ago
Red Belt
0

If you selected CUSTOM_7 in Service Desk › Configuration › Queue Customization | "Your Service Desk Queue name".

The actual data for that field is stored in HD_TICKET.CUSTOM_FIELD_VALUE6  as this starts at Zero.

In the SQL statement "as CUSTOM_7_USER_NAME" is what the column name will be in the report for this custom field, you can rename this title.


Here is some SQL that I believe based on your description, should get you started and can be modified to meet your needs.


select HD_TICKET.ID,
       HD_TICKET.TITLE ,
       HD_PRIORITY.NAME as PRIORITY,
       HD_CATEGORY.NAME as CATEGORY,
       HD_STATUS.NAME as STATUS,
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,
       (select FULL_NAME from USER where HD_TICKET.CUSTOM_FIELD_VALUE6 = USER.ID) as CUSTOM_7_USER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE != 'closed'
order by OWNER_NAME


Comments:
  • Thank you for this information. I have accounted for the numbering starting at 0 in my rule. And the rule returns the correct numerical user ID associated with the user name selected from the custom 'User' type field.

    I reviewed the information you provided, and it looks like I am using a different syntax in my existing rule (which matches all of my other rules). I'm also learning SQL as I go (using what was built out by our previous KACE admin). My existing rule is below. Do you have any suggestions on how I can modify the line that defines the value for FULL_NAME to work in my rule? Your help is most appreciated!

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    SELECT
    -- ticket fields
    HD_TICKET.ID, -- $id
    HD_TICKET.ID AS TICKNUM, -- $ticknum
    HD_TICKET.TITLE, -- $title
    DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
    DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified

    -- change fields
    C.COMMENT, -- $comment
    C.DESCRIPTION, -- $description
    GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
    H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://k1000.mydomain.com/admin/ticket.php?ID=',H.HD_TICKET_ID,'\n')
    ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history

    -- about the updater
    UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
    UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
    UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
    IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional

    -- about the owner
    OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
    OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
    OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
    IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user

    -- about the submitter
    SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email

    -- about priority
    P.NAME AS PRIORITY, -- $priority

    -- about status
    S.NAME AS STATUS, -- $status

    -- about impact
    I.NAME AS IMPACT, -- $impact

    -- about category
    CAT.NAME AS CATEGORY, -- $category

    -- other fields
    HD_TICKET.CUSTOM_FIELD_VALUE0 as VALUE0,
    HD_TICKET.CUSTOM_FIELD_VALUE1 as VALUE1,
    HD_TICKET.CUSTOM_FIELD_VALUE4 as VALUE4,
    HD_TICKET.CUSTOM_FIELD_VALUE6 as VALUE6,
    HD_TICKET.CUSTOM_FIELD_VALUE7 as VALUE7,
    HD_TICKET.CUSTOM_FIELD_VALUE8 as VALUE8,
    HD_TICKET.CUSTOM_FIELD_VALUE19 as VALUE19,

    -- -- example of static distribution list
    'me@mydomain.com' AS NEWTICKETEMAIL -- $newticketemail

    FROM HD_TICKET
    /* latest change **/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
    -- AND C.ID=<CHANGE_ID>
    /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
    /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
    /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
    /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
    /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
    /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
    /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

    WHERE
    -- C.NOTIFY_USERS LIKE '%comment_change%'
    C.DESCRIPTION NOT LIKE '%CREATED%'
    AND HD_TICKET.CUSTOM_FIELD_VALUE16 = '1'

    /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1 - abryantfsb 3 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Link

Related Links

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ