Custom SLA's K1000
Has anyone created custom SLA's to use the category instead of the built in impact or priority?
If so would you be willing to share?
Thanks
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
Hobbsy
6 years ago
Yes we had a customer in Texas that asked for exactly that, the priority to be set depending on the category. It was quite straightforward to do, you need to match the Category name with the priority ID using a case statement.
We used the following select statement
SELECT
HD_TICKET.HD_QUEUE_ID,
HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.HD_PRIORITY_ID,
HD_CATEGORY.NAME
FROM
HD_TICKET
LEFT JOIN HD_CATEGORY ON HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
WHERE HD_TICKET.HD_QUEUE_ID = X
And then an update statement like
/* Update query checks the category and applies the correct priorty to the ticket */
UPDATE
HD_TICKET
LEFT JOIN HD_CATEGORY ON HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID
SET
/* this sets the priority field based on the category*/
HD_TICKET.HD_PRIORITY_ID = CASE
WHEN HD_CATEGORY.NAME = "Copier::No Output::Paper Jam" THEN 81
WHEN HD_CATEGORY.NAME = "Copier::No Output::Error on console" THEN 81
WHEN HD_CATEGORY.NAME = "Copier::Poor Output::Toner failing" THEN 81
WHEN HD_CATEGORY.NAME = "Copier::Poor output::Colors missing/bad" THEN 81
WHEN HD_CATEGORY.NAME = "Copier::Input problem::Paper stuck" THEN 81
WHEN HD_CATEGORY.NAME = "Security::Admin::Termination actions" THEN 82
WHEN HD_CATEGORY.NAME = "Security::Admin::Other" THEN 79
WHEN HD_CATEGORY.NAME = "Security::CIS::New User" THEN 79
WHEN HD_CATEGORY.NAME = "Security::CIS::Change access" THEN 79
WHEN HD_CATEGORY.NAME = "Security::CIS::Password Lock" THEN 79
WHEN HD_CATEGORY.NAME = "Security::CIS::Password Unlock" THEN 79
WHEN HD_CATEGORY.NAME = "Security::CIS::Termination actions" THEN 82
WHEN HD_CATEGORY.NAME = "Security::CIS::Other" THEN 79
WHEN HD_CATEGORY.NAME = "Server::P5::Patch" THEN 81
WHEN HD_CATEGORY.NAME = "Server::P5::Update OS" THEN 81
WHEN HD_CATEGORY.NAME = "Server::P5::Hardware repair" THEN 81
WHEN HD_CATEGORY.NAME = "Virtual Desktop::Memory::Add" THEN 87
WHEN HD_CATEGORY.NAME = "Virtual Desktop::Memory::Problem" THEN 80
WHEN HD_CATEGORY.NAME = "Virtual Desktop::Processors::Add" THEN 80
ELSE 81
END
WHERE
/* Ties the Query to a specific queue */
HD_TICKET.HD_QUEUE_ID = X AND
/* KACE Specific restricts the update to the ticket ID's found in the select statement
if onTicketSave will only do that ticket */
(HD_TICKET.ID in (<TICKET_IDS>))