KACE Product Support

Hey, is this still true???

I am trying to use code like this:
select
distinct HD_TICKET.ID,
HD_TICKET.CUSTOM_FIELD_VALUE1 AS USERNAME, -- $USERNAME
HD_TICKET.CUSTOM_FIELD_VALUE2 AS PASSWORD, -- $PASSWORD
HD_TICKET.CUSTOM_FIELD_VALUE3 AS EMAIL, -- $EMAIL
HD_TICKET.CUSTOM_FIELD_VALUE4 AS PHONE, -- $PHONE
HD_TICKET.RESOLUTION AS RESOLUTION,
case
when HD_TICKET.SUMMARY like "%Company: some-company1%" then "some-company1@somewhere.com"
when HD_TICKET.SUMMARY like "%Company: some-company2%" then "some-company2@somewhere.com"
when HD_TICKET.SUMMARY like "%Company: some-company3%" then "some-company3@somewhere.com"
when HD_TICKET.SUMMARY like "%Company: some-company4%" then "some-company4@somewhere.com"
when HD_TICKET.SUMMARY like "%Company: some-company5%" then "some-company5@somewhere.com"
else "someone@somewhere.com"
end as NEWTICKETEMAIL -- $newticketemail
from
HD_TICKET
join HD_TICKET_CHANGE c on HD_TICKET.ID = c.HD_TICKET_ID and c.ID = <CHANGE_ID>
left join HD_TICKET_CHANGE_FIELD cf on c.ID = cf.HD_TICKET_CHANGE_ID
join HD_STATUS S on HD_TICKET.HD_STATUS_ID = S.ID
join HD_SERVICE_TICKET st on HD_TICKET.SERVICE_TICKET_ID = st.ID
join HD_SERVICE serv on st.HD_SERVICE_ID = serv.ID
,(select S.`VALUE` ticket_label from SETTINGS S where S.`NAME` = "HD_TICKET_LABEL") settings
where

# HD_TICKET.PARENT_ID = 0 and HD_TICKET.IS_PARENT # Parent Ticket
HD_TICKET.PARENT_ID > 0 and !HD_TICKET.IS_PARENT # Child Ticket
and HD_TICKET.TITLE like "User Accounts%" # title starts with "User Accounts"

and serv.NAME = "Main Employee Process" # of process "Main Employee Process"
and S.STATE = "closed" # on ticket close...
and (
c.DESCRIPTION like concat("%", settings.ticket_label, " Created%")
or ifnull(cf.FIELD_CHANGED, "") = "STATUS_NAME"
) # ...on ticket close
group by
HD_TICKET.ID


and when using these variables in the E-mail that gets sent out:

$USERNAME
$PASSWORD
$EMAIL
$PHONE

$Resolution

$CUSTOM_FIELD_VALUE1
$CUSTOM_FIELD_VALUE2
$CUSTOM_FIELD_VALUE3
$CUSTOM_FIELD_VALUE4



The e-mail just displays:
$USERNAME
$PASSWORD
$EMAIL
$PHONE

$Resolution

$CUSTOM_FIELD_VALUE1
$CUSTOM_FIELD_VALUE2
$CUSTOM_FIELD_VALUE3
$CUSTOM_FIELD_VALUE4


Any info on what I am doing wrong would be much appreciated!

Jason - jct134 2 years ago