KACE How to use SQL to delete assets, or other way to delete multiple assets
I have more that 1000 orphaned assets in my K1000 database due to MIA computer deleting after 6 months and manual inventory item deletes. I would like to have a way to delete all of these orphaned assets (already have an SQL report I wrote to report on them)
Community Chosen Answer
If you can identify them as orphaned assets programmatically then you could create a custom rule in the service desk to delete them. Use the rules wizard to create a generic rule, it doesn't matter what you select in the wizard because you'll change the SQL code anyway.
The rule's select statement will be based on the SQL you already have in your report. Be sure to have it return a column called ID will will be contain the asset ID. The values returned as ID will be passed to the update query inside a variable called TICKET_IDS.
Before adding the update query I would check the box to have the select query results emailed to myself and make sure they correct assets are identified. Once that is verified you can add the update query and run the rule manually.
The update query would then be:
DELETE FROM ASSETS WHERE ASSET.ID in <TICKET_IDS>