My intent is to capture when the person assigned as the Approver in a ticket changes the approval status to Approved then, set a new Approver (known already) set the Approval to 'none' and, send the new approver an email notification. My query captures the appropriate change and notifies the new approver via email but doesn't update the Approver and Approval fields.

The update statement works fine when run with a wizard made query that just looks at category and status, which leads me to believe I have a valid update statement, it just doesn't execute on the ticket this query captures. NOTE - the run log shows adding the ticket comment, but it does not.

 

Any suggestions would be most welcomed. Thanks

 

**select**

SELECT *,
HD_TICKET.ID as TICKNUM, -- $ticknum
'work.email@work.com' AS NEWTICKETEMAIL, -- $newticketemail
U2.FULL_NAME as SUBMITTER_FULLNAME -- $submitter_fullname
FROM HD_TICKET
JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
JOIN HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID AND C.ID = <CHANGE_ID>
JOIN HD_TICKET_CHANGE_FIELD HTCF on C.ID = HTCF.HD_TICKET_CHANGE_ID
JOIN HD_CATEGORY ON HD_CATEGORY.ID=HD_CATEGORY_ID
JOIN HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
WHERE HD_TICKET.ID = C.HD_TICKET_ID
AND HD_TICKET.HD_QUEUE_ID=4
AND HD_STATUS.NAME != 'Closed'
AND HD_CATEGORY.NAME like 'Data Center%'
AND HTCF.FIELD_CHANGED='APPROVAL'
AND HTCF.AFTER_VALUE='approved'
AND HD_TICKET.APPROVER_ID NOT LIKE '3791'

 

**Update**

update HD_TICKET, USER as T5
    set HD_TICKET.APPROVER_ID = T5.ID,
HD_TICKET.APPROVAL = 'none'
  where T5.ID = '3791' and
        (HD_TICKET.ID in (<TICKET_IDS>))

 

**Run Log**

06/24/2013 11:35:55> Starting: 06/24/2013 11:35:55
06/24/2013 11:35:55> Executing Select Query...
06/24/2013 11:35:55> selected 1 rows
06/24/2013 11:35:55> Adding ticket comments...
06/24/2013 11:35:55>   updated 1 tickets
06/24/2013 11:35:55> Sending ticket notifications...
06/24/2013 11:35:55>   sent mail to 1 of 1
06/24/2013 11:35:55> Executing Update Query...
06/24/2013 11:35:55>   updated 0 rows
06/24/2013 11:35:55> Ending: 06/24/2013 11:35:55

5 Comments   [ + ] Show Comments

Comments

  • This is an On Ticket Save rule - left that part out.
  • Have you tried turning on the option to send the query results to you in email? Sometimes it helps to see what KACE is returning when it runs the rule.
  • Thanks for the suggestion Chuck, I had not done this. Unfortunately it doesn't provide any additional information. I get the same results running the query in workbench. sigh - my love-hate relationship with mysql continues ;)
  • Hmm. Your SQL query would seem to find tickets where the approver isn't 3791 (HD_TICKET.APPROVER_ID NOT LIKE '3791') but then your update query only wants to update tickets where the approver is 3791. That seems like a mismatch.
  • Oddly enough the update works when this query is used in the rule. But it's a very broad capture of tickets and I was trying to capture a fairly narrow target.

    ****Select****

    select HD_TICKET.*,
    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 != 'Closed') AND HD_STATUS.NAME != 'Pending%') AND HD_CATEGORY.NAME like 'Data Center%') AND HD_TICKET.APPROVER_ID NOT LIKE '3791') and HD_TICKET.HD_QUEUE_ID = 4 )
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share