KACE Product Support Question
SQL Custom Rules - Relation of select statement results and update statements
01/24/2018 1296 views
Do the aliases in the Select statement carry over to the update statement or do you have to redefine everything over again?
How do I reference the results so that I can execute a statement to update custom Asset fields with information from the Inventory? Do the results from the select statement have a query name to reference instead of the source table?
I have a working select statement that shows me exactly what I need. However, every update statement returns an error: mysqli error: [1054: Unknown column ... ]. It seems that it cannot find the columns whether I use the database name or the alias that I have in the select statement.
I am trying my best to grasp how KACE handles SQL statements. It may be similar to what I have learned in the past but right now, I keep learning that I do not understand SQL in KACE as much as I thought I did. It seems that every step forward is two steps back. I have read http://www.itninja.com/blog/view/k1000-custom-ticket-rules-using-an-inline-view-to-query-update-tables-in-one-pass and http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example. Unfortunately, since my statements are not using the Tickets tables, this information does not answer my question. I have been searching all day online for information but there is nothing out there that I can find to help guide me. I am not asking for someone else to write these for me, I just need to understand how it works in KACE so that I can write the rules on my own.
Answer Chosen by the Author
Please log in to answer
The only information passed to the update statement is the TICKET_IDS variable, which is a comma delimited list of the values returned as ID in the select statement. This means that you need to craft your select statement in a way that it will pass a list of values that you can act upon. For more complicated update statements you may need to use a sub-select statement or joins to other tables to get the data you want to add to the target table (ASSET_DATA_x, for instance).
Feel free to post details on what you are trying to accomplish, including select statements.
Answered 01/25/2018 by: chucksteel