Email group on new ticket creation
We use the K1000 management appliance and have not found a way for an email to be generated to our department when a new ticket is created.
Does anyone know how or where we can set this up?
Thanks for any suggestions.
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
Hobbsy
8 years ago
Top Answer
Set up a distribution list within your Exchange so that you have a single address to send to.
Next add the group email address into a custom field as the default value and make the field hidden. This will mean that you have the email address available as a data field to select in your ticket rule.
Next Create a ticket rule, using the following select statement, I created the main part of this with the wizard and then added in custom time code. The logic is if the ticket is new and the created date is within the last 20 mins fire the rule
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_TICKET.CUSTOM_FIELD_VALUE8 as EMAIL_LIST,
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 (((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 20 MINUTE))))) and HD_TICKET.HD_QUEUE_ID = 3 )
The text highlighted in BLUE is the custom time code and the text in Yellow is the email group list.
You can now tick the box in the rule to send an email to "Each Recipient in Query" and insert the value EMAIL_LIST in the "Column containing email address" box
You can set the update query to change the status to Opened, which will mean the rule should only fire once. Set the rule to run every 15 mins
Comments:
-
What should my my Update SQL look like to have it change the status to open? Sorry I am new to SQL This is what mine has:
update HD_TICKET, HD_CATEGORY as T5
set HD_TICKET.HD_CATEGORY_ID = T5.ID
where T5.NAME = '' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
(HD_TICKET.ID in (<TICKET_IDS>)) - ambemmay 8 years ago -
Hobbsy-
What type of custom field does this need to be? I made it a text then input the email address but we arent getting the emails. The query seems to be working with the exception that its not generating the emails. "Next add the group email address into a custom field as the default value and make the field hidden. - ambemmay 8 years ago-
A plain text custom field should work fine. Edit the custom field and make the default value the email address. Then display the field as read only in your ticket. That way you can make sure the data value (email address) is correct. Remember the custom field value is one lower for your query, so Custom field 9 is HD_TICKET.CUSTOM_FIELD_VALUE8 in the data base and this needs to be in your query. Once you have these in place and it starts to work, simply change the field to hidden in the ticket. - Hobbsy 8 years ago
-
Thank you. We didnt know that the custome field value is one lower for the query. It is working now! - ambemmay 8 years ago
Posted by:
getElementById
8 years ago
Mines set up a little differently but achieved the same result. I do it like this because I like being able to have techs add comments to tickets via email. It seems to save time too as they aren't having to log into the Service Desk constantly.
Here is a Kace checklist for Exchange.
https://support.software.dell.com/k1000-systems-management-appliance/kb/130641
You'll also want to make sure to set up the DefaultTicketOwners (in the kbox, not AD) user with an email address pointing to the email address of your AD distribution group with your techs in it. Set your DefaultTicketOwners as the default owner of each of your ticket categories. More precise information on the DefaultTicketOwners user/setup is found in the kbox built-in help.
