I've created a pretty standard ticket rule to notice when a code is passed into Resolution and send the user an email. It works pretty well, but the token $ticket_number doesn't get replaced with the TICK in the email - bit of a drag because when they reply it'll log a new call, argh heresy.

I assume I need to add something to the query to include the field but can't work it out. Is there a database field list somewhere I should be referring to?

Assistance appreciated ;)


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_TICKET.RESOLUTION like '%askcodes%') and HD_TICKET.HD_QUEUE_ID = 1 )
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
This should be moved to the ticket rules forum...

As the query stands the ticket number can be referenced as $id so you could put this in the subject to get what you need: [TICK:$id]

The caveat is that this will only work for tickets over 1000. If you want the true helpdesk behaviour with the leading zeroes then you'll have to rewrite your query.

Before I rewrite your query I would also like to point out something in it that might cause an issue for you. If you are only looking for the resolution then this query will have matches when the resolution is set AND every time the ticket is saved afterwards and a resolution remains. For example, if you were to update the status now but not change the resolution then your rule would still fire and all the subsequent actions of the rule would come into play.

A better way to write this is per faq 1046 Using that article you can detect when something changes at the time it changes.

So, a rewrite. Note that I am leaving only the ticket number -- you can reference it as $ticket_number. If you want the other columns in the result then you can add them back in. Because I am taking them out the "view search results" link will not work inside the rule editor, but I test my queries with the mysql query browser and not that link anyway.
select HD_TICKET.ID,
case when HD_TICKET.ID < 10 then concat('000',cast(HD_TICKET.ID as char))
when HD_TICKET.ID < 100 then concat('00',cast(HD_TICKET.ID as char))
when HD_TICKET.ID < 1000 then concat('0',cast(HD_TICKET.ID as char))
else HD_TICKET.ID end TICKET_NUMBER
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
LEFT JOIN HD_TICKET_CHANGE_FIELD F ON F.HD_TICKET_CHANGE_ID=C.ID

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 F.FIELD_CHANGED='RESOLUTION' and F.AFTER_VALUE LIKE '%askcodes%'
Answered 09/26/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
>>This should be moved to the ticket rules forum...
Sorry!

>>As the query stands the ticket number can be referenced as $id so you could put this in the subject to get what you need: [TICK:$id]
Perfect! That works - thanks

>>The caveat is that this will only work for tickets over 1000. If you want the true helpdesk behaviour with the leading zeroes then you'll have to rewrite your query.
Why would I need that? I tested with emails inwards with TICK:25 etc and they work OK

>>Before I rewrite your query I would also like to point out something in it that might cause an issue for you. If you are only looking for the resolution then this query will have matches when the resolution is set AND every time the ticket is saved afterwards and a resolution remains. For example, if you were to update the status now but not change the resolution then your rule would still fire and all the subsequent actions of the rule would come into play. <<
No, this is not a problem. The Update section of the rule takes the trigger code out of the Resolution field :)
Answered 09/26/2011 by: Acedashdog
Purple Belt

Please log in to comment
0
Why would I need that? I tested with emails inwards with TICK:25 etc and they work OK
you may not NEED it for an email update to work but it's necessary if you want it to cosmetically look the same as the helpdesk would display it


No, this is not a problem. The Update section of the rule takes the trigger code out of the Resolution field :)

How does the askcodes string get into the resolution field to begin with?
Answered 09/26/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
you may not NEED it for an email update to work but it's necessary if you want it to cosmetically look the same as the helpdesk would display it
Agreed
How does the askcodes string get into the resolution field to begin with?
Ah, now that's the clever bit. Our intranet page with the prices of PCs on has a large cheesy "Buy Now" button. Clicking that throws a mailto: into the user's Outlook pre-addressed to the KACE, pre-subjected with Order me a desktop etc, and with @resolution=askcodes in the body. That sets it up as a Procure call and triggers the email to ask for the codes. The only missing item was the tick number to ensure the reply was tacked onto the call.

Is there somewhere I can get a list of the fields so I can add in other bits of data?

Thanks for this, appreciated! [:D]
Answered 09/27/2011 by: Acedashdog
Purple Belt

Please log in to comment
0
Just a subsidiary question; what do the TICK numbers do once we get up over 10,000 tickets?
Answered 09/27/2011 by: Acedashdog
Purple Belt

Please log in to comment
0
Any field should be update-able with @label=value
There are some issues so watch out for spaces in the label names

We are at >150000 tickets and it works fine. e.g. TICK:150000
Answered 09/27/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
No, I meant SQL data field names to put in the code
I know all the non-custom fields are @able :)

Oh, yes, you are over 10k aren't you? Should have spotted that!
Answered 09/27/2011 by: Acedashdog
Purple Belt

Please log in to comment
0
if you attach the mysql query browser (or other tools) to the database then it will list all the objects you can affect in it (tables, columns, etc). The schema is published at http://www.kace.com/support/customer/releases.php but the 5.3 schema hasn't gotten up there yet. 5.3 servicedesk is almost identical to 5.2 though.
Answered 09/27/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Great - thanks. We're still 5.2 I think so that should be fine
Answered 09/27/2011 by: Acedashdog
Purple Belt

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