I have been noticing that I will write queries in MySQL and will get results, but when I transfer the query to KACE Service Desk, and will get no results.  The same goes for modified queries that I get from other pages.  The link below will be one of them.

http://www.itninja.com/question/have-k1000-use-the-same-title-for-all-tickets-in-a-process

My question is, since there is no results from KACE, will the update that I write for the KACE Service Desk off of the query that works in MySQL do nothing?
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
Here's a rule I have to pull info from a Parent ticket and feed it to a child ticket in the comments. Might help...

Select:
SELECT PARENT.TITLE, 
HD_TICKET.TITLE, 
HD_TICKET.ID, 
PARENT.CUSTOM_FIELD_VALUE1,
PARENT.CUSTOM_FIELD_VALUE2,
PARENT.CUSTOM_FIELD_VALUE3,
PARENT.CUSTOM_FIELD_VALUE4,
PARENT.CUSTOM_FIELD_VALUE6,
PARENT.CUSTOM_FIELD_VALUE7,
PARENT.CUSTOM_FIELD_VALUE8,
C.COMMENT
FROM HD_TICKET PARENT, HD_TICKET, HD_TICKET_CHANGE C
WHERE PARENT.ID = HD_TICKET.PARENT_ID
and PARENT.HD_QUEUE_ID = 9
and C.HD_TICKET_ID = HD_TICKET.ID
AND HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 10 SECOND)


Update:
UPDATE HD_TICKET PARENT,HD_TICKET_CHANGE C, HD_TICKET
SET HD_TICKET.TITLE = PARENT.TITLE,
C.COMMENT = concat(
'\n Office: ',
PARENT.CUSTOM_FIELD_VALUE1,
'\n Company: ',
PARENT.CUSTOM_FIELD_VALUE2,
'\n EmployeeID: ',
PARENT.CUSTOM_FIELD_VALUE3,
'\n GP Account: ',
PARENT.CUSTOM_FIELD_VALUE5,
'\n Time Entry Account: ',
PARENT.CUSTOM_FIELD_VALUE6,
'\n Pivots Account: ',
PARENT.CUSTOM_FIELD_VALUE7,
'\n Bid Log Account: ',
PARENT.CUSTOM_FIELD_VALUE8
)
WHERE PARENT.ID = HD_TICKET.PARENT_ID
and C.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET.HD_QUEUE_ID = 4
AND HD_TICKET.ID IN (<TICKET_IDS>)

BTW: this does do the title as well. Just remove all the comments BS.
Answered 08/04/2015 by: h2opolo25
Red Belt

  • This seems to do the trick actually. Is it normal that it gives no results on the query? Also, would this be easy enough to go across multiple queues?
    • It should only work when the ticket is created so you probably won't see any results unless you actually enable the rule and create a test ticket. Use a development K1000 VM if at all possible for testing. This is from one Queue to another. You would have to set this rule up in the queue of the "receiving" ticket for each queue you have. So if you have a Parent ticket in queue 2 and child tickets in queues 3 and 4 then you'd need two rules, one in 3 and one in 4.
      • I figured as much would happen. I actually don't see results at all for the query, but the change works.

        These look to be comments on the ticket. To make the changes to the ticket fields itself, would the update be HD_TICKET.CUSTOM_FIELD_VALUE6=PARENT.CUSTOM_FIELD_VALUE6?
  • h2opolo25, Thank you for your help. I modified the query to how I needed it, but what you gave was exactly what I am looking for. This will do nicely for what I was trying to do.
Please log in to comment

Answers

0
I believe that when the service desk runs rules it will append a line that restricts it to the queue that the rule is running in. Would that explain the differences you are seeing?
Answered 08/03/2015 by: chucksteel
Red Belt

  • That is one thing that I am making sure that I am doing. I did notice however it does append the queue number to the query even though you do not see it. However, it is the correct queue number. That is why I'm confused.

    I should receive the same information as the query results from MySQL, shouldn't I?
  • Here is a little snippet of my code I'm using. I am running a process right now. CUSTOM_FIELD_VALUE2 is a single select that has a value of no. MySQL is showing the lines that I figured it should. As for KACE, it is giving me this message:

    There was an error retrieving the data for this page.
    Please refresh the page. If the error persists, try resetting the default page layout.

    Query is below.

    SELECT
    HD_TICKET.ID,
    HD_TICKET.TITLE,
    HD_TICKET.HD_PRIORITY_ID,
    HD_TICKET.HD_IMPACT_ID,
    HD_TICKET.OWNER_ID,
    HD_TICKET.HD_CATEGORY_ID,
    HD_TICKET.CUSTOM_FIELD_VALUE2,
    HD_TICKET.MACHINE_ID,
    HD_TICKET.PARENT_ID
    FROM
    HD_TICKET PARENT, HD_TICKET
    WHERE
    PARENT.ID = HD_TICKET.PARENT_ID
    AND HD_TICKET.CUSTOM_FIELD_VALUE2 = 'no'
    AND HD_TICKET.HD_QUEUE_ID = 3
    • The only thing I can think of is that because you are aliasing the HD_TICKET table as PARENT but then also querying it again as HD_TICKET, it's causing an odd error that MySQL Workbench deals with but KACE doesn't. It doesn't look like you're actually querying anything from the parent ticket but you said this is a snippet. You might be better off getting data from the parent ticket by using a join, but that might depend on what you are querying.
      • Here is a query that I wrote just to test joining the HD_TICKET table to itself. It seems to work great in MySQL and pulls in information, but does not do anything in KACE. I guess my question would be, can you join the HD_TICKET table to itself?

        SQL Query
        SELECT CHILD.ID as CHILD_ID,
        HD_TICKET.PARENT_ID as PARENT_ID,
        HD_TICKET.TITLE as TITLE
        FROM HD_TICKET
        LEFT JOIN HD_TICKET CHILD
        ON CHILD.PARENT_ID = HD_TICKET.PARENT_ID
        WHERE HD_TICKET.ID = CHILD.ID
        AND HD_TICKET.HD_QUEUE_ID = 3
      • I can't think of any right now but I certainly remember seeing queries that use information from both child and parent tickets, so yes, this should work.
Please log in to comment
0
If they have the same custom fields then yes that would work. I did it in comments because the fields between my queues are not the same. It also works with regular fields as well (HD_TICKET.TITLE = PARENT.TITLE, etc...)
Answered 08/04/2015 by: h2opolo25
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity