Ticket rule to trigger an email - no $ticket_number
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 )
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
Answers (9)
Please log in to answer
Posted by:
GillySpy
13 years ago
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.
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%'
Posted by:
Acedashdog
13 years ago
>>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 :)
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 :)
Posted by:
GillySpy
13 years ago
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?
Posted by:
Acedashdog
13 years ago
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]
Posted by:
Acedashdog
13 years ago
Just a subsidiary question; what do the TICK numbers do once we get up over 10,000 tickets?
Posted by:
GillySpy
13 years ago
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
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
Posted by:
Acedashdog
13 years ago
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!
I know all the non-custom fields are @able :)
Oh, yes, you are over 10k aren't you? Should have spotted that!
Posted by:
GillySpy
13 years ago
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.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.