K1000 Bulk update inventory
Community Chosen Answer
Yes, it is possible to change asset information using ticket rules, but it isn't supported, so if you get in trouble, Quest won't help you. Here's an example of something that I have setup. For various reasons, I needed a custom field in the computer asset that contains the serial number. Our assets are named using the serial number, so I created a rule that populates the custom field based on the asset name. Here is the select statement that finds assets without the custom field set properly:
SELECT ASSET.ASSET_DATA_ID as ID, ASSET.NAME, ASSET_DATA_5.FIELD_10014
JOIN ASSET_DATA_5 on ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID
WHERE ASSET.ASSET_TYPE_ID = 5
AND ASSET.NAME != ASSET_DATA_5.FIELD_10014
AND ASSET.NAME != ""
The key to using ticket rules for things other than tickets is that the select statement returns a variable called TICKET_IDS based on the ID column returned in the statement. In this case, it is returning the ASSET_DATA_ID for those assets where FIELD_10014 (my custom field) is not the asset name.
Here is the update statement:
set FIELD_10014 = (select ASSET.NAME as SERIAL
where ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID and ASSET.ASSET_TYPE_ID = 5)
where ID in (<TICKET_IDS>)
You can see how the TICKET_IDS variable is used in the last line.
In your case, you would want to do this the other way, I think. For instance, if you have a field in the asset which contains the serial number, you could select those assets where the NAME doesn't match that field, and then set the NAME using that data. You could then perform the import of the other data (purchase info, etc.) with a normal asset import to update those assets.
For instance, this query will return all of your computer assets that aren't named for the matching computer inventory serial number:
SELECT A.ID, M.NAME as 'Machine', A.NAME as 'Asset', M.BIOS_SERIAL_NUMBER
FROM MACHINE M
JOIN ASSET A on A.MAPPED_ID = M.ID AND A.ASSET_TYPE_ID = 5
WHERE A.NAME != M.BIOS_SERIAL_NUMBER
This statement would update those assets to be named for the matching computer in inventory:
SET ASSET.NAME = (SELECT BIOS_SERIAL_NUMBER FROM MACHINE WHERE ID = ASSET.MAPPED_ID)
WHERE ASSET.ID in (<TICKET_IDS>)
That should (I didn't test it) change all of those asset names.
If you have a SQL statement that will make all of the changes you want, then you can just put that in the select part of a ticket rule and run the rule. I would urge you to make backups and test thoroughly before doing any of these operations.
Also, if you haven't already setup a tool like MySQL Workbench I highly recommend it. You will need it to find the names of any custom fields if you are dealing with them.