SQL Code to copy custom inventory Info to Asset Object
I have a custom inventory rule in K1000 that pulls the BitLocker encryption info. I am looking for a way to copy this into the Asset Objects so if a device gets purged due to MIA i could still recover the details. Could you please help me with the SQL codes to make this happen?
Thanks in advance,
Please log in to answer
Posted by: chucksteel 2 years ago
SELECT ASSET_DATA_5.ID FROM ASSET_DATA_5 JOIN ASSET on ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID and ASSET.ASSET_TYPE_ID = 5JOIN MACHINE_CUSTOM_INVENTORY on MACHINE_CUSTOM_INVENTORY.ID = ASSET.MAPPED_ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 28181where FIELD_10093 is null
This assumes that you only want to update assets that don't already have this field populated. If the data in the CIR will change, then you'll have to modify that a little bit.
set FIELD_10093 = (select
JOIN MACHINE_CUSTOM_INVENTORY on MACHINE_CUSTOM_INVENTORY.ID = ASSET.MAPPED_ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 28181
where ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID and ASSET.ASSET_TYPE_ID = 5)
where ID in (<TICKET_IDS>)
I would recommend that you test this by modifying the select statement to only match one asset by adding something like:
AND MACHINE_CUSTOM_INVENTORY.ID = [id of test machine]