/build/static/layout/Breadcrumb_cap_w.png

K1000 Service Desk: Adding Owner Email

I have a bunch of rules to notify supervisors when tickets sit in a status for a certain period of time. I want the rule to also send an email to the ticket owner. When I add this statement which i stole from another rule:

OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email

I get an error. I assumed it had to do with pulling the owner info from another table and tried to add this join statement to fix it:

LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID

Here is the full query:

 Select 'lwalters@wacoisd.org' AS LISA,

'cfrey@wacoisd.org as CHARLIE,

OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email

S.NAME AS STATUS, T.ID AS ID, T.TITLE AS ISSUE,

U.FULL_NAME AS SUBMITTER, O.FULL_NAME AS OWNER,

T.CREATED AS CREATED, T.MODIFIED AS STALLED

FROM HD_TICKET T

JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)

JOIN USER U ON (U.ID = T.SUBMITTER_ID)

JOIN USER O ON (O.ID = T.OWNER_ID)

WHERE (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'ajimenez')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'trott')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'mmccormick')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'sfelkner')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'ckluk')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'bgamboa')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'rdaniels')

OR (S.NAME = 'Follow-up' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'lmland')

AND T.HD_QUEUE_ID = 1


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: lmland 11 years ago
10th Degree Black Belt
1

I answered my own question. The table HD_TICKET was aliased to T and when I changed HD_TICKET.OWNER_ID" to "T.OWNER_ID" it worked. It's almost like I'm learning SQL.


Comments:
  • I am going through the same pains as I learn SQL on my KACE over the last month.

    It would appear you have the same thing I have in my brain where I just have to say something out loud or read it on a different format to kind of reset my mind.

    BTW - Doing this type of email reminder was made MUCH simpler for me by duplicating the embedded "WAITING OVERDUE" ticket rule and modifying it to my needs. Also making some templates in MYSQL (are you using mYSQL?) for basic selects have helped a lot. - Wildwolfay 11 years ago
  • I'm using MySQL Workbench. I've duplicated the default rules and modified them for other rules. - lmland 11 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

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