/build/static/layout/Breadcrumb_cap_w.png
02/03/2017 1530 views
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
Answer Summary:
4 Comments   [ + ] Show comments

Comments

  • under which table we have this field TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and?
    • It's in the HD_TICKET_CHANGE table.
  • 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
  • should i enable this option Enable Service Desk POP3 Server?
  • 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

Answer Chosen by the Author

0
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%".

Answered 02/06/2017 by: chucksteel
Red Belt

  • Thanks for the suggestion but I made that change and still getting the same error
    • 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?
      • 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
    • 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