/build/static/layout/Breadcrumb_cap_w.png

Error adding a custom query to Kace ticket rule

I am adding the below query (which I copied from this post: http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned)

I receive an error though when I paste it into the ticket rule page and try to "view ticket search results". The error I receive is: 

mysql error: [1054: Unknown column 'SORT_OVERDUE_STATUS' in 'order clause'] in EXECUTE("SELECT PARENT.Title AS 'Parent New User Name', PARENT.CUSTOM_FIELD_VALUE3 AS 'Date of Hire', HD_TICKET.TITLE AS 'Child Title Field', HD_TICKET.ID AS 'ID' FROM HD_TICKET PARENT, HD_TICKET WHERE PARENT.ID = HD_TICKET.PARENT_ID and HD_TICKET.HD_QUEUE_ID = '1' ORDER BY SORT_OVERDUE_STATUS, STATUS_ORDINAL, PRIORITY_NUMBER, SORT_OWNER_NAME, TITLE LIMIT 0,10")

No word from Kace support. What am I doing wrong? I can successfully run many queries with MySQL workbench (including this one), but they will not return results when I query them in Kace. Any ideas? Thanks! 

 SELECT PARENT.Title AS 'Parent New User Name', 
PARENT.CUSTOM_FIELD_VALUE3 AS 'Date of Hire', 
HD_TICKET.TITLE AS 'Child Title Field', 
HD_TICKET.ID AS 'ID'
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID. 

1 Comment   [ + ] Show comment
  • Did part of your query get cut off from the post? The error you included shows a longer SQL statement than what you posted.

    The error you are getting states that the column SORT_OVERDUE_STATUS is an unknown column, and that's probably because there isn't a select statement that creates an alias named that. I would remove all of the ORDER BY statements, since they normally don't matter for a rule. - chucksteel 11 years ago
    • Nothing got cut off. That error was definitely confusing me because it was referring to tables and requests not even in my query. It turns out that if I run the select statement and email myself the results (without doing any update statements), then the query runs and I get the results I need..Kind of absurd. We figured it out though. - michaellynton 11 years ago

Answers (1)

Posted by: Wildwolfay 11 years ago
Red Belt
1

THe thing with the "view tickets" that match your SQL criteria button, is that is an HTML function that is predesigned by KACE to show certain ticket information and sort it and such.  The rule you copied probably still works, so if you RUN NOW and it works in the bottom log, you're good to go.  What that little button does is essentially give you a preview, which is why it is throwing the SORT_OVERDUE_STATUS error as it's trying to sort with something aliased as SORT_OVERDUE_STATUS.

Anyways ---  Hit the RUN NOW button and see what your log tells you.

If you want the preview to run you need to use a SQL format that is provided or develop your own that ensures you get all of the tables it is trying to query for the HTML.  I used to write out ALL the tables so I could alias them for emails, but found that TICKET_IDS was not properly populating (as it was grabbing first numerical value, which was the HD_QUEUE_ID).  For tickets that DONT need an email sent within the ticket rule I just use Select * from HD_TICKET and the other fields that are usually selected when you create a ticket.

For fields you need to alias for emails I use this template:  (The view ticket results usually doesn't produce what your trying to filter, but it does provide a good TICKET_IDS for update query's and the SQL works)

 

   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://vk1000.company.local/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
      -- -- static distribution list
      'asevera@email.com,testone@email.com,testtwo@email.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%'

Comments:
  • Long story short, run the ticket rule and check the log, don't let the "view tickets" button scare you. - Wildwolfay 11 years ago
    • how would i pull USER_NAME name under USER table in ticketing page . i want to pull AD login name (USER_NAME ) in ticketing page when user created ticket - rahimpal 8 years ago
    • how would i pull USER_NAME name under USER table in ticketing page . i want to pull AD login name (USER_NAME ) in ticketing page when user created ticket - rahimpal 8 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