/build/static/layout/Breadcrumb_cap_w.png

email to group on owner change

What we have in place so far.

The default category on creation of a new ticket is set to "please select a category..." (categories are hidden from end users) This category is set to auto assign ownership to the TICKET_OWNER user account. This user account has the groupemail@domain.com email address associated with it. This accomplishes the initial notification to the group of technicians I want to notify.

The next step is where I'm having issues. After these technicians are notified and one of them changes ownership to themselves, I’d like the other technicians (either by their specific email addresses, or the groupemail@domain.com email address) to be notified that the ticket has a valid owner and no the TICKET_OWNER dummy user account.

Make sense?

Any help would be greatly appreciated.

-Peter

0 Comments   [ + ] Show comments

Answers (4)

Posted by: airwolf 13 years ago
Red Belt
0
Write an OTS rule that emails that group address whenever a ticket owner is changed from TICKET_OWNER to someone else.
Posted by: pedter 13 years ago
Orange Belt
0
I apologize, however being new to the kbox I'm not sure exactly how to do that.
I have created a new rule for this queue.

the selection was based on Owner username != RE_TICKET_OWNER
and Status != CLOSED

here is that select statement:
select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', ')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
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
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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 ((( (1 not in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.USER_NAME = 'RE_TICKET_OWNER')) ) AND HD_STATUS.NAME != 'CLOSED') and HD_TICKET.HD_QUEUE_ID = 2 )

now that i have the correct tickets selected, on the next screen I'm not sure what to put in the 'Send an email for each row' 'Email Column" field
in the subject i have "[TICK$id] $title (Ownership taken)"


what do I put in the Email column field? Or am I supposed to do this some other way?

thanks again!
email body I have "The ownership has been taken for this ticket, better luck next time"

Peter
Posted by: airwolf 13 years ago
Red Belt
0
You need your select query to only look at updated tickets. Try this out, but be warned that I just slapped it together and haven't tested it.

Select Query
select distinct HD_TICKET.ID,
HD_TICKET.OWNER_ID as OWNER_ID,
HD_TICKET.SUBMITTER_ID as SUBMITTER_ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
OWNER.USER_NAME as OWNER_NAME,
OWNER.FULL_NAME as OWNER_FULLNAME,
OWNER.EMAIL as OWNER_EMAIL,
UPDATER.USER_NAME as UPDATERNAME,
UPDATER.EMAIL as UPDATEREMAIL,
SUBMITTER.FULL_NAME as SUBMITTER_FULLNAME,
'groupemail@domain.com' as GRPEMAIL
from (HD_TICKET)
left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID>
left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID
left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
left join USER OLD_OWNER on OLD_OWNER.ID = HD_TICKET_CHANGE.OWNER_ID
left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID
left join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
where HD_TICKET_CHANGE.HD_TICKET_ID= HD_TICKET.ID
and OLD_OWNER.FULL_NAME like 'RE_TICKET_OWNER'


Your email column is $grpemail. Make sure you change the email address to the correct group address in the query.
Posted by: pedter 13 years ago
Orange Belt
0
we got this working, thanks!

Comments:
  • I am trying to do the same thing. Can you send me the entire query? What is here does not work. chuber@martinsnet.com - cphuber51 10 years ago
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

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