Trying to Update Assets with Ticket Rule
I'm new to KACE and have inherited a system that has been under utilized. One of our admins has just pushed out the agent and we are seeing devices/assets starting to appear, which is great. I'd like to start updating some of the fields on a bulk basis and I read that you could do it through Ticket Rules, but I'm getting an access denied message. Eventually we'll want to update specific fields based on certain criteria, but right now I'm just looking to mark all the assets as "Active". I did a bit of Googling and came up with this very basic set of instructions. 493 is the ID for the "Active" status.
Enter Select SQL:
SELECT ASSET.NAME AS ASSET_NAME
Enter Update SQL:
UPDATE ASSET.ASSET_STATUS_ID
SET ASSET.ASSET_STATUS_ID=493
When I run this I get an error: 12/05/2023 11:25:09> Starting: 12/05/2023 11:25:09 12/05/2023 11:25:09> Executing Select Query... 12/05/2023 11:25:09> selected 609 rows 12/05/2023 11:25:09> Executing Update Query... 12/05/2023 11:25:09> mysqli error: [1142: UPDATE command denied to user 'B1'@'localhost' for table 'ASSET_STATUS_ID'] in EXECUTE("UPDATE ASSET.ASSET_STATUS_ID SET ASSET.ASSET_STATUS_ID=493 ") 12/05/2023 11:25:09> Ending: 12/05/2023 11:25:09
I'm logged in with my own account, which is an admin user, and I don't see the 'B1' user listed anywhere, although I understand it's a default.
Answers (3)
If you simply want to update the asset status you can do that in "Asset Management/Asssets". Select all assets, click on Choose Action/Change Asset status.
Please be very careful with ticket rules and have a test appliance where you can try certain things as you can destroy the database with an update query and then you need to restore the whole appliance. Always create also a backup before you do this and enable offboard backup or download the backup from the appliance so that you are able to restore it.
The very best here is to contact your sales rep or partner where you bought your licenses and get a training for ticket rules as you get better insights and understanding of how that works. Hope that helps you :)
Ah yes, that would be much easier. Thanks for the response. I'm not a programmer by any stretch, but I like trying to figure things out, so I tend to Google a lot of the syntax. You're suggestion is ideal for Asset Status, but I'm hoping to start creating some mass updates base don other parameters. I think the training is the way to go., but for this particular item I did happen to find out that it must have been my Syntax. I ended up finding the right syntax to mark all of my device subtypes of "laptops" to Active. The Active ID is 493 and the ID for the subtype Laptop is 11003, and the below ended up working for me. It likely isn't perfect, but it's a start:
Select SQL:
SELECT ID,ASSET_CLASS_ID FROM `ORG1`.`ASSET`
Update SQL:
UPDATE ASSET
SET ASSET_STATUS_ID="493"
where ASSET_CLASS_ID = 11003
Comments:
-
FYI, your select statement may be nice for looking at the output, but it isn't really doing anything for you. The only information sent from the select statement to the update statement is the ID column, which is available as a concatenated list references as <TICKET_IDS>. Your current statement works, but it will update every asset with class 11003 on every execution. A better approach would be:
select ID FROM ORG1.ASSET where ASSET_CLASS_ID = 11003 and ASSET_STATUS_ID != 493
This will return just those laptops which are not active and your update statement would be:
UPDATE ASSET set ASSET_STATUS_ID = 493 where ID in (<TICKET_IDS>)
Note that the variable is named TICKET_IDS because the rules are designed to act on tickets. - chucksteel 4 months ago-
Thanks for the information, I'll incorporate this into what I'm testing out. Another example of what I'm looking to do is to update our location field based on the name of the asset. I've been at this job for about 6 weeks and am trying to clean things up. The team had labelled the desktops and laptops based on their location, but the actual location field isn't populated. The team can look at an asset labelled SSA1234 and know based on SSA what branch location that asset is in, so I'll be looking to write something that looks at the first three digits and populate the correct location based on that. These are one time cleanup tasks, once things are cleaned up then they'll be maintaining the information as we go, so they'll manually mark a new system as Active when the deploy it, rather than use a scheduled rule to run. - tverberk 4 months ago
-
One time cleanup tasks are better handled using the UI, in my opinion. You can also use an asset import to update all of the assets quickly. Export to CSV, use formulas in Excel, import the CSV. - chucksteel 4 months ago
I figured out that my issue was syntax. My Select and Edit fields were:
SELECT ID,ASSET_CLASS_ID FROM `ORG1`.`ASSET`
UPDATE ASSET
SET ASSET_STATUS_ID="493"
where ASSET_CLASS_ID = 11003