Miscellaneous

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'") - bozadmin 9 years ago

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. - chucksteel 9 years ago

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, - bozadmin 9 years ago

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. - chucksteel 9 years ago

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 - bozadmin 9 years ago

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? - chucksteel 9 years ago

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. - bozadmin 9 years ago

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. - chucksteel 9 years ago

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. - chucksteel 9 years ago

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. - chucksteel 9 years ago

The initial comment is in the $initial_comment variable. The select statement doesn't pull anything for $description or $history. - chucksteel 9 years ago