/build/static/layout/Breadcrumb_cap_w.png
06/26/2018 366 views
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
Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • Thank you chucksteel !

    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>)

Answer Chosen by the Author

1
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]



Answered 06/27/2018 by: chucksteel
Red Belt

  • 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")
    • 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.
      • 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
  • 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.