KACE Product Support

This can be any field from your query.

The owner_email, submitter_email, cc_list are just there if you use the wizard to generate your SQL.

Cool. After some trial and error, I got it working. But, I'm not clear on something. If I use U3.EMAIL in the "Email Column" field, it does not work. If I use EMAIL, it works. What I don't understand is why does it correctly choose the Approver email, when it could be Owner or Submitter.

Here's my query.


SELECT HD_TICKET.ID, HD_TICKET.TITLE,U3.USER_NAME,U3.EMAIL,APPROVER_ID,APPROVAL,APPROVE_STATE
FROM
(HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
WHERE
HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (((( HD_TICKET.APPROVAL like '%None%') OR HD_TICKET.APPROVAL is null) AND HD_STATUS.NAME not like '%Closed%') and HD_TICKET.HD_QUEUE_ID = 1 )


Its because the email column you choose (U3.Email) in the select is pulling from the join for the approver.

Just for grins, change U3.EMAIL to U2.EMAIL. It'll send it to the submitter.

If you did U1, the owner.

Got it. I assume you can only have one EMAIL field in the select? I.E. What happens if I had U2.EMAIL and U3.MAIL in the select line? Not that I would do this, just curious.

You would have to alias them to avoid confusion.

Something like
U2.EMAIL AS EMAIL2,
U1.EMAIL AS EMAIL1

If you truly are just sending an email as your first post says and there is no update nor comment on the field then if you wanted to send the same rule to send an email to go to the submitter and owner and approver then you could have
select ..., STAKEHOLDERS.EMAIL as STAKEHOLDERS_EMAIL
from
(HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
left join USER STAKEHOLDERS ON STAKEHOLDERS.ID IN (OWNER_ID, SUBMITTER_ID, APPROVER_ID)
...

This query will return up to 3 rows meaning 3 emails will be sent -- one to each "stakeholder". the email column value would be STAKEHOLDERS_EMAIL

This obviously works best if you are sending the same email, but here's a way to send a different email in the same rule:
select ...
case when STAKEHOLDERS.ID=OWNER_ID then concat('Hi ',STAKEHOLDERS.FULL_NAME', you are owner')
when STAKEHOLDERS.ID=SUBMITTER_ID then concat('Hi ',STAKEHOLDERS.FULL_NAME', you are submitter')
when STAKEHOLDERS.ID=APPROVER_ID then concat('Hi ',STAKEHOLDERS.FULL_NAME', you are approver')
else 'error' end as SPECIFIC_BODY,
, STAKEHOLDERS.EMAIL as STAKEHOLDERS_EMAIL
from
(HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
left join USER STAKEHOLDERS ON STAKEHOLDERS.ID IN (OWNER_ID, SUBMITTER_ID, APPROVER_ID)
...

Now you can put $specific_body in the email and it would be different in each email.

But if your doing other things (updates, comments) and particular it's an "on ticket save" rule then it's easiest to stick to one row per select query, otherwise there are other things to keep track of I don't mention here.