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. So we need a TR to assign an Opened (as opposed to New) ticket to the Updater if the Owner is Unassigned.

I've tabled this question before, and the answer I got back was 'it's really hard to detect the updating user'

However, thinking about it, this isn't true, because when an update is done, the person making it is recorded in the history.... so it's there somewhere.

So I need SQL to

Check if the ticket is Unassigned and Opened (the wizard can give me that)
Collect the Updated By User field from the History
Stuff that into the Owner field on the ticket.

My SQL isn't that good.

Running this through the Wizard I get this


select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', ')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and ((( HD_STATUS.NAME not like '%New%') AND (1 not in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.USER_NAME like '%_%')) ) and HD_TICKET.HD_QUEUE_ID = 1 )


and to update

update HD_TICKET as T, USER as T5
set T.OWNER_ID = T5.ID
where T5.USER_NAME = 'last update in history' and
(T.ID in (<TICKET_IDS>))


So far so good, but I need to dig the last updater name out of the history table for the T5.USER_NAME field and for that I need help, if anyone feels up to a challenge :)
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

-1
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.
Answered 10/13/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
1
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?
Answered 10/13/2011 by: Acedashdog
Purple Belt

Please log in to comment
0
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
Answered 10/13/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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.
Answered 10/13/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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.
Answered 11/21/2011 by: Acedashdog
Purple Belt

Please log in to comment
0
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?
Answered 11/21/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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.
Answered 11/21/2011 by: Acedashdog
Purple Belt

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