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.
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
Top 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.
Comments:
-
My rule is in a queue all on its own with no tickets since these are not going to be referencing any help desk tickets. Thank you for that previous suggestion as it helps keep things organized.
My goal is to update the asset table when there is a matching record in the Inventory table. We want to pull the matching information from the information reported by the device into the asset table and keep it current. Once this is working, we are likely going to include other columns as they are identified as important to keep for our records.
Once the device is deactivated or replaced, then that device will be removed from the inventory (along with all the information from the agent) but we want to keep information about that device in the asset table. Right now, we have to either manually type this into the asset table or export out the information in a report and then import back into the asset table - which is causing other issues that can be avoided if we can get this information to automatically copy over with a rule.
My current select statement is :
SELECT
AD5.ID as ADID,
AD5.FIELD_10011 AS OSVERSION,
AD5.FIELD_10008 AS AMANUFACTURER,
AD5.FIELD_10012 AS IPADDRESS,
AD5.FIELD_10013 AS MACADDRESS,
A.NAME AS ANAME,
A.ASSET_DATA_ID as ASID,
M.NAME as MNAME,
M.MAC AS MAC,
M.IP AS IP,
M.OS_NAME AS OS,
M.CS_MANUFACTURER AS MMANUFACTURER
FROM MACHINE M, ASSET A, ASSET_DATA_5 AD5
WHERE A.NAME = M.NAME
AND A.ASSET_DATA_ID = AD5.ID
ORDER BY AD5.ID
The update statement I last tried was:
UPDATE ASSET_DATA_5
SET
OSVERSION = OS,
AMANUFACTURER = MMANUFACTURER,
IPADDRESS = IP,
MACADDRESS = MAC
We may eventually add a where statement to only update records when any of the fields do not match, but one step at a time. I have to get this working first. - jessburd 6 years ago-
The variable name is TICKET_IDS, but it doesn't need to contain IDs of service desk tickets, that is just what the variable is called. In your select statement you aren't including a column named ID, so it won't populate that variable. For your example this select statement will return the ASSET_DATA_5 rows that have a machine name matching their asset name and the data in FIELD_10011 doesn't match the machine's OS_VERSION:
SELECT AD5.ID
FROM ASSET_DATA_5 AD5
JOIN ASSET on ASSET.MAPPED_ID = AD5.ID and ASSET.ASSET_TYPE_ID = 5
JOIN MACHINE on MACHINE.NAME = ASSET.NAME
WHERE AD5.FIELD_10011 != MACHINE.OS_VERSION
Your update statement can then look like this:
UPDATE ASSET_DATA_5
set FIELD_10011 = (select
MACHINE.OS_VERSION
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>)
Keep in mind that this assumes your Computer asset type is set to match computer names to asset names, we use the serial number in our environment.
This rule only updates FIELD_10011 to make things simpler. I would recommend setting up a separate rule for each field that you want to keep updated. This should reduce the number of updates to only those needed (instead of updating every field every time the rule runs). You can also easily duplicate the rule and change FIELD_10011 and the matching column from the MACHINE table when you want to keep another column in sync.
For testing purposes, this will also allow you to identify one machine and add:
AND MACHINE.ID = 1234
to the select statement. Replace 1234 with the ID of a MACHINE that doesn't have FIELD_10011 correct and run the rule. - chucksteel 6 years ago-
Excellent. Thank you. This does exactly what we needed and I understand it more. I think this will go a long way for me when working on future rules that we may need. - jessburd 6 years ago
