Hi,

I want to create a custom ticket rule to email OWNER_EMAIL and CC_LIST when the "Email each recipient in query results - Column containing email addresses:" is checked and filled in.  I did see some CONCAT rules but could not figure out how to add it to my current SQL code.  Any help would be appreciated.  I know it should be easy, I just have very little SQL knowledge and can't quite grasp how to add the fields to my code.  I could make 2 rules but would prefer not to.

Here is my code:

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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
                        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_CATEGORY.NAME = 'Contract') AND HD_STATUS.NAME != 'Closed') AND HD_TICKET.DUE_DATE >= now()) and HD_TICKET.HD_QUEUE_ID = 1) AND  TIMESTAMP(HD_TICKET.DUE_DATE) <= DATE_ADD(NOW(),INTERVAL 30 DAY))
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
The category CC_LIST is stored in HD_CATEGORY.CC_LIST so you'll need to join that with U1.EMAIL (U1 is the alias for the join to the USER table on owner). I would use CONCAT_WS so we can specify the separator as a comma.

In the select portion of the statement you need to add this line:
CONCAT_WS(",", HD_CATEGORY.CC_LIST, U1.EMAIL) AS TICKETEMAIL

The select list is a comma separated list of columns to select, so to add this to the end of the list find the last entry, which is Q.NAME as QUEUE_NAME and place a comma at the end of that line and then add the new line given above.

The column containing email address will be TICKETEMAIL.


Answered 02/15/2016 by: chucksteel
Red Belt

  • It is still only emailing the Ticket Owner email. I want it to email both the Ticket Owner and the CC List.
    • Try turning on the option to email you the query results so that you can verify what is being returned in the TICKETEMAIL column.
      • Only the Ticket Owner is being returned in the query with a comma before that. So the CC_List is not getting pulled.
      • How many addresses are in the CC_LIST? At one point there was a bug with the CC_LIST that if there were too many addresses the email didn't get sent. We switched to using email groups so I'm not sure if that has been fixed.
      • Only 1 email address in the CC_List
      • It must not be pulling the category correctly, but I'm not sure why.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share