/build/static/layout/Breadcrumb_cap_w.png

Update Asset Field Based on Other Fields with Ticket Rule

This post will attempt to show how to auto-populate and update an asset field based on concatenating other asset fields. The fields/columns will need to change to match the tables and columns from your database. Use the Report Wizard to create a report for the specific Asset Type and select all fields. Then Edit SQL to get the actual field/column names for your environment. 

Note: I use Monitor as an example here but this can be accomplished with any Asset Type.

1. Create Asset Type “Monitor"

2. Add Asset Fields Manufacturer, Type, Size, Description to “Monitor”

2u9MR3.png

3. Create a Monitor Asset with Manufacturer = Dell, Type = LED, Size = 24

4. Go to Service Desk > Configuration > Rules

5. Create a new Rule “Asset Rule – Update Monitor Description”

6. Enter Select SQL:

SELECT ASSET.NAME AS ASSET_NAME

, ASSET_DATA_14.FIELD_10005 AS Manufacturer

, ASSET_DATA_14.FIELD_10006 AS Type

, ASSET_DATA_14.FIELD_10007 AS Size

, CONCAT(LEFT(ASSET_DATA_14.FIELD_10005,1), "-", ASSET_DATA_14.FIELD_10006, "-", ASSET_DATA_14.FIELD_10007) as Description 

FROM ASSET_DATA_14

LEFT JOIN ASSET ON ASSET_DATA_14.ID = ASSET.ASSET_DATA_ID

AND ASSET.ASSET_TYPE_ID=14   

ORDER BY ASSET_NAME

7. Enter Update SQL:

UPDATE ASSET_DATA_14

SET ASSET_DATA_14.FIELD_10008 = CONCAT(LEFT(ASSET_DATA_14.FIELD_10005,1), "-", ASSET_DATA_14.FIELD_10006, "-", ASSET_DATA_14.FIELD_10007)

8. Set Frequency to desired amount (15 minutes in my example) then Save

When changing some of the fields, after 15 minutes (or a manual rule run) the Description will update with the new info.




Comments

This post is locked

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