Service Desk question
Hi All,
I am kind of new to Kace and hoping someone has done this before me or has some suggestions.
I am trying to send all tier 1 tickets to outside helpdesk team and wondering what might be the best way to accomplish this. The tickets will come in to our queue then our internal helpdesk team will select which tickets that need to go to tier 1 and hopefully send all the information on the ticket and any updates to them as well.
So I am thinking doing by either of these 2 options someone has another idea.
1. creating a ticket owner user for "external team" and assign the ticket to that user once that that happens and tickets is saved they will get all the ticket details with custom rule.
2. Create a custom field "external team" and anytime the internal team selects this custom field to fire off email with custom rule and notify the external team.
But I also want anytime the ticket is updated with any info the external team to get notified so not sure If I can do this with custom field.
Thanks!
0 Comments
[ + ] Show comments
Answers (6)
Answer Summary:
Please log in to answer
Posted by:
Cooltech25
7 years ago
Chuck - how can I change this to anytime owner changes rather than when I assign it to specific owner (sitetest)?
I think I have to change this line but not sure what to - and U1.FULL_NAME like '%sitetest%'
can I do something like and U1.FULL_NAME not like '%null%'
Comments:
-
Just to be clear, do you want a rule that will run anytime the owner changes the ticket, or anytime the owner of the ticket is changed? - chucksteel 7 years ago
-
anytime the owner of the ticket is changed. I am trying to resolve an issue with dual notifications. I am hoping I disable the default owner change notification and use the custom. So that only 1 ticket notification goes out. - Cooltech25 7 years ago
Posted by:
chucksteel
7 years ago
Top Answer
This is the rule I use for owner changes. It includes columns named PREVIOUS_OWNER_EMAIL and NEW_OWNER_EMAIL that can be used for the Column containing email address option.
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,
U4.EMAIL as PREVIOUS_OWNER_EMAIL,
U5.EMAIL as NEW_OWNER_EMAIL
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=<CHANGE_ID>
and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='OWNER_ID'
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 USER U4 on U4.ID = HD_TICKET_CHANGE_FIELD.BEFORE_VALUE
LEFT JOIN USER U5 on U5.ID = HD_TICKET_CHANGE_FIELD.AFTER_VALUE
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 in (select 1 from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)) ) and HD_TICKET.HD_QUEUE_ID = 2 )
Be sure to change the HD_TICKET.HD_QUEUE_ID = 2 to the appropriate queue ID.
Comments:
-
Chuck thank you so much! this is perfect. I am only missing adding any comment or history to the email. What variable can I use to add initial comment? My variables are not working this is what I am getting so far on the email:
Title: Test custom rule
Ticket: TICK:282997
Name: Site Test
Email: xxxx@company.com
Phone: xxx.xxx.xxxx
Submitted by: name
Comment/Details:
Initial Comment: $initial_comment
Last Comment: $comment - Cooltech25 7 years ago-
The above rule doesn't include the coding for the initial comment. You will need to add a few things.
First, this join statement should go with the other join statements, you can put this after the other join to the HD_TICKET_CHANGE table.
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
Then add these line to the columns being selected:
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT,
HD_TICKET_CHANGE.COMMENT as COMMENT,
You can put that after this line:
Q.NAME as QUEUE_NAME, - chucksteel 7 years ago-
Chuck - you are lifesaver! I owe you a drink dude! - Cooltech25 7 years ago
-
Feel free to send some coin:
Bitcoin: 19gdYdv3sSbS3w6YguPwp9CZ368o2JsFzT
Ether:
0xd27CF1cD188286728A30B0aa756926c00A83B049 - chucksteel 7 years ago
Posted by:
chucksteel
7 years ago
I would probably go with option 1. We have a similar setup for printer tickets that are handled by a service contract.
Comments:
-
Thanks for the suggestions Chuck. I was leaning that way as well. I will go ahead with option 1. I will see what custom rule I can come up with to send all the ticket info on the email. - Cooltech25 7 years ago
Posted by:
Cooltech25
7 years ago
I am hoping someone will be able to assist me with this sql. I copied from various posts here but its not working. I am hoping to send this 1 ticket owner all the info on the ticket Initial comment, last comment, submitter name, email and phone as well. If someone already has 1 that works and can share that would be awesome too.
select HD_TICKET.ID, HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE, U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
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, STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
U3.FULL_NAME as UPDATERNAME,
U4.FULL_NAME as INITIALNAME,
UNIX_TIMESTAMP(THISCHANGE.TIMESTAMP),
THISCHANGE.COMMENT,
THISCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.LOCATION AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_TICKET.CUSTOM_FIELD_VALUE0 AS EMPLOYEE_NAME,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE THISCHANGE ON THISCHANGE.HD_TICKET_ID = HD_TICKET.ID and THISCHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
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 = THISCHANGE.USER_ID
left join USER U4 on U4.ID = INITIAL_CHANGE.USER_ID
left join HD_QUEUE on HD_QUEUE.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.OWNER_ID = USER.ID and OWNER_FULLNAME like '%site test%'
and THISCHANGE.DESCRIPTION not like "%Created%"
and HD_TICKET.HD_QUEUE_ID = 1 and HD_STATUS.STATE != 'closed'
select HD_TICKET.ID, HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE, U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
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, STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
U3.FULL_NAME as UPDATERNAME,
U4.FULL_NAME as INITIALNAME,
UNIX_TIMESTAMP(THISCHANGE.TIMESTAMP),
THISCHANGE.COMMENT,
THISCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.LOCATION AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_TICKET.CUSTOM_FIELD_VALUE0 AS EMPLOYEE_NAME,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE THISCHANGE ON THISCHANGE.HD_TICKET_ID = HD_TICKET.ID and THISCHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
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 = THISCHANGE.USER_ID
left join USER U4 on U4.ID = INITIAL_CHANGE.USER_ID
left join HD_QUEUE on HD_QUEUE.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.OWNER_ID = USER.ID and OWNER_FULLNAME like '%site test%'
and THISCHANGE.DESCRIPTION not like "%Created%"
and HD_TICKET.HD_QUEUE_ID = 1 and HD_STATUS.STATE != 'closed'
Comments:
-
There are four joins to the USER table and they are all aliased differently.
U1 = Owner
U2 = Submitter
U3 = This change user
U4 = Initial change user
However, under the where clause you are limiting results to tickets where the owner matches user.id (HD_TICKET.OWNER_ID = USER.ID). At that point the database doesn't know which table to use, because it doesn't know about a table named USER (it only knows about U1, U2, U3, and U4). What you are really trying to match is the ticket owner based on the full name, so this line:
and HD_TICKET.OWNER_ID = USER.ID and OWNER_FULLNAME like '%site test%'
can be simplified to this:
and U1.FULL_NAME like '%site test%' - chucksteel 7 years ago-
Thanks Chuck - Getting syntax error.
mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=<CHANGE_ID> JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_I' at line 26] in EXECUTE(" select HD_TICKET.ID, HD_TICKET.ID as TICKNUM, HD_TICKET.TITLE, U1.USER_NAME as OWNER_NAME, U3.USER_NAME as LASTINPUTNAME, 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, STATE, U1.FULL_NAME as OWNER_FULLNAME, U1.EMAIL as OWNER_EMAIL, U2.USER_NAME as SUBMITTER_NAME, U2.FULL_NAME as SUBMITTER_FULLNAME, U2.EMAIL as SUBMITTER_EMAIL, U3.EMAIL as UPDATEREMAIL, U3.FULL_NAME as UPDATERNAME, U4.FULL_NAME as INITIALNAME, UNIX_TIMESTAMP(THISCHANGE.TIMESTAMP), THISCHANGE.COMMENT, THISCHANGE.DESCRIPTION as CHANGE_DESCRIPTION, INITIAL_CHANGE.COMMENT as INITIAL_COMMENT, HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL, HD_CATEGORY.NAME AS CATEGORY_NAME, U2.LOCATION AS SUBMITTER_LOCATION, U2.WORK_PHONE AS SUBMITTER_WORK_PHONE, HD_PRIORITY.NAME AS TICKET_PRIORITY, HD_TICKET.CUSTOM_FIELD_VALUE0 AS EMPLOYEE_NAME, HD_QUEUE.NAME AS QUEUE_NAME from ( HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY) JOIN HD_TICKET_CHANGE THISCHANGE ON THISCHANGE.HD_TICKET_ID = HD_TICKET.ID and THISCHANGE.ID=<CHANGE_ID> JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) 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 = THISCHANGE.USER_ID left join USER U4 on U4.ID = INITIAL_CHANGE.USER_ID left join HD_QUEUE on HD_QUEUE.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 U1.FULL_NAME like '%site test%' and THISCHANGE.DESCRIPTION not like "%Created%" and HD_TICKET.HD_QUEUE_ID = 1 and HD_STATUS.STATE != 'closed' ") - Cooltech25 7 years ago-
Make sure that the brackets around CHANGE_ID are actually brackets in the select statement. Sometimes when you copy and paste they get replaced with the escaped characters. The appliance uses <CHANGE_ID> as a variable which it replaces at runtime with the ID of the change. - chucksteel 7 years ago
Posted by:
Cooltech25
7 years ago
Not getting the syntax error anymore but cannot get it to send an email. I have been at it since yesterday.
All I am trying to do is if we assign a ticket to "site test" owner we fire off an email with all the ticket detail and info. Maybe this sql I copied around is too complicated for what I am trying to do.
Comments:
-
The rule is set to email the category's cc list, and not the owner. The column containing email addresses should probably be OWNER_EMAIL. - chucksteel 7 years ago
-
I tried changing this line from HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
to HD_OWNER_EMAIL AS NEWTICKETEMAIL, and got syntax error again. I am out of my depth here with sql. - Cooltech25 7 years ago-
There's no need to change the query at this point. Just change the Column containing email address option to OWNER_EMAIL and make sure that works. - chucksteel 7 years ago
Posted by:
Cooltech25
7 years ago
Chuck thank you much!!!
Now I got the rule working but I need to tweak it a bit. It's sending multiple notifications system one and the custom one every time the ticket is saved. Even when the owner updates the ticket the custom rule sending him an email.
Any suggestions on how I can run the custom rule only once the first time we assign the ticket to the external user and after that just the regular system notifications should do the rest?