We would like to be able to give the end user a time frame  or position of the ticket in the current queue so they will have an idea when the ticket will be resolved/worked on. Any ideas?
1 Comment   [ + ] Show Comment

Comments

  • So you want to be able to show the user their ticket is number x out of y open tickets?
    • Yes Chucksteel. Any ideas?
      • This should be possible with a custom rule that updates a custom field. I'd have to work out the SQL query but it should be possible.
Please log in to comment

Answers

0
Here's what I was able to come up with so far. I have a rule that can run every fifteen minutes that will update a custom field with the queue position for every ticket. The SQL select statement is the following:

select HD_TICKET.ID
from HD_TICKET
where
HD_TICKET.CUSTOM_FIELD_VALUE4 != '0' 
and TIME_CLOSED = '0000-00-00 00:00:00'
and HD_TICKET.HD_QUEUE_ID = 1 

This looks for any tickets that are open and have a custom field value 5 (the sql columns start at 0 so custom field 5 is value4) isn't 0. I guess it would work without this check but I left it in there.

The update query follows:
update HD_TICKET
    set HD_TICKET.CUSTOM_FIELD_VALUE4 = 
(SELECT @curRow := @curRow + 1 AS row_number
FROM (SELECT @curRow := 0) r) 
  where 
        TIME_CLOSED = "0000-00-00 00:00:00" and HD_QUEUE_ID = 1
ORDER BY HD_TICKET.ID

This sets the field to a sequential number for all open tickets ordered by the ticket ID, you could change that to another field, like due date, if that better suits your purposes. The shortest interval for running ticket rules is fifteen minutes so the values won't be super accurate depending on the volume of tickets you have in your queue. I also had a rule that sets the order on ticket save but in that case you would need to re-save each ticket to know its true place in the queue since the value might be stale.

Answered 06/20/2014 by: chucksteel
Red Belt

  • Tks Chucksteel!!
    We were thinking to create a custom filed for the sequential ID (will keep changing every 15 minutes) and we would like to order by start date and time. Can we have it ordered by priority Level 2 only?
    • Ordering by ticket ID should be the same as ordering by when the ticket was opened, but if you wanted to be specific about it then change this line in the update statement:
      ORDER BY HD_TICKET.ID
      to:
      ORDER by HD_TICKET.CREATED

      To restrict the rule to only run on tickets with a specific priority you would need the priority ID for that priority. It's easiest to get that ID if you can look in the database since the UI doesn't really show it to you. Let's say the ID turns out to be 10. Once you have the ID you need to adjust the update statement's where clause to include that ID, so this:
      TIME_CLOSED = "0000-00-00 00:00:00" and HD_QUEUE_ID = 1
      would become:
      TIME_CLOSED = "0000-00-00 00:00:00" and HD_QUEUE_ID = 1 and HD_PRIORITY_ID = 10

      Also, I didn't mention that if this rule isn't in your first queue you'll need to adjust the HD_QUEUE_ID = 1 to match the queue ID to the correct value.
      • So I created the Field Value 22 (23rd in the SQL column) that is a numeric field. But is not populating when I run the script. What am I missing? Here is the script:

        select HD_TICKET.ID
        from HD_TICKET
        where
        HD_TICKET.CUSTOM_FIELD_VALUE22 != '0'
        and TIME_CLOSED = '0000-00-00 00:00:00'
        and HD_TICKET.HD_QUEUE_ID = 6 and HD_TICKET.CUSTOM_FIELD_VALUE2 = 'Level 2' and HD_STATUS.NAME = 'New'
        (I want to apply only for the "New" Tickets and that have severity "Level 2".

        Query:
        update HD_TICKET
        set HD_TICKET.CUSTOM_FIELD_VALUE22 =
        (SELECT @curRow := @curRow + 1 AS row_number
        FROM (SELECT @curRow := 0) r)
        where
        TIME_CLOSED = "0000-00-00 00:00:00" and HD_QUEUE_ID = 6
        ORDER by HD_TICKET.CREATED
      • In order for my rule to work you need to use one of the first 15 custom fields. Custom fields higher than 15 are stored in a separate table (HD_FIELD) which would make the query more complicated.

        Also, my update query doesn't act on the tickets returned by the select query, I'm not sure why I did it that way. Use this update query instead to act only on the ticket returned by the select statement:

        update HD_TICKET
        set HD_TICKET.CUSTOM_FIELD_VALUE22 = (SELECT @curRow := @curRow + 1 AS row_number FROM (SELECT @curRow := 0) r)
        where
        HD_TICKET.ID in <TICKET_IDS>
      • Tks for your quick response. I applied the change and fixed the query error was getting. custom filed is now 8 but is still not assigning any number to the field.
        We wanted to be able to give these IDs to each unassigned ticket to update the submitter on the "position" of his ticket in the queue.
      • Try turning on the option to email the query results to an email address and have them sent to you. This will tell you if the tickets are being selected properly.
Please log in to comment
0
Almost perfect!! We are very close. I now received an email with the tickets ordered but in the actual field value 7 that I called "Sequence" does not populate. Any clue?
I now get the following error: 

07/10/2014 14:41:30> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER by HD_TICKET.CREATED' at line 4] in EXECUTE("update HD_TICKET
    set HD_TICKET.CUSTOM_FIELD_VALUE7 = (SELECT @curRow := @curRow + 1 AS row_number FROM (SELECT @curRow := 0) r) 
  where
       ORDER by HD_TICKET.CREATED")

07/10/2014 14:41:30> Ending: 07/10/2014 14:41:30
Answered 07/10/2014 by: fsalles
Senior White Belt

  • I think when I sent the new update statement I didn't put in the right number for custom field value. Make sure the update statement has HD_TICKET.CUSTOM_FIELD_VALUE7
    • It is correct.
      • In the update statement, remove
        ORDER by HD_TICKET.CREATED
        and put in
        HD_TICKET.ID in <TICKET_IDS>
    • The custom field is set to not required and user create. Field type: number
    • Now the error is: 07/10/2014 14:53:02> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '17805,17817' at line 4] in EXECUTE("update HD_TICKET
      set HD_TICKET.CUSTOM_FIELD_VALUE7 = (SELECT @curRow := @curRow + 1 AS row_number FROM (SELECT @curRow := 0) r)
      where
      HD_TICKET.ID in 17805,17817")

      07/10/2014 14:53:02> Ending: 07/10/2014 14:53:02
      • You might need parenthesis around the <TICKET_IDS>, so
        (<TICKET_IDS>)
      • You're genius! Thank you SO much!
      • You're welcome. This was a good challenge!
Please log in to comment
0
HI Chucksteel, I turned on the email but haven't received anything. The custom filed should be numeral type correct? Not sure what is missing..
Answered 07/10/2014 by: fsalles
Senior White Belt

  • When is the rule set to run and is it enabled (I sometimes forget to change that setting).
    • Yes, runs every 15 minutes and is enabled.
      • And does the run log show anything?
Please log in to comment
0
No errors. I also set to send the query results to my email.

But something seems fishy. I see there is a link right below the Query box that says: "View ticket search Results" and when I click there is shows in red the following: 

"mysql error: [1054: Unknown column 'SORT_OVERDUE_STATUS' in 'order clause'] in EXECUTE("select HD_TICKET.ID from HD_TICKET where HD_TICKET.CUSTOM_FIELD_VALUE7 != '0' and TIME_CLOSED = '0000-00-00 00:00:00' and HD_TICKET.HD_QUEUE_ID = 6 and HD_TICKET.CUSTOM_FIELD_VALUE2 = 'Level 2' and HD_TICKET.HD_STATUS_ID = 'New' and HD_TICKET.HD_QUEUE_ID = '6' ORDER BY SORT_OVERDUE_STATUS, STATUS_ORDINAL, PRIORITY_NUMBER, SORT_OWNER_NAME, TITLE LIMIT 0,10")"

But the run log box shows no error.
Answered 07/10/2014 by: fsalles
Senior White Belt

  • That's odd that the run box doesn't show an error since there is one. Does the select query in your rule match the query shown in the error? It's possible that KACE is just changing the query when you click that link in order to limit the results it returns for testing purposes.

    I also just noticed that the query has HD_TICKET.HD_STATUS_ID = 'New'. This wouldn't match any tickets since it would be looking for a status ID, not a status name. If you only want to match a specific status for the rule then the query will need to be changed to join to the status table (or you'll need the status ID that you want to match, not its name).
    • I don't think it matches. The box of the query contains:
      select HD_TICKET.ID
      from HD_TICKET
      where
      HD_TICKET.CUSTOM_FIELD_VALUE7 != '0'
      and TIME_CLOSED = '0000-00-00 00:00:00'
      and HD_TICKET.HD_QUEUE_ID = 6 and HD_TICKET.CUSTOM_FIELD_VALUE2 = 'Level 2' and HD_TICKET.HD_STATUS_ID = 'New'

      Yes, I want only the "New" (status) tickets with Level 2 (custom field value2) to be in the query ordered by custom fieldvalue 7). Does this make sense?
      • Then you need to update the query to:

        select HD_TICKET.ID
        from HD_TICKET
        JOIN HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
        where HD_TICKET.CUSTOM_FIELD_VALUE7 != '0'
        and TIME_CLOSED = '0000-00-00 00:00:00'
        and HD_TICKET.HD_QUEUE_ID = 6
        and HD_TICKET.CUSTOM_FIELD_VALUE2 = 'Level 2'
        and HD_STATUS.NAME = 'New'

        This adds a join to the HD_STATUS table where the status name is stored, the HD_TICKET table just stores a pointer to HD_STATUS based on the ID, not the name of the status.
Please log in to comment
Answer this question or Comment on this question for clarity