Creating () variable array - Correct Syntax
Title should read : Creating ( < TICKET_IDS > ) variable array - Correct Syntax (with no spaced in the parentheses. The original text keeps getting hidden.)
Example: I am trying to set up a statement that will update the asset information using information already in the inventory table. I have the update statement:
UPDATE ASSET_DATA_5
set FIELD_10011=
(select MACHINE.OS_NAME from MACHINE
JOIN ASSET ON ASSET.NAME = MACHINE.NAME
WHERE (ASSET.ID in ( < TICKET_IDS > ) AND ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID)
There are no spaces in my SQL statement in KACE around the < and > in the WHERE statement but when this gets posted here, whatever is in the parenthesis is hidden.
So I need to have the Select statement correct and I know that incorrect statements can have negative repercussions on the system. Normally, I try to figure it out on my own, but I do not want to impact performance if I can avoid it. I have seen examples if the select statement for rules that are directly related to help desk tickets but since my statements will not have anything to do with the tickets themselves, I am not sure how to structure it correctly. I believe my select statement will target the Asset_Data_5 table since that is where I will be making the updates. Are there any examples that can be provided for me to review?
1 Comment
[ + ] Show comment
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
The select statement needs to return a column named ID, and the values in that column will be placed in a comma separated list accessed with the TICKET_IDS table. For example, this statement will select records in ASSET_DATA_5 that don't have a value in FIELD_10014:
SELECT ID FROM ASSET_DATA_5 where FIELD_10014 = ""
Then you can update just those rows using something like this:
UPDATE ASSET_DATA_5
set FIELD_10014 = (select
case
when ASSET.NAME = "" then MACHINE.NAME
else ASSET.NAME
end as SERIAL
FROM ASSET
JOIN MACHINE on MACHINE.ID = ASSET.MAPPED_ID
where ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID and ASSET.ASSET_TYPE_ID = 5)
where ID in (<TICKET_IDS>)
Regarding the formatting, this site doesn't automatically escape open and closing angle brackets, so your browser is seeing the variable inside of them as a HTML tag.
Comments:
-
I just tried it as a test and got an error:
There were syntax errors in your query.
mysqli error: [1054: Unknown column 'FIELD_10014' in 'where clause'] in EXECUTE("SELECT ID FROM ASSET_DATA_5 where FIELD_10014 = "" and HD_TICKET.HD_QUEUE_ID = '5' ") - jessburd 6 years ago -
I changed and got a new error:
The statement is now
SELECT ID FROM ASSET_DATA_5.FIELD_10014 where FIELD_10014 = ""
And I got this error:
There were syntax errors in your query.
mysqli error: [1142: SELECT command denied to user 'B1'@'localhost' for table 'FIELD_10014'] in EXECUTE("SELECT ID FROM ASSET_DATA_5.FIELD_10014 where FIELD_10014 = "" and HD_TICKET.HD_QUEUE_ID = '5' ")
I don't understand this because out report user is R1, not B1. I may have to contact their support again. - jessburd 6 years ago-
You probably need to use FIELD_10011, my example uses FIELD_10014 but you may not have that field ID in your database. Also, you are trying to select from a table named ASSET_DATA_5.FIELD_10014, which doesn't exist. The table name is ASSET_DATA_5 and the column is FIELD_10014 or FIELD_10011.
SELECT ID FROM ASSET_DATA_5 WHERE FIELD_10011 = "" - chucksteel 6 years ago-
I missed that number difference. Thank you for pointing that out. I am still getting syntax errors.
There were syntax errors in your query.
mysqli error: [1054: Unknown column 'HD_TICKET.HD_QUEUE_ID' in 'where clause'] in EXECUTE("SELECT ID FROM ASSET_DATA_5 WHERE FIELD_10011 = "" and HD_TICKET.HD_QUEUE_ID = '5' ")
I do not understand why this is so difficult. We seem to be getting syntax errors on statements that others are having no problems with. I will continue to try and figure this out but it is so frustrating to have everything come back with some sort of error. I have a few more examples of select statements that people have shared on these boards, but my confidence of them working is strong. - jessburd 6 years ago -
Your statement doesn't include any relationship between the ASSET_DATA_5 table and the HD_TICKET table, so it has no way of knowing what HD_TICKET.HD_QUEUE_ID is. If you didn't add that manually, then the appliance may have added it so that the statement only runs on tickets in the queue where the rule lives. - chucksteel 6 years ago
-
I do not understand how to link the ASSET_DATA_5 table and the HD_TICKET table. I have another post where I was asking why the rules have to be in the Help Desk Ticket area because I want to write rules that have nothing to do with the tickets. I just need to copy data from the inventory table to the asset table for long term storage since the device information is removed when a device is deleted from the inventory table. Supposedly, this can be done but I am running into nothing but errors. I will be the first to admit that I most likely don;t know enough about KACE at this time but there are not many resources for learning how to do this. - jessburd 6 years ago
-
Yes, creating rules that work this way can be frustrating. Two questions:
What are the schedule settings for the rule?
Are you running the rule manually to test, or are you using the "View Ticket Search Results" link? If you are using the link to test the results, then it will append the "and HD_TICKET.HD_QUEUE_ID = '5'" line to the end of the rule. In general, I don't recommend ever clicking that link. - chucksteel 6 years ago -
And one more thing, I recommend creating a separate queue to hold your rules that automate processes that are not part of the ticketing system. It helps to keep things cleaner. Just remember to setup the queue such that no one can be an owner or submitter in the queue so that it doesn't show up in the list of queues. - chucksteel 6 years ago
-
Thank you for the suggestion on a "Rule Queue". I have set it up and moved the rule to that queue. I am planning on having it run every morning. That way, the system can catch any systems that are added or modified the previous day. Eventually, I may change that to a few times a week or even weekly, once I get an idea of how much that information changes here.
As far as testing it, I am using the "View Ticket Search Results" link. I have also tried testing them in HeidiSQL and it returns similar errors - at least with the <CHANGE_ID> error that I have asked about in another post. I have not yet tried to run this one in HeidiSQL yet. I was not aware that the link on the rule page added that line, but now that I know, I think I will avoid using it. This is complicated enough without KACE sticking in syntax that I don't want.
I was not running it manually because I know that an incorrectly written SQL statement can negatively impact the database. So I wanted to try to confirm the syntax as much as possible before running the rule. - jessburd 6 years ago -
Going back to linking the HD_Ticket table to other tables, is there an example of where someone did that to accomplish something not related to the ticket tables? - jessburd 6 years ago
Posted by:
chucksteel
6 years ago
Top Answer
Here is an example showing a relationship between the ticket table and one of the asset tables. This rule is from our purchasing queue. All purchased items are created as an asset first and when an item is purchased it is selected in the asset field of the ticket. This rule appends the ticket ID to a field on that asset type.
Here is the select statement:
select HD_TICKET.*,
ASSET_DATA_11.FIELD_10003 as FIELD_10003
from HD_TICKET
left join ASSET on ASSET.ID = HD_TICKET.ASSET_ID
left join ASSET_DATA_11 on ASSET.ASSET_DATA_ID = ASSET_DATA_11.ID
where HD_TICKET.CUSTOM_FIELD_VALUE7 = 'Set automatically for Items'
and HD_TICKET.HD_QUEUE_ID = 11
There is a join statement that relates the asset table to the ticket, which is followed by another join statement to the ASSET_DATA_11 table. The rule runs on ticket save and only runs if the custom field 8 is the default value 'Set automatically for Items'. Another rule populates that custom field with the price of the purchased item based on the data in the purchased item asset.
Here is the update statement:
update ASSET_DATA_11
set FIELD_10003 = concat(FIELD_10003, '<TICKET_IDS>,')
where ID =
(select ASSET.ASSET_DATA_ID from ASSET
left join HD_TICKET on HD_TICKET.ASSET_ID = ASSET.ID
where HD_TICKET.ID = <TICKET_IDS>)
The update statement uses a sub select statement to find ID of the asset which has been purchased, based on the ticket ID. In this case, the TICKET_IDS variable will contain the ID of the ticket which was just saved.
Comments:
-
Thank you for this example. I do have one more question. From your example, there appears to be a need for a related ticket for these statements. What happens if there are no tickets in that queue? - jessburd 6 years ago
-
This rule is specific for that queue, so there will be tickets. If you are creating a rule that is based on information stored in tickets, then the rule needs to live in the queue with the tickets. If your rule doesn't depend on ticket data, then it does not need to have a relationship to the HD_TICKET table. - chucksteel 6 years ago
-
6 days ago, you commented "Your statement doesn't include any relationship between the ASSET_DATA_5 table and the HD_TICKET table, so it has no way of knowing what HD_TICKET.HD_QUEUE_ID is. If you didn't add that manually, then the appliance may have added it so that the statement only runs on tickets in the queue where the rule lives." I understood this to mean that I have to link it to the HD-TICKET table. Now I am understanding that I do not have to link it?
Should I be able to SELECT ID FROM ASSET_DATA_5 WHERE FIELD_10011 = "" without any further modification in the select box?
Could the syntax error I received when I tried it be a result of using the "View Ticket Search Results"? - jessburd 6 years ago -
Yes, the syntax error was because you were clicking the "View Ticket Search Results" link. When you click that link it automatically added a reference to HD_TICKET.HD_QUEUE_ID. If you don't click that link, then you don't have to worry about a relationship with the HD_TICKET table. - chucksteel 6 years ago
-
Thank you for being so patient with me and providing me with so much information. I know it was a lot of questions, but you have helped me understand this better than before. - jessburd 6 years ago
I am used to statements like this in SQL Server:
UPDATE
Table_A
SET
Table_A.col1 = Table_B.col1,
Table_A.col2 = Table_B.col2
FROM
Some_Table AS Table_A
INNER JOIN Other_Table AS Table_B
ON Table_A.id = Table_B.id
WHERE
Table_A.col3 = 'cool' - jessburd 6 years ago