I want to create a ticket rule to automatically assign an Unassigned ticket to the editing user if they save it (ie "you asked for it you eat it")

Halfway there with an On Save rule that fires for Unassigned tickets and sets them to a fixed username. What I need is the SQL to express "the logged in user" in the update statement. My SQL is too weak to do this, can anyone help please?

Thanks
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
I experienced the same issue in our old system. Techs posted comments but never took ownership of the tickets. I hope there is a solution to your request. If so, I will not have to manually assign such tickets.

Thanks
Answered 09/06/2011 by: hdgirl1
Yellow Belt

Please log in to comment
0
It was something I built into our current Altiris HD quite early; the bad side of that is tat the techs don't worry about taking ownership - if they work on it it follows them home like a puppy [;)]

The nearest I've managed in KACE was to create a daily emailed report of Unassigned tickets, though I couldn't seem to get it to select only tickets with work done on them.
Answered 09/07/2011 by: Acedashdog
Purple Belt

Please log in to comment
0
Hi Acedashdog,

I recently wen't through the process of writing a sql report to do something which is similar to this.

One thing I discovered was that it is a very unknown area to work out how to tell who is the current logged in user.
What I can suggest however is creating a ticket rule to run when a ticket is saved.
The ticket rule can then look at the HD_TICKEt_CHANGE table and, IF the ticket owner is currently unassigned, have a look at the update that just went in to that table for that ticket and grab the USER_ID from there.
Using that, you can then write an update query in the result to update the ticket owner to that user_id.

How does that sound?

-Dave
Answered 09/07/2011 by: davids
Senior Yellow Belt

Please log in to comment
0
Interesting....
Answered 09/07/2011 by: Acedashdog
Purple Belt

Please log in to comment
0
Give this Ticket Rule a whirl mate

Select Query
SELECT *
FROM HD_TICKET
WHERE HD_TICKET.OWNER_ID = 0
AND TIMESTAMPDIFF(SECOND,HD_TICKET.CREATED,NOW()) > 10


Update Query
UPDATE HD_TICKET T, HD_TICKET_CHANGE C
SET T.OWNER_ID = C.USER_ID
WHERE C.HD_TICKET_ID in (<TICKET_IDS>)
AND C.TIMESTAMP = NOW()
AND (T.ID in (<TICKET_IDS>))


Set the Ticket Rule Frequency to 'on Ticket Save'

This rule will check the tickets each time they are saved. If the ticket owner is currently 0 (i.e. Unassigned) and the ticket creation date and time is more than 10 seconds from the current time, then the ticket rule will update the ticket owner to the person who made the last change.

You will notice in the Select Query a line that says 'TIMESTAMPDIFF(SECOND,HD_TICKET.CREATED,NOW()) > 10'.
This is what controls that ticket created time is more then 10 seconds from the current time. If you wanted to change this to 5 seconds from the current time, you would change it to 'TIMESTAMPDIFF(SECOND,HD_TICKET.CREATED,NOW()) > 5'.

Keep in mind that the person who made the last update must be in your 'Support Call Owners By Label' label, otherwise the ticket owner will be set, but if that person is not in the list it will still show Unassigned.

Hope this helps mate

-Dave
Answered 09/07/2011 by: davids
Senior Yellow Belt

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