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.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
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
Answered 08/07/2015 by: Hobbsy
Red Belt

  • 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>))
  • 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.
    • 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.
      • Thank you. We didnt know that the custome field value is one lower for the query. It is working now!
Please log in to comment

Answers

0
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.
Answered 08/07/2015 by: getElementById
Second Degree Blue Belt

Please log in to comment
Answer this question or Comment on this question for clarity