/bundles/itninjaweb/img/Breadcrumb_cap_w.png
In our environment we are unable to use BIOS serial number to map assets since some of our machines have generic text in that field. As a work around we are now using machine name to map assets. I am looking for a way to use a SQL query to auto populate BIOS serial number into a field in Asset object and it should skip the machine if it finds the following text in there. Could you please help?

BIOS Serial Number = System Serial Number
BIOS Serial Number = To be filled by O.E.M.
BIOS Serial Number =
BIOS Serial Number = NullĀ 

ASSET_DATA_5.FIELD_10094

Thanks,
Sam
1 Comment   [ + ] Show comment

Comments

  • How is your MySQL knowledge?
    https://www.itninja.com/question/sql-code-to-copy-custom-inventory-info-to-asset-object
    • Beginner. I tried to improvise the SQL query from that thread but wasn't successful.

All Answers

0
Select statement:
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 on MACHINE.ID = ASSET.MAPPED_ID
WHERE ASSET_DATA_5.FIELD_10094 != MACHINE.BIOS_SERIAL_NUMBER
Rather than find assets where the serial number isn't one of those strings, this finds assets that have a serial number assigned that does not equal the machine's serial number. Does that work for you?

Update query:
UPDATE ASSET_DATA_5
set FIELD_10094 = (select MACHINE.BIOS_SERIAL_NUMBER
FROM ASSET
JOIN MACHINE on MACHINE.ID = ASSET.MAPPED_ID
where ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID and ASSET.ASSET_TYPE_ID = 5)
where ID in (<TICKET_IDS>)
I recommend testing this by limiting the select query to one specific machine. You can do that by adding this line to the end of the statement:
AND MACHINE.BIOS_SERIAL_NUMBER = '12345'

where 12345 is the actual serial number of your test device.


Answered 07/03/2018 by: chucksteel
Red Belt

  • Actually, that wont work because there is a possibility of machines show up with a new name and the BIOS serial number wont copy over due to another asset exist with identical BIOS Serial already. I have a scheduled report that identifies assets with duplicate bios serials in assets. If a machine shows up as duplicate we clean it up manually.
    • The select statement will select the specific asset that is matched to the machine (ASSET.MAPPED_ID). So it should only update the particular asset that matches the machine in question. If you run the select statement as a report, does it only return the machines you would expect? To run it as a report add the following to the first line so it looks like this:
      SELECT ASSET_DATA_5.ID, MACHINE.NAME, MACHINE.BIOS_SERIAL_NUMBER, ASSET.NAME
      • I tried this and the report shows all the machines that has "To be filled by O.E.M." or "System Serial Number" in BIOS Serial field in assets.
      • And you don't want those updated with the system serial number? IF not, then add the following to the end of the select statement:
        AND ASSET_DATA_5.FIELD_10094 != "To be filled by O.E.M"
        and ASSET_DATA_5.FIELD_10094 != "System Serial Number"
  • Thankyou for all the help.

    This works perfectly!

    Select Statement

    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 on MACHINE.ID = ASSET.MAPPED_ID
    WHERE ASSET_DATA_5.FIELD_10094 != MACHINE.BIOS_SERIAL_NUMBER
    AND MACHINE.BIOS_SERIAL_NUMBER != "To be filled by O.E.M."
    AND MACHINE.BIOS_SERIAL_NUMBER != "System Serial Number"


    Update Query

    UPDATE ASSET_DATA_5
    set FIELD_10094 = (select MACHINE.BIOS_SERIAL_NUMBER
    FROM ASSET
    JOIN MACHINE on MACHINE.ID = ASSET.MAPPED_ID
    where ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID and ASSET.ASSET_TYPE_ID = 5)
    where ID in (<TICKET_IDS>)