/build/static/layout/Breadcrumb_cap_w.png

SQL Code to copy BIOS Serial Number into Asset Object

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
  • How is your MySQL knowledge?
    https://www.itninja.com/question/sql-code-to-copy-custom-inventory-info-to-asset-object - chucksteel 5 years ago
    • Beginner. I tried to improvise the SQL query from that thread but wasn't successful. - samuelcolt 5 years ago

Answers (1)

Posted by: chucksteel 5 years ago
Red Belt
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.



Comments:
  • 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. - samuelcolt 5 years ago
    • 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 - chucksteel 5 years ago
      • 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. - samuelcolt 5 years ago
      • 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" - chucksteel 5 years ago
  • 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>) - samuelcolt 5 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ