Service Desk Ticket Rule reminder for Open Tickets need attention
OK, I have to admit the whole custom ticket rule thing in the KACE Help Desk confuses the heck out of me. In my organization we not only use the help desk system for our IT Staff but we also have Queues set up for our Maintenance Department and our Library. Well the Library staff have a huge problem of leaving tickets in an opened state and never updating or closing them. I am assuming they are fulfilling the requests but that is not my place to determine.
What I want to do is create a ticket rule or rules that will automatically email a person or any email address such as a distribution group to say there is a ticket waiting for attention. Ideally I would like to set this so it emails the library distribution email after a ticket has sat in a non-closed state for so many days. That way they would get a reminder to check the ticket and do something about it even if it is just moving it to a closed status.
I see the pre-set up rules and thought maybe I could modify one of those to fit my needs. Basically I have gone into customize the custom ticket rules and try to go through the wizard. I figured if I could enter the criteria as follows
Status != Closed (Does not equal Closed)
Created > ????
??? Represents where I would like to put a duration like 7 days ago or something. I have no idea how to do that and cannot seem to find any helpful hints on what to put in there so it can be properly read in the database
Once I can determine that I can get to the screen where it emails someone one tickets fit the criteria. I do not want it to change status or anything just hound the library staff to check their tickets.
EDIT NOTE: I am also looking to creating a custom report for this but was hoping to get something that could send an email to the actual ticket that is been sitting idle and not a compiled report sent daily,
What I want to do is create a ticket rule or rules that will automatically email a person or any email address such as a distribution group to say there is a ticket waiting for attention. Ideally I would like to set this so it emails the library distribution email after a ticket has sat in a non-closed state for so many days. That way they would get a reminder to check the ticket and do something about it even if it is just moving it to a closed status.
I see the pre-set up rules and thought maybe I could modify one of those to fit my needs. Basically I have gone into customize the custom ticket rules and try to go through the wizard. I figured if I could enter the criteria as follows
Status != Closed (Does not equal Closed)
Created > ????
??? Represents where I would like to put a duration like 7 days ago or something. I have no idea how to do that and cannot seem to find any helpful hints on what to put in there so it can be properly read in the database
Once I can determine that I can get to the screen where it emails someone one tickets fit the criteria. I do not want it to change status or anything just hound the library staff to check their tickets.
EDIT NOTE: I am also looking to creating a custom report for this but was hoping to get something that could send an email to the actual ticket that is been sitting idle and not a compiled report sent daily,
1 Comment
[ + ] Show comment
-
Wilkerson, did you ever get this to work? If not, I have something that may help you. - Mo - mmarchese@cookcountytreasurer.com 6 years ago
Answers (2)
Please log in to answer
Posted by:
Druis
9 years ago
I have a rule in place to alert me of any agents have left a ticket unattended.
Here is the Script:
SELECT 'myemail@company.com' AS ES_EMAIL,
S.NAME AS STATUS,
T.ID AS ID,
T.TITLE AS ISSUE,
U.FULL_NAME AS SUBMITTER,
O.FULL_NAME AS OWNER,
T.CREATED AS CREATED,
T.MODIFIED AS STALLED
FROM HD_TICKET T
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
JOIN USER O ON (O.ID = T.OWNER_ID)
WHERE (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) >= 7)
AND T.HD_QUEUE_ID = 1
You'll need to change 'New' to whatever status you want to be alerted on. Also the 7 in the WHERE statement is the number of days before the alert goes out.
The Script will populate some of the content of the email.
Column containing email addresses: ES_EMAIL
Message:-
There's a ticket with status "New" for at least 7 days, please review.
Created: $created
Stalled: $modified
Status: $status
Ticket ID: $id
Issue: $issue
Submitter: $submitter
Owner: $owner
Comments:
-
Forgive my ignorance as your response looks like your script is exactly what I want to do so bear with me as I outline what I think the steps should be.
Go To Kace Service Desk --> Configuration
Select the Queue where the rule is to be made
Scroll down to Ticket Rules and click on Customize by Custom Ticket Rules
Create new(SQL) rule
Name: Unattended Tickets
Description: Tickets left in a non-closed state for longer than 7 days
Select SQL:
SELECT 'myemail@mydomain' AS ES_EMAIL,
S.NAME AS STATUS,
T.ID AS ID,
T.TITLE AS ISSUE,
U.FULL_NAME AS SUBMITTER,
O.FULL_NAME AS OWNER,
T.CREATED AS CREATED,
T.MODIFIED AS STALLED
FROM HD_TICKET T
JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)
JOIN USER U ON (U.ID = T.SUBMITTER_ID)
JOIN USER O ON (O.ID = T.OWNER_ID)
WHERE (S.NAME != 'Closed' AND DATEDIFF(NOW(), T.MODIFIED) >= 7)
AND T.HD_QUEUE_ID = 10
Email each recipient in query results:
Subject:
Column containing email addresses: ES_EMAIL
Message:
There's a ticket that is not "Closed" for at least 7 days, please review ticket and verify that request is completed and closed.
Created: $created
Stalled: $modified
Status: $status
Ticket ID: $id
Issue: $issue
Submitter: $submitter
Owner: $owner
As you can see I made just couple changes in the SQL but it does not seem to work and I get the following error
There were syntax errors in your query.
mysql error: [1054: Unknown column 'HD_TICKET.HD_QUEUE_ID' in 'where clause']
I thought it was because I changed that line to != "Closed" so I changed it back to = 'New' and it still throws the same error.
Any thoughts? - bwilkerson 9 years ago-
I'm not sure what to tell you. I copied and pasted directly from your text to a SQL window to test it and it work for me.
Which version of Kace are you running? - Druis 8 years ago -
When rules run KACE appends a line to make sure the rule only runs on the queue that the rule is configured. So at runtime it is adding "and HD_TICKET.HD_QUEUE_ID = 10" even though you already have that specified in the rule. Unfortunately this statement has that table aliased as T (FROM HD_TICKET T), so when it adds a reference to HD_TICKET again things get confusing. I believe if you remove the alias to the ticket as T the rule will work. You will have to update the other references to T to make the rest of the query work, e.g. T.ID becomes HD_TICKET.ID, etc. - chucksteel 8 years ago
Posted by:
chucksteel
9 years ago
Regarding how to handle dates in rules see this post:
http://www.itninja.com/question/custom-ticket-rule
