/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1100 Ticket email based on custom Due Date

12/05/2019 171 views

Hi.

We use our K1100 appliance for service desks but also as short-term sales activity portal. The central idea with this to track customers and follow up with the prospective customers and to do that, I created queue which contains simple information about them. I used custom field 4 for Contact Date and field 5 for Follow Up Date.

Here is where I am stuck.

My desired outcome? To have the system email the submitter of the ticket to remind them to follow up. I am not an expert in SQL so I really need some ideas as to how to make this work. I know it will have something to do with Custom field 5 and the date but don't know how to move from idea to reality.

Thanks in advance for any assistance with this.


Answer Summary:
5 Comments   [ + ] Show comments

Comments

  • Did you consider working with the built-in SLA or escalation option of the KACE SMA ticket system?
    It depends on the due date field of a queue. Anyhow you can rename the due date field to "Follow up".
    SLA can also recognize business hours and holidays in calculation.
  • Hi.
    Thanks for the suggestion which I had tried. The issue with that is the design of the built-in Due Date - it will the sales person have to do more work than they want. That is a no fly zone by the project champion...
    Therefore a custom Due Date is the way around it.
  • OK, understand. Probably this would work with a ticket rule that is scheduled for once-every-day. It would run through all tickets that have <Custom Field 5> = <TODAY> plus <Ticket Status> != Closed or = Open (?)
  • Thanks - now I have to trim unneeded SQL - admittedly not my strong suit. My version of the appliance is 9.1.317 and I have found version updates tend to break old rules based on newer variables...
    I will post whatever I am learning from this.
  • My statement idea is simple:

    get the status of the ticket
    if it is not closed or is open,
    and the date is today
    and the queue = the queue#

    Then email the submitter

    I just don't understand how to structure it...

    select HD_TICKET.*,
    HD_STATUS.NAME AS STATUS_NAME,
    HD_STATUS.ORDINAL as STATUS_ORDINAL,
    STATE,
    if(STATE != Closed or = Open
    and Custom_5> = <TODAY>
    and HD_TICKET.HD_QUEUE_ID = ??)

Answer Chosen by the Author

1

Assuming the "Follow Up Date" is in custom field 5 (what means it's stored in CUSTOM_FIELD_VALUE4) you could try this SELECT statement and run it daily at whatever time you want the email to be sent (the SELECT is the default SELECT from the wizard and just the last line is added) for all tickets that are not closed:


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() - unix_timestamp(HD_TICKET.TIME_OPENED)

else unix_timestamp() - 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

and ((  STATE != 'closed') and HD_TICKET.HD_QUEUE_ID = 1 )

and (STR_TO_DATE(HD_TICKET.CUSTOM_FIELD_VALUE4,'%Y-%m-%d') = Date(Now()))


You probably need to adjust the queue ID to your environment.

Answered 12/09/2019 by: chrpetri
Blue Belt

  • That is excellent! I saw something similar to this in another ticket but I didn't quite get what to do with it.

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