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

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Write an OTS rule that emails that group address whenever a ticket owner is changed from TICKET_OWNER to someone else.
Answered 02/24/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
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
Answered 02/24/2011 by: pedter
Orange Belt

Please log in to comment
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.
Answered 02/24/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
we got this working, thanks!
Answered 02/25/2011 by: pedter
Orange Belt

  • I am trying to do the same thing. Can you send me the entire query? What is here does not work. chuber@martinsnet.com
Please log in to comment
Answer this question or Comment on this question for clarity

Share