/build/static/layout/Breadcrumb_cap_w.png

Change to SQL backend has broken a custom ticket rule

Hi everyone,

I have a custom ticket rule setup on my system to send an email an email address listed in the Custom 1 of the user that submitted it.  Since the update to 7.x, this rule has been broken.  The error is 1054: Unknown column 'SUBMITTER.CUSTOM_1' in 'field list'.

Here's the SQL we're using:

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://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.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
      SUBMITTER.CUSTOM_1 AS SUBMITTER_CUSTOM_1,     -- $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
      'bpendry@martinsnet.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

I need to know what we need to change to make this work if possible.  I've taken a look at the database, and it looks like the custom mappings are now held outside of the user table in their own table.

Thanks in advance for the help

Nick

0 Comments   [ + ] Show comments

Answers (2)

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

Top Answer

There have been several posts about this issue, here is one:

Searching the site for user_field_value will yield more.

There is also a KB article available here:


Comments:
  • Hi Chuck, thanks for the reply. I've modified the script a few times to try and make this work, I keep getting an error.

    1054: Unknown column 'USER_FIELD_VALUE.USER_ID' in 'on clause'

    Here's the current SQL that I think should work. I'm not sure what I'm missing....

    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://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.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
    SUBMITTER_CUSTOM.FIELD_VALUE AS SUBMITTER_CUSTOM_1, -- $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
    'bpendry@martinsnet.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
    /* submitter custom*/ LEFT JOIN USER_FIELD_VALUE SUBMITTER_CUSTOM ON SUBMITTER.ID = USER_FIELD_VALUE.USER_ID AND USER_FIELD_VALUE.FIELD_ID = 1
    /* 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 - nwilliams@martinbros.com 6 years ago
    • In your join statement you are creating an alias for the USER_FIELD_VALUE table and calling it SUBMITTER_CUSTOM. Once you do that you need to refer to the alias and not the actual table name:
      LEFT JOIN USER_FIELD_VALUE SUBMITTER_CUSTOM ON SUBMITTER.ID = SUBMITTER_CUSTOM.USER_ID AND SUBMITTER_CUSTOM.FIELD_ID = 1 - chucksteel 6 years ago
      • Sorry for the slow response. I think we have a working script, but for some reason it's always showing 0 rows selected when I try and test.

        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://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.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
        SUBMITTER_CUSTOM.FIELD_VALUE AS SUBMITTER_CUSTOM_1, -- $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
        'bpendry@martinsnet.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
        /* submitter custom*/ LEFT JOIN USER_FIELD_VALUE SUBMITTER_CUSTOM ON SUBMITTER.ID = SUBMITTER_CUSTOM.USER_ID AND SUBMITTER_CUSTOM.FIELD_ID = 1
        /* 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

        If I remove the "AND C.ID=<CHANGE_ID>" and run the query in MySQL Workbench, I get correct results for all the tickets in the system. But for some reason this still doesn't seem to work.

        Any thoughts?

        I appreciate your help.

        Nick - nwilliams@martinbros.com 6 years ago
      • You can't effectively test rules that run on save because of the CHANGE_ID variable. The best method is to save the rule and then create a ticket to test. I have a queue setup for testing rules that only admins can see. - chucksteel 6 years ago
      • I'm afraid the last run log isn't showing much. Simply this: 12/06/2017 11:33:07> Starting: 12/06/2017 11:33:07 12/06/2017 11:33:07> Executing Select Query... 12/06/2017 11:33:07> selected 0 rows

        as I said before, I can see the results correctly, so I'm not sure what to look at next - nwilliams@martinbros.com 6 years ago
      • Try adjusting the C.DESCRIPTION to LIKE '%Created%'. - chucksteel 6 years ago
      • Changing that doesn't seem to have made any difference at all. Is there a better way to trouble shoot this than to just look and see if the save has results? - nwilliams@martinbros.com 6 years ago
      • The CHANGE_ID Variable didn't change with 7.x did it? - nwilliams@martinbros.com 6 years ago
      • You can look at the HD_TICKET_CHANGE table for the ticket you created and see what it reports. You could also remove the C.DESCRIPTION like "%Created%" so that it should match on any change to the ticket and see if it triggers an email. - chucksteel 6 years ago
      • No, the variable is still the same. Another way to trouble shoot is to find the ID for the change in the HD_TICKET_CHANGE table for that ticket and then run the query using that ID in MySQL Workbench where the variable normally would be. - chucksteel 6 years ago
      • It seems to work when I take the WHERE
        C.DESCRIPTION LIKE '%CREATED%' out. So that's a step in the right direction. - nwilliams@martinbros.com 6 years ago
      • We're working now! I hate to say it was mostly how I was testing that was the issue, but now the issue is resolved. Thank you very much for your time and effort to help a handicapped scripter like myself :) - nwilliams@martinbros.com 6 years ago
Posted by: anonymous_9363 6 years ago
Red Belt
0
Using your email address as a nickname on a public forum probably isn't up there in the list of The World's Greatest Ideas.

Comments:
  • Just curious why you say that. It's becoming fairly common practice to use email addresses as usernames on websites. - chucksteel 6 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

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