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,
Answers (1)
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
