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)
Answers (2)
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>
Comments:
-
That worked, but I had to change your delete statement to the following.
DELETE FROM ASSET WHERE (ASSET.ID in (<TICKET_IDS>))
Here is the select statement that I used
SELECT ASSET.ID
FROM ASSET
where ASSET.ASSET_TYPE_ID=5
and ASSET.NAME NOT IN (SELECT MACHINE.NAME FROM MACHINE ORDER BY MACHINE.NAME)
ORDER BY ASSET.ID
Thank you so very much for your help!! - elpalmer@ufl.edu 11 years ago-
This is just what I was looking for. Many thanks to you both. - rockhead44 10 years ago
Thank you! This is just the information I needed. I'll give it a try today.