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,
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
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


Answered 04/23/2015 by: Druis
Blue Belt

  • 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?
    • 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?
    • 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.
Please log in to comment
0
Regarding how to handle dates in rules see this post:
http://www.itninja.com/question/custom-ticket-rule

Answered 04/23/2015 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity