KACE Product Support

And if you come here asking someone to do your job for you by writing the SQL to accomplish this, badmouthing the product isn't going to get you very far.

I did no such thing. I came here to as for assistance or advice from someone who might have encountered this before. I didn't come here to be snapped at for asking an honest question. If you perhaps take another look, you'd see that I said " unlike most things Altiris used to to" which reflects my general pleasure at how easy it was to replicate our required functionality. I've said before that KACE does what Altiris does, better, faster and simpler. I've never 'badmouthed' it in my whole exposure to the product.

And please don't 'badmouth' my team either. They're not lazy; they're not used to manually assigning their tickets after four years of a system that does it automatically and four weeks with one that doesn't. There's a definite difference.

Writing SQL is not my job. I'm not a DBA. Managing a helpdesk is my job. I would suggest that asking for advice on SQL driven ticket rules is in fact a valid action on a message board marked "ticket rules"? If I knew how to write custom multi-table SQL queries I wouldn't be asking....

I have great respect for you Andy; you've helped me several times. I'd hate to fall out over something like this. But if I can't ask a question and get a civil answer how am I supposed to learn to do it right?

Apparently I must have misunderstood your original post. However, you can't say this doesn't sound negative:
However, unlike most things Altiris used to to, KACE seems unwilling to do this.
Anyway, as Gerald reiterated: the wizard isn't going to help in this case. His code should work.

It was negative WRT the one thing I couldn't seem to replicate (and I said 'seems' to express the fact that I simply may not know how) but I'd thought I had counterbalanced that small minus with 'unlike most things' which reflected that 99% of what we used to use (or struggle to use) Altiris for just damn well works with KACE, to my great pleasure.

If I gave you the impression I was moaning about the whole product I apologize, because I wasn't. I wouldn't have thrown out a 6-year Altiris contract almost overnight to move to it if I didn't like it!

My problem is that though I can do a variety of things fairly well, I'm not a SQL programmer, and I do need help on a fairly basic level with the sort of clever TR stuff that can really make the difference here. So I come and ask humbly for help, if anyone already knows the answer. If it's 'no' I'm quite happy; it's not as if it cost me owt.

What I've got here looks like that help, and I'm grateful. Once I get the chance I'll settle down with a cup of tea and work through setting it up.

Sorry if I snapped at you before. I was probably just having a bad day/week - it happens in IT. :)

Have you tried Gerald's solution?

Happens to everyone. :)

I've not had time to settle down and try working it out yet; I'm stopgapping with a scheduled report emailed to the whole team that shows unclaimed tickets. Incidence is down so far, but I'd still like to automate it so will take a look when things are quieter.

You cannot get the USER with a wizard generated query. It gives you the submitter, the owner and the approver but not the updater. The updater only has a concept in relation to the ticket change so you must join in the change table. We know that we must have an updater and a change record so we use an inner join:


from ...
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
JOIN USER UPDATER ON UPDATER.ID=C.USER_ID
...

Now UPDATER.ID is the updater. Now what you need to do is figure out if that updater is a valid owner so we have to join in the Queue's owner labels. We only care if we have some valid owners so we use inner joins again.. Queue owners are related to queues through labels. The owner labels are stored in HD_QUEUE_OWNER_LABEL_JT and USER labels are stored in USER_LABEL_JT so we look for matches there.


from ...
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
JOIN USER UPDATER ON UPDATER.ID=C.USER_ID
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID
JOIN USER_LABEL_JT UL ON UPDATER.ID=UL.USER_ID and UL.LABEL_ID=QOL.LABEL_ID
...

There now this query will only return results when the updater is a queue owner. Now we need to limit it to only when an owner is unassigned


from ...
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
JOIN USER UPDATER ON UPDATER.ID=C.USER_ID
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID
JOIN USER_LABEL_JT UL ON UPDATER.ID=UL.USER_ID and UL.LABEL_ID=QOL.LABEL_ID
...
WHERE
...
and (HD_TICKET.OWNER_ID=0 OR HD_TICKET.OWNER_ID IS NULL)

Then you need an update query . The update query based on a change is the tricky part. This is where everything a beginner knows about rules probably goes out the window. Because we need to also find out what the change record is and use that in the update we need to get the Select to tell the update what that is. The select knows because of <CHANGE_ID> but that doesn't exist in the update query. The only mechanism the select query has to pass data to the update query is via <TICKET_IDS> If you read the bubble help of the select query carefully you will see that: "replacing the string <TICKET_IDS> with a comma separated list of IDs extracted from the Select Query". So, whatever is returned as the ID column in the query will become the value stored in <TICKET_IDS>. Here's the trick: we can modify the select query to return the change ID instead of the ticket ID like here-- the caveat is when you do this you cannot use "Results are tickets, add a comment to each one" feature because the results are no longer tickets, the results are changes.

SELECT HD_TICKET.ID, C.ID, /* note how I put the change ID second. I could also remove HD_TICKET.ID but then you'd wonder where it went */
....
from ...
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
JOIN USER UPDATER ON UPDATER.ID=C.USER_ID
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID
JOIN USER_LABEL_JT UL ON UPDATER.ID=UL.USER_ID and UL.LABEL_ID=QOL.LABEL_ID
...
WHERE
...
and (HD_TICKET.OWNER_ID=0 OR HD_TICKET.OWNER_ID IS NULL)

Now our update can key on the change record like this. This update may seem incomplete but because of the select above we already know for sure the updater is a valid owner.
UPDATE HD_TICKET T
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=T.ID and C.ID=<TICKET_IDS>
SET T.OWNER_ID=C.USER_ID

If you did want to log a comment to leave an audit trail then do this:
UPDATE HD_TICKET T
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=T.ID and C.ID=<TICKET_IDS>
JOIN USER UPDATER ON UPDATER.ID=C.USER_ID
SET T.OWNER_ID=C.USER_ID,
DESCRIPTION=TRIM(BOTH '\n' FROM(REPLACE (CONCAT(TRIM(BOTH '\n' FROM
DESCRIPTION), '\n',
'Changed Owner From "Unassigned" to "',UPDATER.FULL_NAME,'".'
,'\n') ,'\n\n','\n')))

note: i didn't test any of this

We have a problem with support techs not assigning themselves the owner of the tickets they edit. Not their fault, as Altiris used to do this automatically if they didn't. However, unlike most things Altiris used to to, KACE seems unwilling to do this.
Really? I would consider it their fault if they're too lazy to assign tickets to themselves. There are SEVERAL instances where people would like to add comments to tickets without having it automatically assigned to them. And if you come here asking someone to do your job for you by writing the SQL to accomplish this, badmouthing the product isn't going to get you very far.

I'll see what I can come up with for you. You should forget about using the wizard for most custom rules, because it will create inefficient queries that can't join tables - among other things. This type of rule definitely calls for custom SQL.

You need to stop thinking, "Altiris did it, so it must be right!" - because it's not.