Deleting unlinked device assets?
Is there any way to find and delete assets that are not linked to a device anymore? I know that I can visually identify them by whether they have a bracketed device name next to the entry. But Kace was not well maintained over the years and we have hundreds of orphaned records that I want to remove and clean up, so manual identification is not really optimal.
Answers (1)
Top Answer
There is no quick way to do this unfortunately, due mainly to the fact that you cannot create labels in the Asset Management space, so you cannot sort device assets into groups and also due to the fact that the data fields you need are not available via the search options
Running the following SQL in a report will show you the extent of the issue
SELECT ASSET.ID,
ASSET.NAME
FROM ASSET ASSET
WHERE (ASSET.ASSET_TYPE_ID = 5) AND (ASSET.MAPPED_ID = 0)
ORDER BY ASSET.ID, ASSET.NAME
Comments:
-
Thanks for this! I had to clean up some syntax but I got it working. From here I can output to a CSV and then convert the names to a regex string. Then search assets with names matching the regex, and delete in bulk.
SELECT ASSET.ID,
ASSET.NAME,
ASSET.ASSET_TYPE_ID,
ASSET.MAPPED_ID
FROM ASSET
WHERE (ASSET.ASSET_TYPE_ID = 5) AND (ASSET.MAPPED_ID IS NULL)
ORDER BY ASSET.ID, ASSET.NAME - jlfrank 2 years ago -
OK, I feel silly now but I found an easier approach, and it seems to be more accurate too.
There is a built in report called "Computer Assets Mapped to Computers" that will output all asset mappings to devices. You can use this one, or if you want to exclude archived assets you can create a duplicate and add the archive status to the filters. Export the report as a CSV, and then filter on the Device column for "0 items". Now you can convert this column to regex and search your assets for deletion. - jlfrank 2 years ago
