Any feature or way that We can assign each ticket a sequencial number in the queue so We can provide to the end user the position of the ticket in the current queue?
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
-
So you want to be able to show the user their ticket is number x out of y open tickets? - chucksteel 9 years ago
-
Yes Chucksteel. Any ideas? - fsalles 9 years ago
-
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. - chucksteel 9 years ago
Answers (4)
Please log in to answer
Posted by:
chucksteel
9 years ago
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.
Comments:
-
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? - fsalles 9 years ago-
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. - chucksteel 9 years ago-
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 - fsalles 9 years ago -
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> - chucksteel 9 years ago -
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. - fsalles 9 years ago -
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. - chucksteel 9 years ago
Posted by:
fsalles
9 years ago
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
Comments:
-
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 - chucksteel 9 years ago
-
It is correct. - fsalles 9 years ago
-
In the update statement, remove
ORDER by HD_TICKET.CREATED
and put in
HD_TICKET.ID in <TICKET_IDS> - chucksteel 9 years ago
-
The custom field is set to not required and user create. Field type: number - fsalles 9 years ago
-
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 - fsalles 9 years ago-
You might need parenthesis around the <TICKET_IDS>, so
(<TICKET_IDS>) - chucksteel 9 years ago -
You're genius! Thank you SO much! - fsalles 9 years ago
-
You're welcome. This was a good challenge! - chucksteel 9 years ago
Posted by:
fsalles
9 years ago
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..
Comments:
-
When is the rule set to run and is it enabled (I sometimes forget to change that setting). - chucksteel 9 years ago
-
Yes, runs every 15 minutes and is enabled. - fsalles 9 years ago
-
And does the run log show anything? - chucksteel 9 years ago
Posted by:
fsalles
9 years ago
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.
Comments:
-
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). - chucksteel 9 years ago-
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? - fsalles 9 years ago-
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. - chucksteel 9 years ago