/build/static/layout/Breadcrumb_cap_w.png
05/30/2017 715 views
Hi all,

To keep it short, we have a checklist that e-mails specific users when a new ticket is created from this queue. However, we only want to inform another party when a specific checkbox for application access is required. Not all new hires will get access to this application so we don't want to have him continuously receiving e-mails when a new hire starts. How can I go about this? I've done some searching but nothing specifically for my use case.

Any help would be greatly appreciated!

Thanks!
3 Comments   [ + ] Show comments

Comments

  • I have been using custom rules to email a specific user on a text field in the past, so I don't see why you couldn't create a custom rule to trigger on that checkbox... I suggest downloading MySQL Workbench and connecting to the appliance so that you can test on that checkbox.
  • Hey dsykes, would you mind sharing some of how you set up your custom rule? I'm not too savvy with SQL and feel like I'm poking in the dark even after connecting to the DB.
    • I create a custom rule via the wizard then customize as needed.

      For instance (probably could be cleaned up)...

      This selects tickets and notifies a technician for a special case we have... in our system all tickets are routed automatically based on the predefined categories in KACE, but if a user is of the Commercial Operations/Dispatch department and the ticket is New-Not Started it will be directed to a user called XXXXX (didn't want to put their name here).

      This occurs ON TICKET SAVED

      Select statement:

      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(HD_TICKET.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

      *************************************This is where I came in an fiddled with the code*********************
      AND (((((HD_STATUS.NAME NOT LIKE '%Closed%')
      AND HD_PRIORITY.NAME LIKE '%Normal%')
      AND (HD_TICKET.CUSTOM_FIELD_VALUE1 LIKE '%Commercial Operations%'
      OR HD_TICKET.CUSTOM_FIELD_VALUE1 LIKE '%Dispatch%'))
      AND HD_STATUS.NAME LIKE '%New - Not Started%')
      AND HD_TICKET.HD_QUEUE_ID = 5)
      ***********************************************************************************************************************




      Update statement:

      update HD_TICKET, USER as T5, HD_STATUS as T6
      set HD_TICKET.OWNER_ID = T5.ID,
      HD_TICKET.HD_STATUS_ID = T6.ID,
      HD_TICKET.TIME_OPENED = IF(T6.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
      HD_TICKET.TIME_CLOSED = IF(T6.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
      HD_TICKET.TIME_STALLED = IF(T6.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
      HD_TICKET.SATISFACTION_RATING = IF(T6.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
      HD_TICKET.SATISFACTION_COMMENT = IF(T6.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)
      *******************Another edit******************************************************
      where T5.USER_NAME = 'XXXXX' and
      T6.NAME = 'New - Owner Assigned' and
      *****************************************************************************************
      HD_TICKET.HD_QUEUE_ID = T6.HD_QUEUE_ID and
      (HD_TICKET.ID in (<TICKET_IDS>))
  • You mention a checklist and specific checkboxes. Does the checklist currently automatically create tickets? If so, how?
    • I think I actually figured this out. The checklist was being created as a child ticket. However, I've done away with the checklist as it wasn't being used and I found an easy way to basically get the same SQL query as what dsykes posted. Unfortunately, the e-mail that goes to the intended party comes up kinda ugly, the formatting is off.

There are no answers at this time