Title should read : Creating ( < TICKET_IDS > ) variable array - Correct Syntax (with no spaced in the parentheses. The original text keeps getting hidden.)

Does the select statement in the custom ticket rules create the array or do I have to still define that information? After searching on here, in the knowledge case on Quest's website, and reviewing the 8.0 admin guide, I cannot find anything that helps me understand how to set this up.

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:
set FIELD_10011= 

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?
Answer Summary:
1 Comment   [ - ] Hide Comment


  • Can you post your select statement?
    • That is the problem. I do not know how to start it. I am not sure exactly what my select statement should have in it. I know I can't expect someone else to write it, so that is why I was hoping for some examples where others have used a rule to update one table in KACE from another (like me trying to copy data from the inventory to the asset table so when the device is ultimately removed from inventory, important device information remains in the asset table). It seems I have to write a separate select statement from my update statement.

      I am used to statements like this in SQL Server:
      Table_A.col1 = Table_B.col1,
      Table_A.col2 = Table_B.col2
      Some_Table AS Table_A
      INNER JOIN Other_Table AS Table_B
      ON Table_A.id = Table_B.id
      Table_A.col3 = 'cool'
Please log in to comment

Answer Chosen by the Author


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
where HD_TICKET.CUSTOM_FIELD_VALUE7 = 'Set automatically for Items' 
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 = 
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.

Answered 01/19/2018 by: chucksteel
Red Belt

  • 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?
    • 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.
      • 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"?
      • 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.
      • 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.
Please log in to comment
Answer this question or Comment on this question for clarity


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:
Then you can update just those rows using something like this:
set FIELD_10014 = (select
    when ASSET.NAME = "" then MACHINE.NAME
    else ASSET.NAME
    end as SERIAL
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.
Answered 01/15/2018 by: chucksteel
Red Belt

  • 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' ")
  • 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.
    • 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.

      • 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.
      • 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 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.
      • 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.
      • 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.
      • 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.
      • 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?
Please log in to comment