KACE Product Support : Email each recipient when ticket is moved with Rule

Email each recipient when ticket is moved with Rule

I am having some problem where I am able to move a ticket with Rule and modifying some part, but I also need a notification run.

Here are the queries:

Select SQL

select HD_TICKET.*,

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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,

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 ((( HD_STATUS.NAME = 'New') AND HD_TICKET.TITLE like '%Employee Email and Equipment Request Form%') and HD_TICKET.HD_QUEUE_ID = 3 )

UPDATE QUERY

update HD_TICKET, HD_CATEGORY as T5

SET HD_TICKET.HD_QUEUE_ID = 11

and HD_PRIORITY.ID = New

WHERE HD_TICKET.ID = ;

EMAIL EACH RECIPIENT

Subject: [TICK:$ticknum] Assigned to $queuename: $title

Column containing email address: NEWTICKETEMAIL

Message:

$submitter_fname has opened a ticket.

The submission was:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)
Title: $title
Priority: $priority
Status: $status

History: $comments
Please see your ticket at https://kace.company.com/adminui/ticket.php?ID=$ticknum

For Email Each Recipient, I know I haven't declared the variables in Select SQL, but as soon I declare or call from SQL it gives me an error.

Any help?

Thanks

2 Comments [ + ] Show comments

Can you post the error message you get? - svmay 8 years ago
Or can you post how you declare the variables? Where should get an emailnotification? - svmay 8 years ago

Answers (1)

Posted by: svmay 8 years ago

Try this select-sql

select HD_TICKET.*,
                        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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
                        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,
						'yourmailadress@yourcompany.com' AS NEWTICKETEMAIL
                        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 (((  HD_STATUS.NAME = 'New') AND HD_TICKET.TITLE like '%Employee Email and Equipment Request Form%') and HD_TICKET.HD_QUEUE_ID = 3 )

Enter the email address where the notification should be sent.

Comments:

I forgot to mention earlier that I already have one Rule in place which sends out an email notification whenever a ticket is assigned to that queue manually. Will the SQL posted by you not conflict with that one? I will try the SQL query posted and report back. - vnuna 8 years ago
- Who would you like to notify by email? - svmay 8 years ago
  - a group email address - vnuna 8 years ago
  - Is the select in the other rule the same? I think you get two mails instead of one (when the select is the same) - svmay 8 years ago
  - what do you want to happen when a new ticket is created in this queue? - svmay 8 years ago
well I already have a rule in place which sends out an email notification to a group email address. I have a main queue where all tickets fall in "Tech Group" from there tech group user manually assign tickets to their respective queue, "Equipment Request". When they manually assign, I have a rule created under "Equipment Request", as soon as a ticket comes in an email is dispatched to notify a group email address, which is working fine if they manually assign the ticket.

To eliminate this process, I want a Rule which runs every 15mins to look for anything like "Equipment....." and transfer it to the "Equipment Request" from "Tech Group" and sends an email notification to group email address. I was able to create the Rule to move the ticket from "Tech Group" to "Equipment Request", but the Notification Rule which I already have in place seems like not sending email notification, it seems that only works when someone manually assign the ticket to that group not when a Rule is ran.

I hope I was able to explain :) - vnuna 8 years ago
- ok, I hope I have already understand you - sorry english is not my native.
  
  Again for me, that I understand you correctly - you want that a new ticket with a specific title automatically change the queue to "equipment request" and when this happen a group get an mailnotification - right? - svmay 8 years ago
  - I would make a custom rule to change the queue when the title contains - so the ticket change automatically the queue. I would create another custom rule In the queue "equipment request", where send the mail notification for your group when a new ticket is in this queue. - svmay 8 years ago
exactly, thats what I have right now. 1 rule to send mail notification when a ticket is added to the queue, and 2nd in the "tech queue" which changes ticket automatically. but rule to send mail notification in "equipment request" only working when the ticket is assigned manually to the group not if the ticket is being moved by a rule. - vnuna 8 years ago
Here is the Rule which sends mail notification:

SQL
SELECT
-- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- change fields
Unix_Timestamp(C.TIMESTAMP),
C.COMMENT, -- $comment
C.DESCRIPTION, -- $description
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at https://kace.lush.com/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
H.COMMENT, -- $comments
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority
-- about status
S.NAME AS STATUS, -- $status
-- about impact
I.NAME AS IMPACT, -- $impact
-- about category
CAT.NAME AS CATEGORY, -- $category
-- other fields --
HD_QUEUE.NAME AS QUEUENAME, -- $queuename
-- example of static distribution list
'group@company.com' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_ID>
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
AND H.ID = (SELECT Min(HD_TICKET_CHANGE.ID)
FROM HD_TICKET_CHANGE
WHERE HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
JOIN HD_QUEUE_OWNER_LABEL_JT ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID
JOIN USER_LABEL_JT ON HD_QUEUE_OWNER_LABEL_JT.LABEL_ID = USER_LABEL_JT.LABEL_ID
JOIN USER OLIST ON USER_LABEL_JT.USER_ID = OLIST.ID
/* queue */
JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
WHERE
(C.DESCRIPTION LIKE '%Changed Ticket Queue%') AND OWNER.USER_NAME is null
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1

EMAIL EACH RECIPIENT
Subject: [TICK:$ticknum] Assigned to $queuename: $title
Column Containing email address: NEWTICKETEMAIL
Message:
$submitter_fname has opened a ticket.

The submission was:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)
Title: $title
Priority: $priority
Status: $status

History: $comments
Please see your ticket at https://kace.company.com/adminui/ticket.php?ID=$ticknum

This rule is working fine. May be my above SQL has C.DESCRIPTION LIKE '%Changed Ticket Queue% due to which it doesn't email notification when a ticket is moved via rule but only sends when a ticket is manually moved? - vnuna 8 years ago
- I don't really have an answer for it, I doesn't use such a kind of rule. But what you can do - use the comment function at the custom ticketrule who change the queue id per rule and set a comment in this ticket (maybe: "Queue change by rule") and look for tickets with this comment at the select in the custom ticket rule (in queue "equipment request").
  
  I have no other good solution at the moment. But I will look for a matching solution for you. - svmay 8 years ago
  - OK, is there anyway to update C.DESCRIPTION from Update SQL or if I want a rule to add some values to C.DESCRIPTION such as "Changed ticket Queue from" ? - vnuna 8 years ago
  - ok, so how do i update "C.DESCRIPTION" OR "HISTORY" of the ticket with this rule to add "Queue change by rule" so I can use it with another rule to send notification? - vnuna 8 years ago
  - You can add a comment by activate the checkbox "Append comment to ticket" and write the comment in the textfield. - svmay 8 years ago
  - I tried that already and it does add the comment, but the script to send notification is only working on "Ticket Save". May be I need an additional 2nd script to run every 15mins? - vnuna 8 years ago

Answer this question

Posted by:

View more:

KACE Product Support Questions

question

Email each recipient when ticket is moved with Rule

Answers (1)

Posted by:

View more:

Related Questions

Related Links

Related Posts

KACE Product Support

Email each recipient when ticket is moved with Rule

Answers (1)

Posted by:

Don't be a Stranger!

View more:

Related Questions

Related Links

Related Posts

Share