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:
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

Comments

  • 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:
      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'
Please log in to comment

Answers

0
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.
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.

      SELECT ID FROM ASSET_DATA_5 WHERE 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.
Please log in to comment
Answer this question or Comment on this question for clarity