KACE Product Support

Hi @kiwiblue,

I built a ticket queue for device issuance. When an employee borrows a device,
such as a beamer or laptop for an internal presentation - we open a ticket in this queue and choose that asset which he/she borrows.
When saving, a rule is activated that changes the description of the ticket - as well as reads information from the linked asset.
At this point the device is handed over and the employee electronically confirms in the ticket that he has received the device (simple checkbox).

In total there are 3 rules to be executed when creating a ticket. Here is one that inserts the information from the linked asset into custom fields.
Maybe you can customize the SQL update to fit you.

SQL query:

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(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_STATUS.NAME = 'automatic') and HD_TICKET.HD_QUEUE_ID = 19 )

SQL update:

update HD_TICKET

JOIN HD_STATUS as T7

ON T7.HD_QUEUE_ID = HD_TICKET.HD_QUEUE_ID

LEFT JOIN ORG1.ASSET as A on A.ID = HD_TICKET.ASSET_ID

LEFT JOIN ORG1.ASSET_ASSOCIATION as AA on AA.ASSET_ID = HD_TICKET.ASSET_ID AND ASSET_FIELD_ID = 10139

    set

HD_TICKET.CUSTOM_FIELD_VALUE1 = (SELECT NAME FROM ORG1.ASSET_CLASS WHERE ASSET_CLASS.ID = A.ASSET_CLASS_ID),

HD_TICKET.CUSTOM_FIELD_VALUE12 = (SELECT NAME FROM ORG1.ASSET WHERE ID = AA.ASSOCIATED_ASSET_ID),

HD_TICKET.SUMMARY = CONCAT (HD_TICKET.CUSTOM_FIELD_VALUE1, ' - ', A.NAME, '

 // 

', ' 

Requested by: ', HD_TICKET.CUSTOM_FIELD_VALUE0),

HD_TICKET.HD_STATUS_ID = T7.ID, 

HD_TICKET.TIME_OPENED  = IF(T7.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), 

HD_TICKET.TIME_CLOSED  = IF(T7.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), 

HD_TICKET.TIME_STALLED = IF(T7.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED), 

HD_TICKET.SATISFACTION_RATING = IF(T7.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING), 

HD_TICKET.SATISFACTION_COMMENT = IF(T7.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)

  where T7.NAME = 'Waiting for confirmation' and 

        (HD_TICKET.ID = (<TICKET_IDS>))