SQL Custom Rules - Relation of select statement results and update statements
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.
Please log in to answer
Posted by: chucksteel 3 years ago
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.