I need help with creating a rule when we assign tickets to techs that will include the Coments instead of them getting just link with the url to the helpdesk. I searched around but could not find something I can use.

Any help is really appricated.

Thank you,

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
  • CHuck - its working awesome. Thank you!
Please log in to comment

Answers

0

Here is the select statement for our "Email owner on ticket changes" rule:

 select HD_TICKET.ID, HD_TICKET.ID as TICKNUM, 
HD_TICKET.TITLE, U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
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, STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
U3.FULL_NAME as UPDATERNAME,
U4.FULL_NAME as INITIALNAME,
UNIX_TIMESTAMP(THISCHANGE.TIMESTAMP),
THISCHANGE.COMMENT,
THISCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.LOCATION AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE THISCHANGE ON THISCHANGE.HD_TICKET_ID = HD_TICKET.ID and THISCHANGE.ID=<CHANGE_ID>
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 = THISCHANGE.USER_ID
left join USER U4 on U4.ID = INITIAL_CHANGE.USER_ID
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_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.OWNER_ID != THISCHANGE.USER_ID
and THISCHANGE.DESCRIPTION not like "%Created%"
and U1.EMAIL != 'helpdesk@company.com'
and HD_TICKET.HD_QUEUE_ID = 2 andHD_STATUS.STATE != 'closed'

This includes joins to the HD_TICKET_CHANGE table that pull in the most recent comment using the <CHANGE_ID> variable and the initial comment on the ticket by selecting the comment with the lowest ID. These can be included in the email to the technician using the $comment and $initial_comment variables. 

(Note: edited to include line breaks and removed company's email address).

 

Answered 06/09/2014 by: chucksteel
Red Belt

  • Thank you Chuck for the quick reply. Besides the email address do I need to change anything else in this. Dont know much about sql.
    And will this also include the comments and the initial comment or full history instead of just last comment?

    Thanks,
    • The ticket's owner email is $owner_email, the email address you see in the query filters out any tickets that are owned by our helpdesk (I guess I should have changed that, oops).

      This doesn't include the full ticket history, just the most recent comment and the first comment.
      • as long as I get the most recent and first comment it should work. But I am getting a sql error when I run this statement. Here is the error.

        6/09/2014 14:58:38> Starting: 06/09/2014 14:58:38
        06/09/2014 14:58:38> Executing Select Query...
        06/09/2014 14:58:38> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)JOIN HD_TICKET_CHANG' at line 1] in EXECUTE("select HD_TICKET.ID, HD_TICKET.ID as TICKNUM, HD_TICKET.TITLE, U1.USER_NAME as OWNER_NAME, U3.USER_NAME as LASTINPUTNAME, 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, STATE, U1.FULL_NAME as OWNER_FULLNAME, U1.EMAIL as OWNER_EMAIL, U2.USER_NAME as SUBMITTER_NAME, U2.FULL_NAME as SUBMITTER_FULLNAME, U2.EMAIL as SUBMITTER_EMAIL, U3.EMAIL as UPDATEREMAIL, U3.FULL_NAME as UPDATERNAME,U4.FULL_NAME as INITIALNAME,UNIX_TIMESTAMP(THISCHANGE.TIMESTAMP),THISCHANGE.COMMENT,THISCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,INITIAL_CHANGE.COMMENT as INITIAL_COMMENT,HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,HD_CATEGORY.NAME AS CATEGORY_NAME,U2.LOCATION AS SUBMITTER_LOCATION,U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,HD_PRIORITY.NAME AS TICKET_PRIORITY,HD_QUEUE.NAME AS QUEUE_NAMEfrom ( HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)JOIN HD_TICKET_CHANGE THISCHANGE ON THISCHANGE.HD_TICKET_ID = HD_TICKET.ID and THISCHANGE.ID=0JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)left join USER U1 on U1.ID = HD_TICKET.OWNER_IDleft join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID left join USER U3 on U3.ID = THISCHANGE.USER_ID left join USER U4 on U4.ID = INITIAL_CHANGE.USER_IDleft join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_IDwhere 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 andHD_TICKET.OWNER_ID != THISCHANGE.USER_ID andTHISCHANGE.DESCRIPTION not like "%Created%"andU1.EMAIL != support@company.com' and HD_TICKET.HD_QUEUE_ID = 2 andHD_STATUS.STATE != 'closed'")
      • Do your line breaks line up with the edited version above? When pasting into ITNinja they aren't always shown so I edited to make sure they are there, but they might not have made it when you pasted into KACE either.
      • I corrected the line breaks and did some testing - I am not getting any email notifications. Any ideas on where to look. I even changed the email address line to mine instead of our helpdesk@compnay.com email just to test.

        Thanks,
      • Turn on the option to email the query results and have them emailed to your address. That will let you know if the query ran and what it returned.
      • I have that enabled and ran the query but got nothing .
        Results are tickets, add a comment to each one

        Send an email for each result row

        Subject:

        Email Column:

        Email Body:


        Run an update query, using the results from the one above



        Run Log:


        Results are tickets, add a comment to each one

        Send an email for each result row

        Subject:

        Email Column:

        Email Body:


        Run an update query, using the results from the one above



        Run Log: 06/11/2014 13:18:20> Starting: 06/11/2014 13:18:20
        06/11/2014 13:18:20> Executing Select Query...
        06/11/2014 13:18:20> selected 0 rows
      • I'd remove the andU1.EMAIL != support@company.com' line to make sure it isn't interfering. Also, did you update the HD_TICKET.HD_QUEUE_ID = 2 to reflect your queue's ID?
      • Yes I did update the queue ID,. If I take out that line how would it it know who to email? will it email it to whoever we assign the ticket to? Cause I do not have defaultticketowner email configured.
      • That line doesn't configure who to email, that's part of the selection of which tickets match the rule. For the who to email part you need to enter one of the columns that are selected in the query. In this case OWNER_EMAIL is probably what you want to enter in the Email Column field.
    • Chuck, I really appreciate all the help so far. I have been trying few different things and cannot achieve a result. in your last response "In this case OWNER_EMAIL is probably what you want to enter in the Email Column field." where do I make this change? Would it be easier to email the owner initial comment on the ticket if we changed the status to "Site Visit"?

      Thanks,
      • If you have the checkbox for "Send an email for each result row" checked then you have the options for sending an email. Those options are Subject, Email Column, and Email Body. The value for Email Column should be set to one of the columns returned by the SQL select statement. The statement that I use has the ticket owner's email address returned in the OWNER_EMAIL column so that is what you want to have as the value for Email Column.
    • That is what I have - I have the Send email for each result checked. Subject: I have Ticket $ticknum. Owner I have OWNER_EMAIL. Yet when I Run Now the query I get nothing. Here is the Run Log result.
      "06/13/2014 09:47:10> Starting: 06/13/2014 09:47:10
      06/13/2014 09:47:10> Executing Select Query...
      06/13/2014 09:47:10> selected 0 rows"
      • Set the rule to run on ticket save then update a ticket. Also, I should have mentioned that the rule doesn't send the email if you update a ticket where you are the owner, so either have someone else change one of your tickets or change a ticket for someone else and see if they get the email.
    • I had it set to run on ticket save. An was making the changes as another user. I am getting email now but no initial comment.

      Here is what I have in the body of the email as well
      Ticket $ticknum owned by "$owner_name" has been reopened because the customer or other non-owner made a ticket update.

      Title: $title
      Ticket: $id
      From: $submitter_fname
      $description
      $history
      $comment

      You may review the ticket here:
      https://helpdesk.company.com/adminui/ticket?ID=$ticknum

      Its giving me the Title, Ticket # and comment but nothing else.
      • The initial comment is in the $initial_comment variable. The select statement doesn't pull anything for $description or $history.
Please log in to comment
Answer this question or Comment on this question for clarity