/build/static/layout/Breadcrumb_cap_w.png

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.


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 5 years ago
Red Belt
0

Top Answer

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.

UPDATE ASSET_DATA_5
set FIELD_10014 = (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>)

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.



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