/build/static/layout/Breadcrumb_cap_w.png

Unassigned Ticket Notification Rule

Created an unassigned ticket rule for any  ticket unassigned for more than 30 minutes . Trying to get it to send notification to Queue owners instead of individual email address.

second part is when emailing results to a specific email the results are not formatted.

How  do i get it to email the queue owners and have the email formatted to be readable


0 Comments   [ + ] Show comments

Answers (3)

Posted by: Hobbsy 1 week ago
Red Belt
0

First using a ticket rule restricts you to a single email address, so if you need to email multiple people you will probably need to create a mailing list which has its own single email address.

If you hide that email in a custom field, ie make it the default value and a hidden field you can call it as a data value in the ticket rule.

Hopefully that helps

 

Posted by: jjayko 1 week ago
White Belt
0

i have this for the sql query , i would like it to notify the queue owners . the query works just need statement to tie it to Email each recipient in query results with Column containing email addresses. Every time i try a different statement from other articles the query crashes

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(HD_TICKET.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 = 'New') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 3 )


Comments:
  • Again, as I said in my first answer, I’m pretty sure you can only add in a single email address in the addressee box a single email address. So no matter how good your SQL is you will only be able to send a single email per ticket….I think - Hobbsy 1 week ago
    • I think there may be some confusion here. I am not looking to "Email the Results", that option is unchecked. We checked off "Email each recipient in query results" and are looking for statement for the creation/selections of "Email Column" - jjayko 1 week ago
Posted by: jjayko 4 days ago
White Belt
0

Got it to work sending to Label using the following SQL Code

Select

  HD_TICKET.*,

  HD_STATUS.NAME As STATUS_NAME,

  HD_IMPACT.NAME As IMPACT_NAME,

  HD_CATEGORY.NAME As CATEGORY_NAME,

  HD_PRIORITY.NAME As PRIORITY_NAME,

  HD_STATUS.STATE,

  U2.FULL_NAME As SUBMITTER_FULLNAME,

  U2.EMAIL As SUBMITTER_EMAIL,

  Q.NAME As QUEUE_NAME,

  (Select

    Group_Concat(USER.EMAIL Separator ', ') As ADDRESSLIST

  From

    LABEL Inner Join

    USER_LABEL_JT On USER_LABEL_JT.LABEL_ID = LABEL.ID Inner Join

    USER On USER.ID = USER_LABEL_JT.USER_ID

  Where

   LABEL.NAME = 'Helpdesk Notifications') As GROUPMAIL

From

  HD_TICKET 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,

  HD_PRIORITY,

  HD_STATUS,

  HD_IMPACT,

  HD_CATEGORY

Where

  HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID And

  HD_STATUS.ID = HD_TICKET.HD_STATUS_ID And

  HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID And

  HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID And

  HD_TICKET.OWNER_ID = 0 And

  HD_TICKET.HD_QUEUE_ID = 3 And

  HD_STATUS.NAME = 'New'


Then just select Email each recipient in query results  fill in subject variables and GROUPMAIL for The Column

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ