/build/static/layout/Breadcrumb_cap_w.png

SQL for emailing owner about new Ticket assignment

All
I had a email rule that would email the new owner when a ticket was assigned to them. This has stopped working in version 7. Support told me there were some database changes between in 7 and I would need to update the rule. 

Have any of you done this already? I got the code for the original rule from here.

When I run the query in SQL workbench I get an error at the highlighted line

Use ORG1;
SELECT
      -- ticket fields
      HD_TICKET.ID, -- $id
      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
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://KBOXSERVERNAME/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.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
      -- 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
   'helpdesk@mycompany.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)    --replace the () with brackets
     /* 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

4 Comments   [ + ] Show comments
  • under which table we have this field TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and? - rahimpal 6 years ago
    • It's in the HD_TICKET_CHANGE table. - chucksteel 6 years ago
  • thanks, how do i check that K1000 has no issues with sending email/ SMTP setup?


    when ticket are created im not getting any emails since i updated the K1000 Appliance..



    SELECT
    -- ticket fields
    HD_TICKET.ID, -- $id
    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
    GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
    H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://10.10.3.15/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.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
    -- 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
    'kace.apps@frost.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 '%CREATED%'
    /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1



    SUBJECT:[TICK:$ticknum] NEW TICKET: $title

    Column containing
    NEWTICKETEMAIL


    04/10/2017 07:16:09> Starting: 04/10/2017 07:16:09 04/10/2017 07:16:09> Executing Select Query... 04/10/2017 07:16:09> selected 0 rows


    Freq: on ticket save - rahimpal 6 years ago
  • should i enable this option Enable Service Desk POP3 Server? - rahimpal 6 years ago
  • this is my log


    LOG: MAIN
    Warning: purging the environment.
    Suggested action: use keep_environment.
    LOG: MAIN
    <= kaceapps@KKACE01FS.com U=www P=local S=447
    delivering 1cxYMT-000Hjk-Nx
    Connecting to us-smtp-inbound-1.mimecast.com [205.139.110.2 ]:25 ... connected
    SMTP<< 220 **************************************************************
    SMTP>> EHLO KKACE01FS.com
    SMTP<< 250-us-smtp-1.mimecast.com Hello [66.162.99.99(66.162.220.15)]
    250-AUTH LOGIN
    250-AUTH=LOGIN
    250-XXXXXXXA
    250 XXXB
    SMTP>> MAIL FROM:<kaceapps@KKACE01FS.com>
    SMTP<< 250 Sender OK
    SMTP>> RCPT TO:<kace.apps@abc.com>
    SMTP<< 451 Internal resource temporarily unavailable - https://community.mimecast.com/docs/DOC-1369#451
    LOG: MAIN
    H=us-smtp-inbound-1.mimecast.com [205.139.110.2 ]: SMTP error from remote mail server after RCPT TO:<kace.apps@abc.com>: 451 Internal resource temporarily unavailable - https://community.mimecast.com/docs/DOC-1369#451
    SMTP>> QUIT
    Connecting to us-smtp-inbound-1.mimecast.com [205.139.110.141]:25 ... connected
    SMTP<< 220 **************************************************************
    SMTP>> EHLO KKACE01FS.com
    SMTP<< 250-us-smtp-1.mimecast.com Hello [66.162.99.99(66.162.220.15)]
    250-AUTH LOGIN
    250-AUTH=LOGIN
    250-XXXXXXXA
    250 XXXB
    SMTP>> MAIL FROM:<kaceapps@KKACE01FS.com>
    SMTP<< 250 Sender OK
    SMTP>> RCPT TO:<kace.apps@abc.com>
    SMTP<< 451 Internal resource temporarily unavailable - https://community.mimecast.com/docs/DOC-1369#451
    LOG: MAIN
    H=us-smtp-inbound-1.mimecast.com [205.139.110.141]: SMTP error from remote mail server after RCPT TO:<kace.apps@abc.com>: 451 Internal resource temporarily unavailable - https://community.mimecast.com/docs/DOC-1369#451
    SMTP>> QUIT
    Connecting to us-smtp-inbound-1.mimecast.com [207.211.30.141]:25 ... connected
    SMTP<< 220 **************************************************************
    SMTP>> EHLO KKACE01FS.com
    SMTP<< 250-us-smtp-1.mimecast.com Hello [66.162.99.99(66.162.2.99)]
    250-AUTH LOGIN
    250-AUTH=LOGIN
    250-XXXXXXXA
    250 XXXB
    SMTP>> MAIL FROM:<kaceapps@KKACE01FS.com>
    SMTP<< 250 Sender OK
    SMTP>> RCPT TO:<kace.apps@abc.com>
    SMTP<< 451 Internal resource temporarily unavailable - https://community.mimecast.com/docs/DOC-1369#451
    LOG: MAIN
    H=us-smtp-inbound-1.mimecast.com [207.211.30.141]: SMTP error from remote mail server after RCPT TO:<kace.apps@abc.com>: 451 Internal resource temporarily unavailable - https://community.mimecast.com/docs/DOC-1369#451
    SMTP>> QUIT
    Connecting to us-smtp-inbound-1.mimecast.com [205.139.110.242]:25 ... connected
    SMTP<< 220 **************************************************************
    SMTP>> EHLO KKACE01FS.com
    SMTP<< 250-us-smtp-1.mimecast.com Hello [66.162.99.99(66.162.220.15)]
    250-AUTH LOGIN
    250-AUTH=LOGIN
    250-XXXXXXXA
    250 XXXB
    SMTP>> MAIL FROM:<kaceapps@KKACE01FS.com>
    SMTP<< 250 Sender OK
    SMTP>> RCPT TO:<kace.apps@abc.com>
    SMTP<< 451 IP temporarily blacklisted - https://community.mimecast.com/docs/DOC-1369#451
    LOG: MAIN
    H=us-smtp-inbound-1.mimecast.com [205.139.110.242]: SMTP error from remote mail server after RCPT TO:<kace.apps@abc.com>: 451 IP temporarily blacklisted - https://community.mimecast.com/docs/DOC-1369#451
    SMTP>> QUIT
    Connecting to us-smtp-inbound-1.mimecast.com [207.211.30.221]:25 ... connected
    SMTP<< 220 **************************************************************
    SMTP>> EHLO KKACE01FS.com
    SMTP<< 250-us-smtp-1.mimecast.com Hello [66.162.99.99(66.162.220.15)]
    250-AUTH LOGIN
    250-AUTH=LOGIN
    250-XXXXXXXA
    250 XXXB
    SMTP>> MAIL FROM:<kaceapps@KKACE01FS.com>
    SMTP<< 250 Sender OK
    SMTP>> RCPT TO:<kace.apps@abc.com>
    SMTP<< 451 Internal resource temporarily unavailable - https://community.mimecast.com/docs/DOC-1369#451
    LOG: MAIN
    H=us-smtp-inbound-1.mimecast.com [207.211.30.221]: SMTP error from remote mail server after RCPT TO:<kace.apps@abc.com>: 451 Internal resource temporarily unavailable - https://community.mimecast.com/docs/DOC-1369#451
    SMTP>> QUIT
    LOG: MAIN
    == kace.apps@abc.com R=dnslookup T=remote_smtp defer (-44) H=us-smtp-inbound-1.mimecast.com [207.211.30.221]: SMTP error from remote mail server after RCPT TO:<kace.apps@abc.com>: 451 Internal resource temporarily unavailable - https://community.mimecast.com/docs/DOC-1369#451
    LOG: MAIN
    Warning: purging the environment.
    Suggested action: use keep_environment.
    LOG: MAIN
    <= kace.apps@abc.com U=www P=local S=416
    delivering 1cxYMV-000Hjw-OI
    LOG: retry_defer MAIN
    == kace.apps@abc.com R=smart_route T=transport_25 defer (-53): retry time not reached for any host
    LOG: MAIN
    Warning: purging the environment.
    Suggested action: use keep_environment.
    LOG: MAIN
    <= mischange@abc.com U=www P=local S=440
    delivering 1cxYMV-000Hk2-P7
    LOG: retry_defer MAIN
    == kace.apps@abc.com R=smart_route T=transport_25 defer (-53): retry time not reached for any host
    LOG: MAIN
    Warning: purging the environment.
    Suggested action: use keep_environment.
    LOG: MAIN
    <= Robert.Testuser@abc.com U=www P=local S=430
    delivering 1cxYMV-000Hk8-PZ
    LOG: retry_defer MAIN
    == kace.apps@abc.com R=smart_route T=transport_25 defer (-53): retry time not reached for any host
    LOG: MAIN
    Warning: purging the environment.
    Suggested action: use keep_environment.
    LOG: MAIN
    <= abdr@abc.com U=www P=local S=418
    delivering 1cxYMV-000HkE-Q1
    LOG: retry_defer MAIN
    == kace.apps@abc.com R=smart_route T=transport_25 defer (-53): retry time not reached for any host - rahimpal 6 years ago

Answers (1)

Answer Summary:
Posted by: chucksteel 7 years ago
Red Belt
0

Top Answer

I had to change the C.DESCRIPTION LIKE 'TICKET CREATED%' to C.DESCRIPTION LIKE '%Created%'.

If you look in the HD_TICKET_CHANGE table the description for a ticket being created is no longer in upper case. Also, it is still "Ticket Created" but if I search for that entire string it doesn't seem to match, so I had to trim it just to "%Created%".


Comments:
  • Thanks for the suggestion but I made that change and still getting the same error - johnbodden 7 years ago
    • If you are running the rule in SQL Workbench you will need to find a ticket change ID and substitute it in the query. The K1000 will replace <CHANGE_ID> at runtime with the ID of the change. Are there errors in your run log of the rule? - chucksteel 7 years ago
      • Makes sense I can't use a variable there.Once I used an actual Change.ID i figured out that I needed to change
        C.DESCRIPTION LIKE 'TICKET CREATED%'
        to
        C.DESCRIPTION LIKE '%Changed ticket owner%'

        Thanks Again for your help - johnbodden 7 years ago
    • John, did this finally work for you? if so, can you send the working SQL query. I am trying to do the same exact thing and having trouble. - Mo - mmarchese@cookcountytreasurer.com 6 years ago
 
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