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?
ASSET_DATA_5.FIELD_10093
MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=28181
Thanks in advance,
Sam
1 Comment
[ + ] Show comment
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
5 years ago
Top Answer
Select query:
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.
Update query:
UPDATE ASSET_DATA_5
set FIELD_10093 = (select
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE
FROM ASSET
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]
Comments:
-
This is what i am getting
06/27/2018 08:31:21> Starting: 06/27/2018 08:31:21 06/27/2018 08:31:21> Executing Select Query... 06/27/2018 08:31:21> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MACHINE_CUSTOM_INVENTORY on MACHINE_CUSTOM_INVENTORY.ID = ASSET.MAPPED_ID and MA' at line 1] in EXECUTE("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 = 28181 where FIELD_10093 is null AND MACHINE_CUSTOM_INVENTORY.ID = 2501") - samuelcolt 5 years ago-
Can you check the line breaks on the rule and make sure they are correct? There should be a line break after ASSET_ASSET_TYPE_ID = 5 but there isn't one showing. - chucksteel 5 years ago
-
Hi Chucksteel.
That fixed it and successfully copied the data into the right Notes field in the asset object. However, the content has some html codes like <br/> so the formatting is all messed up. I might be asking too much here but is there a way to fix that as well?
Thank you in advance.
Sam - samuelcolt 5 years ago
-
The appliance adds the HTML line break tags to make it display properly in the inventory page. One way to get around that is to use a replace command. In the update statement find this line:
select MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE
and replace it with this:
select replace(MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE, "<br/>", '\r\n')
That should replace the HTML break tags with new line breaks. - chucksteel 5 years ago
Here is what i ended up with. Works perfectly.
Select Query:
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 = 5
JOIN MACHINE_CUSTOM_INVENTORY on MACHINE_CUSTOM_INVENTORY.ID = ASSET.MAPPED_ID and MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 28181
Update Query:
UPDATE ASSET_DATA_5
set FIELD_10093 = (select replace(MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE, "<br/>", '\r\n')
FROM ASSET
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>) - samuelcolt 5 years ago