Difference between SQL queries in MySQL Workbench and Service Desk
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?
0 Comments
[ + ] Show comments
Answers (3)
Answer Summary:
Please log in to answer
Posted by:
h2opolo25
9 years ago
Top Answer
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.
Comments:
-
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? - zgillette 9 years ago
-
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. - h2opolo25 9 years ago
-
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? - zgillette 9 years ago
-
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. - zgillette 9 years ago
Posted by:
chucksteel
9 years ago
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?
Comments:
-
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? - zgillette 9 years ago -
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 - zgillette 9 years ago-
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. - chucksteel 9 years ago
-
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 - zgillette 9 years ago -
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. - chucksteel 9 years ago
Posted by:
h2opolo25
9 years ago
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...)
