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
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
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 6 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 6 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 6 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 6 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 6 years ago
https://www.itninja.com/question/sql-code-to-copy-custom-inventory-info-to-asset-object - chucksteel 6 years ago