/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


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

07/30/2016 904 views

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,

All 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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ