I am trying to setup a ticket rule to send an email notification to a specific email address when certain criteria are met.

Help with this would be appreciated.

these are the criteria.

                        and HD_TICKET_CHANGE.DESCRIPTION like 'Incident Created%'

                        and HD_TICKET.CUSTOM_FIELD_VALUE0 rlike 'HP Records Manager%|Bar Code Reader%'

                        and HD_TICKET.HD_QUEUE_ID = 2

                        and HD_STATUS.NAME !='Closed'

                       and HD_TICKET_CHANGE.DESCRIPTION !=''


1 Comment   [ + ] Show Comment

Comments

  • Can you please post the entire select statement?
    • Thank you Chucksteel.

      select HD_TICKET.ID as TICKNUM,
      HD_TICKET.TITLE as TITLE,
      HD_STATUS.NAME AS STATUS_NAME,
      HD_TICKET.CUSTOM_FIELD_VALUE0 as FPA_APPLICATION,
      HD_TICKET.CUSTOM_FIELD_VALUE2 as COMMENTS,
      HD_STATUS.ORDINAL as STATUS_ORDINAL,
      HD_IMPACT.NAME as IMPACT_NAME,
      HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
      HD_CATEGORY.NAME as CATEGORY_NAME,
      HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
      HD_PRIORITY.NAME as PRIORITY_NAME,
      HD_PRIORITY.ORDINAL as PRIORITY_ORDINAL,
      'myemailaddress@mycompany.com.au' as EMAIL,
      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
      LEFT JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID =
      HD_TICKET.ID
      and HD_TICKET_CHANGE.ID=<CHANGE_ID>
      where HD_PRIORITY.ID = HD_PRIORITY_ID
      and HD_STATUS.ID = HD_STATUS_ID
      and HD_IMPACT.ID = HD_IMPACT_ID
      and HD_TICKET.OWNER_ID = 0
      and HD_CATEGORY.ID = HD_CATEGORY_ID
      and HD_TICKET_CHANGE.DESCRIPTION like 'Incident Created%'
      and HD_TICKET.CUSTOM_FIELD_VALUE0 rlike 'HP Records Manager%|Bar Code Reader%'
      and HD_TICKET.HD_QUEUE_ID = 2
      and HD_STATUS.NAME !='Closed'
      and HD_TICKET_CHANGE.DESCRIPTION !=''


      K1000 Custom ticket rule to email a notification when ticket is created based on several criteria.
      Hi,

      An Incident is for an application you might be responsible for.
      Please review the Incident, take ownership and action in a timely manner.

      The submission was:

      Ticket: $ticknum
      Category: $category_name

      FPA Application: $fpa_application
      Priority: $priority_name
      Status: $status_name
      Severity: $impact_name

      Comment: $comments


      http://kbox1000/userui/ticket?ID=$ticknum

      Thanks,
Please log in to comment

Answers

2
I would remove the HD_TICKET_CHANGE.DESCRIPTION !='' , it is redundant with   HD_TICKET_CHANGE.DESCRIPTION like 'Incident Created%'.

Also, I think that your rlike syntax is incorrect. See this reference:
The rlike function doesn't use the same wildcard (%) as like in my understanding.

Answered 08/01/2016 by: chucksteel
Red Belt

  • Thank you Chuck, I will give it a go. I appreciate your help.
Please log in to comment
Answer this question or Comment on this question for clarity