Old SQL listed below bombs after our K1000 upgrade.  Any assistance would be appreciated:

SELECT

      -- ticket fields
      HD_TICKET.CC_LIST, -- $cc_list

      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

      -- 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://k1000/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
     
      HD_TICKET.CUSTOM_FIELD_VALUE0 AS TIME_ZONE, -- $time_zone

      HD_TICKET.CUSTOM_FIELD_VALUE1 AS COMPANY, -- $company

      HD_TICKET.CUSTOM_FIELD_VALUE2 AS COMPANY2, -- $company2

      HD_TICKET.CUSTOM_FIELD_VALUE3 AS MOBILE, -- $mobile
     
      DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE7,'%b %d %Y') AS EMAIL_END, -- $email_end

      HD_TICKET.CUSTOM_FIELD_VALUE13 AS ERP, -- $erp  

      DATE_FORMAT(HD_TICKET.CUSTOM_FIELD_VALUE10,'%b %d %Y') AS TERM_DATE, -- $term_date

      HD_TICKET.CUSTOM_FIELD_VALUE11 AS TERM_TIME, -- $term_time

      HD_TICKET.CUSTOM_FIELD_VALUE12 AS COMPANY12, -- $company12

      HD_TICKET.CUSTOM_FIELD_VALUE14 AS COMPANY14, -- $company14

      -- employee fields
      EMPLOYEE.FULL_NAME as EMPLOYEE_FNAME, -- $employee_fname
     
      EMPLOYEE.EMAIL AS EMPLOYEE_EMAIL, -- $employee_email
     
      -- manager fields
      MANAGER.FULL_NAME as MANAGER_FNAME, -- $manager_fname
     
      MANAGER.EMAIL AS MANAGER_EMAIL, -- $manager_email
     
      -- forward_email fields
      FORWARD_EMAIL.FULL_NAME as FORWARD_EMAIL_FNAME, -- $forward_email_fname
     
      FORWARD_EMAIL.EMAIL AS FORWARD_EMAIL_EMAIL, -- $forward_email_email
     
      -- h_drive fields
      H_DRIVE.FULL_NAME as H_DRIVE_FNAME, -- $h_drive_fname
     
      H_DRIVE.EMAIL AS H_DRIVE_EMAIL -- $h_drive_email

      -- -- example of static distribution list
     'SThurston@olyfast.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

     /* employee ********/ LEFT JOIN USER EMPLOYEE ON EMPLOYEE.ID =  HD_TICKET.CUSTOM_FIELD_VALUE4
    
     /* forward_email ***/ LEFT JOIN USER FORWARD_EMAIL ON FORWARD_EMAIL.ID =  HD_TICKET.CUSTOM_FIELD_VALUE5
    
     /* h_drive *********/ LEFT JOIN USER H_DRIVE ON H_DRIVE.ID =  HD_TICKET.CUSTOM_FIELD_VALUE6
    
     /* manager *********/ LEFT JOIN USER MANAGER ON MANAGER.ID =  HD_TICKET.CUSTOM_FIELD_VALUE9

    WHERE
        C.DESCRIPTION LIKE 'TICKET CREATED%'
        and
        SUBMITTER.FULL_NAME = 'Sarah Corr'
        OR
        SUBMITTER.FULL_NAME = 'Donna Toth'
        OR
        SUBMITTER.FULL_NAME = 'Lee Don'
        OR
        SUBMITTER.FULL_NAME = 'Rob Toto'

4 Comments   [ + ] Show Comments

Comments

  • What error are you getting when the ticket rule runs?
    • Here is what I see from the run log:

      10/17/2017 10:43:03> Starting: 10/17/2017 10:43:03 10/17/2017 10:43:03> Executing Select Query... 10/17/2017 10:43:03>
  • On closer inspection, you are missing a comma after H_DRIVE.EMAIL AS H_DRIVE_EMAIL
    • That is a good catch. Starting to see this wasn't an upgrade issue. Me thinks this sql was never working.
  • The Location fields in the mySQL tables got re-arranged but I don't see any location queries here. Probably what chucksteel said. Formatting errors.
  • FYI - we've started posting database diffs for new versions, so be on the lookout. They are posted as KB articles at support.quest.com. It's a good idea to check these out before upgrading to anticipate any issues that may arise with any custom ticket rules, reports, smart labels, etc.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share