/build/static/layout/Breadcrumb_cap_w.png

Service Desk Custom Notification Rule

Hi Everyone

I currently have a custom rule setup which sends an email to a group of admins saying a new ticket has been created. In some instances though one of the admins logged the ticket themselves and already made someone the owner before they saved the ticket initally. Therefore a email to the group of admins is just a waste.

My question is, is there a way to send the email to the group only in the case that a new ticket has been put in with the owner field blank.

 

Thanks for your help


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
AND HD_TICKET.OWNER_ID = 0
Posted by: chucksteel 10 years ago
Red Belt
1

In your select statement for the rule you should be able to add

AND HD_TICKET.OWNER_ID = 0

This will make the rule only match tickets without an owner.

If you aren't sure where this goes in the SQL you can post the select query here and we can show you.


Comments:
  • Thanks for the response Chucksteel. I tried the statement that you suggested in a couple of different places...no luck.
    Here is the query:

    SELECT
    -- ticket fields
    HD_TICKET.ID, -- $id
    HD_TICKET.ID AS TICKNUM, -- $ticknum
    HD_TICKET.TITLE, -- $title
    DATE_FORMAT(HD_TICKET.CREATED,'%M %d, %Y') AS CREATED, -- $created
    DATE_FORMAT(HD_TICKET.CREATED,'%I:%i:%s %p') AS TCREATED, -- $tcreated
    DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified


    -- change fields
    C.COMMENT, -- $comment
    C.DESCRIPTION, -- $description
    GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
    H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
    ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history


    -- 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.FULL_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.CUSTOM_3 AS SUBMITTER_DNAME, -- $submitter_dname
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email

    -- about priority
    P.NAME AS PRIORITY, -- $priority

    -- about status
    S.NAME AS STATUS, -- $status

    -- about impact
    I.NAME AS IMPACT, -- $impact

    -- about category
    CAT.NAME AS CATEGORY, -- $category

    -- other fields
    -- -- example of static distribution list
    'xx@xx.com' AS NEWTICKETEMAIL -- $newticketemail


    FROM HD_TICKET
    /* latest change ***/ 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
    WHERE
    C.DESCRIPTION LIKE 'TICKET CREATED%'

    /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1 - Kevino2010 10 years ago
    • You want to place additional criteria in the WHERE clause, so you need to add it beneath C.DESCRIPTION LIKE 'TICKET CREATED%' - chucksteel 10 years ago
      • I cannot thank you enough! - Kevino2010 10 years 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

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