IS SQL Query for data field possible?
When deleting a device for retirement and leaving Asset for info on when it was retired, I need to be able to track a few things, mainly Serial Numbers. But I do not see Assets having serial number automatically assigned and instead I need to add custom text field and enter it every time I dispose of a device. Is there any way to put a SQL query in for the default value in a custom data field in an asset to pull the serial number from the device record it is attached to? If so does anyone know what it would be I am end user level with sql skills... so none.
Answer Chosen by the Author
There are a couple of options. First, check your Computer Asset Type settings (Asset Management, Asset Types, Computer) for the mapped inventory field and matching asset field. We have ours set to BIOS Serial Number and Name, respectively. When setup like this, the assets are named for the serial number of the matching computer. Along with retaining data better, this also allows our technicians that receive equipment to import the assets before they are even unboxed in most cases.
If changing that setting isn't an option for you, then it is possible to use a service desk rule to populate the custom field. Sharing a rule like this is difficult, because your custom field database names will be different from mine. You will need to setup a tool like MySQL Workbench and connect to the database to get the field names for your appliance.
Some background information:
- All assets have an entry in the ASSET table.
- The ASSET table contains the fields common to all assets (their name, the asset type, when it was created, etc.)
- Each asset type has a corresponding table for data related to that asset type
- The asset type ID for computer assets is 5, so its data table is ASSET_DATA_5
Based on knowing those things, we can create a service desk rule. Service desk rules have write access to all areas of the database, which makes them very powerful for automating all sorts of tasks. For the select statement you want to find computers that don't have the field set, my custom field for ASSET_DATA_5 is FIELD_10014
SELECT ID FROM ASSET_DATA_5 where FIELD_10014 = ""
That gives us the computer assets that we need to update. When the rule runs it will create a variable named TICKET_IDS that we can use in the update statement to target those rows.
The update statement will be a little more complicated because we need to get data from the MACHINE table (where data for computers in the inventory is stored) based on the ASSET_DATA_5.ID. For that we need to go through the ASSET table.
set FIELD_10014 = (select MACHINE.BIOS_SERIAL_NUMBER
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>)
For testing purposes, I recommend getting the ID for one record in ASSET_DATA_5 and changing the select statement to target that asset. So the select statement would look like this:
SELECT ID FROM ASSET_DATA_5 where FIELD_10014 = "" and ID = 1234
where 1234 is the ID of an actual record in ASSET_DATA_5. Run the rule and verify that it updates that row correctly. Once it is successful, remove the "and ID = 1234" from the select statement and schedule the rule to run on a regular basis. The first time it runs it should update all of the assets, afterwards it will only update new assets.