/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)

Answer Summary:
Posted by: Hobbsy 4 months 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 4 months ago
Senior 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 4 months 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 4 months ago
Posted by: jjayko 4 months ago
Senior White Belt
0

Top Answer

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


Comments:
  • Thanks for this. helped me a lot. I didnt know you could use two FROM statements. I was trying to work it all into one and was failing.

    Anyway, I wanted to be able to be certain it was a new ticket and I also wanted to only send when the category was something in particular. So stole your email list build and incorporated it into my rule which sends on a new ticket to a distribution list.. (removed DL). I have not tested the email yet, its in my dev server which cant send or receive email but fairly confident it builds the comma separated list as you wrote it. Quite frankly, I dont know what the two last commands are there for and if they are necessary even. This was originally provided by kace pro services a few years ago. CTEXT is the category name and C.description is the very last "Change" that was made to the ticket.. I think it uses some sort of built-in variable (<CHANGE_ID>).

    EDIT: I tested this today and it does work correctly. The reason I made this change is because with the authors script if someone were to go in and say press apply changes, the email would be sent again.


    SELECT
    HD_TICKET.ID AS TICKNUM, -- $ticknum
    HD_TICKET.TITLE, -- $title
    DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
    DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
    -- change fields
    C.COMMENT, -- $comment
    C.DESCRIPTION, -- $description

    -- about the updater
    UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
    UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
    UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
    IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
    -- about the owner
    OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
    OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
    OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
    IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
    -- about the submitter
    SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
    -- about priority
    P.NAME AS PRIORITY, -- $priority
    S.NAME AS STATUS, -- $status
    I.NAME AS IMPACT, -- $impact
    CAT.NAME AS CATEGORY, -- $category
    -- group email addr Comma Separated
    (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 = 'GRP_ACCESS_BADGE_ADMINS') AS GROUPMAIL
    FROM HD_TICKET

    JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE_ID>
    /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
    /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
    /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
    /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
    /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
    /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
    /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
    LEFT JOIN HD_CATEGORY CTEXT ON (CTEXT.ID = HD_TICKET.HD_CATEGORY_ID)

    WHERE
    C.DESCRIPTION LIKE '%CREATED%' AND
    HD_TICKET.OWNER_ID = 0
    AND HD_TICKET.HD_QUEUE_ID = '4'
    AND (CTEXT.NAME like '%Access Control%')
    GROUP BY HD_TICKET.ID
    HAVING 1=1 - barchetta 2 weeks ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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