SQL to update asset table from the Inventory - placed in Helpdesk rules?
I am writing SQL to update our asset records using the fields from the inventory. I have read posts here that it has to go into the HelpDesk custom rules section of KACE. Why? That does not make sense to me since the information from the Inventory and Asset tables are inherently independent from the help desk system. It could be that I just do not have a full understanding of how all three are linked together. I wonder if I am just missing some important information. It seems to me that I would rather have a rule for when a device checks into KACE that a rule to update the asset information should run. Would someone have some clarifying information as to why it is done this way and if there is any documentation on how to properly set up the rule as well, I would be most grateful. I have my SQL statement and have tried to create it according to what I have read, but it fails to work every time I run it. It returns 0 rows if I put it in the select field and I get an error message if I up it in the update section. So I am definitely missing some knowledge.
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
JasonEgg
6 years ago
> I have read posts here that it has to go into the HelpDesk custom rules section of KACE. Why?
I'll let you in on the dirty little secret of Custom Ticket Rules: they offer root access to the SQL database, which is extremely powerful and extremely dangerous. So it doesn't matter if your "ticket rule" has nothing to do with tickets: it's essentially if/then with unrestricted database access.
> I would rather have a rule for when a device checks into KACE
The way I would do this in a CTR is say "select all the devices whose last sync was less than 15 minutes ago" and run it every 15 minutes. Then use the device IDs (which is called "<TICKET_IDS>") in the where clause of your asset UPDATE or INSERT statement.
Comments:
-
Here is the statement that I was using. How do I incorporate Ticket_IDs into the Where clause?
UPDATE ASSET_DATA_5
set FIELD_10011=
(select MACHINE.OS_NAME from MACHINE
JOIN ASSET ON ASSET.NAME = MACHINE.NAME
WHERE ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID - jessburd 6 years ago-
Since I don't know exactly what you're doing I can't speak to your code, but here's the structure you're going for:
UPDATE [table or tables]
SET [the changes to make]
WHERE MACHINE.ID in (<TICKET_IDS>) [AND any other criteria]
"<TICKET_IDS>" will be replaced by the values in the column titled "ID" from the SELECT statement of the CTR. I strongly advise using a test instance of KACE before implementation: A bad CTR could be catastrophic. You should be able to run a small virtual test instance if you've bought a K1 (SMA) appliance; talk to support for details. - JasonEgg 6 years ago-
My apologies, I don't understand. The TICKET_IDS is throwing me off. I will see if I can find someone here who can assist me. Thank you for the information. - jessburd 6 years ago
Posted by:
Hobbsy
6 years ago
Top Answer
The (<TICKET_IDS>) is a variable array that is built from the select statement, so if in the select statement you select tickets then in your update statement, the version that is actually run by your KACE box, you would see (<1,2,3,6,7>) as the selected ticket IDs are added into the Array. IN your case I would suggest that you are careful about updating within inventory as that may screw with the agent updates. But this works fine for Asset records, so if your select statement selects Assets that match a criteria i.e. updated within the last 24 hours, then the array will contain the asset ids
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)
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)
So your SQL may need to look something like the above
Comments:
-
Thank you. I understand this now. - jessburd 6 years ago
