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)

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

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>
Answered 10/22/2012 by: chucksteel
Red Belt

  • 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!!
    • This is just what I was looking for. Many thanks to you both.
Please log in to comment

Answers

0

Thank you!  This is just the information I needed.  I'll give it a try today.

Answered 10/23/2012 by: elpalmer@ufl.edu
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity