KACE Product Support

Hi all,

I am trying to setup a specific rule that would set an approver and assign to a certain person. The hobjob way I have it set now is clunky and doesn't really work well. This is in a school district environment, and we have multiple campuses to consider.

The way our Maintenance help desk is setup is as such:



When a new maintenance ticket is submitted, the creator selects a campus. There is a different approver per campus. The ticket does successfully get created with the correct user as the approver, and the ticket is assigned to the Maintenance secretary.

Here is an example rule as it sits now:

----------Select----------
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_IMPACT.NAME = 'Burden') AND HD_STATUS.NAME = 'New') and HD_TICKET.HD_QUEUE_ID = 3 )

----------Update----------
update HD_TICKET, USER as T5, HD_STATUS as T6
    set HD_TICKET.OWNER_ID = T5.ID,
HD_TICKET.APPROVER_ID = 625,
HD_TICKET.HD_STATUS_ID = T6.ID,
HD_TICKET.TIME_OPENED  = IF(T6.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED  = IF(T6.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T6.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.SATISFACTION_RATING = IF(T6.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
HD_TICKET.SATISFACTION_COMMENT = IF(T6.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)
  where T5.USER_NAME = 'edefrancisis' and
T6.NAME = 'Waiting for Approval' and
HD_TICKET.HD_QUEUE_ID = T6.HD_QUEUE_ID and
        (HD_TICKET.ID in (<TICKET_IDS>))


When the user approves the ticket, the maintenance secretary can do as they wish with it.

Here is the clunky-ness of it. The secretary can see tickets in her queue before they are approved, righ after submission, so she has to sort through herself and ignore them until approved. Also, if the Maintenance personnel submit a ticket for a campus, they would like it to bypass the approval stage.


So, what I am asking: Is there a more efficient way to handle the following process and still allow certain users to bypass it as listed -

User creates a ticket
Depending on what campus is selected it sets the proper approver and assigns to the approver
Once Approved, it is set to approved, and reassigns to the Maintenance Secretary
The Maintenance Secretary can then assign to her staff as needed.

The Secretary and Director would like to be able to submit a ticket for any campus, and have it immediately be set as approved and assigned to the Secretary to be handed off as needed.

I know my way around SQL but am certainly not perfectly fluent with it.

If anyone has any insight, that would be greatly appreciated.

Thanks in advance!