/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1000 v10 Change Ticket Template via Ticket Rule

12/18/2019 203 views

With K1000 v10 we have setup a few of the new Ticket Templates and are having to manually switch between them when needed. I am trying to create a ticket rule change from Template A to Template B on Ticket Save when a criteria is met.
I can isolate what tickets I want based on their Category but I do not know the SQL information for Ticket Templates to change them. Has anyone found these values in the system yet?

0 Comments   [ + ] Show comments

Comments


All Answers

1

If you look in the HD_TICKET table you will find a field TICKET_TEMPLATE_ID, that is the field that dictates the template to be applied and makes reference to the HD_TICKET_TEMPLATE.ID field which contains the ID of the template as seen in the HD_TICKET Table. In theory changing the value in the HD_TICKET.TICKET_TEMPLATE_ID field should change the template.

Answered 12/19/2019 by: Hobbsy
Red Belt

  • Thank you for the help. I am running into a problem trying to get this to work.

    I have tried to inject the field into the Select SQL but am getting errors. I am not a SQL expert by any measure but ran the Ticket Rule Wizard to get a basic selection and update: Select Category=KACE::Test and Status=Opened. Then I modified the Select SQL with:

    select HD_TICKET.*, HD_TICKET_TEMPLATE.ID as TICKET_TEMPLATE_ID,

    This is the Run Log and the error shows:
    mysqli error: [1054: Unknown column 'HD_TICKET_TEMPLATE.ID' in 'field list']

    Below is the full Run Log, Select SQL, and Update SQL I am trying. Any help would be much appreciated!! Thank you.

    Last Run Log:
    12/23/2019 15:26:02> Starting: 12/23/2019 15:26:02 12/23/2019 15:26:02> Executing Select Query... 12/23/2019 15:26:02> mysqli error: [1054: Unknown column 'HD_TICKET_TEMPLATE.ID' in 'field list'] in EXECUTE("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, HD_TICKET_TEMPLATE.ID as TICKET_TEMPLATE_ID, 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 ((( HD_CATEGORY.NAME = 'KACE::Test') AND HD_STATUS.NAME = 'Opened') and HD_TICKET.HD_QUEUE_ID = 1 )")

    Select SQL:
    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,
    HD_TICKET_TEMPLATE.ID as TICKET_TEMPLATE_ID,
    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 ((( HD_CATEGORY.NAME = 'KACE::Test') AND HD_STATUS.NAME = 'Opened') and HD_TICKET.HD_QUEUE_ID = 1 )

    Update SQL:
    update HD_TICKET, HD_CATEGORY as T5
    set HD_TICKET.HD_CATEGORY_ID = T5.ID,
    HD_TICKET.TICKET_TEMPLATE_ID = 5,
    where T5.NAME = 'KACE::Test' and
    HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
    (HD_TICKET.ID in (<TICKET_IDS>))
    • I think you need to join the HD_TICKET table to the HD_TICKET_TEMPLATE Table so that the select query can find the HD_TICKET_TEMPATE.ID field, I would suggest you join on the following fields?

      LEFT JOIN HD_TICKET_TEMPLATE on HD_TICKET.TICKET_TEMPLATE_ID = HD_TICKET_TEMPLATE.ID

      Also you may need to add in HD_TICKET_TEMPLATE to the FROM Statement

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