KACE Product Support

Thanks Chucksteel

Absolutely it is, but approach with care.

In effect you need to build your ticket rule to select all HD fields and asset fields that you need for your data copy and then you will be able to use them in your update statement.

Note the Ticket_IDS variable will work for your asset ID if the asset records are the only ones selected, otherwise you must write your update statement to only update the fields you require.

Here's an example rule I have in our purchasing queue. It adds the ticket ID to the selected purchased item asset. Purchased items are selected as the asset for the ticket.

Select query:

select HD_TICKET.*,
ASSET_DATA_11.FIELD_10003 as FIELD_10003
from HD_TICKET
left join ASSET on ASSET.ID = HD_TICKET.ASSET_ID
left join ASSET_DATA_11 on ASSET.ASSET_DATA_ID = ASSET_DATA_11.ID
where HD_TICKET.CUSTOM_FIELD_VALUE7 = 'Set automatically for Items' 
and HD_TICKET.HD_QUEUE_ID = 11

CUSTOM_FIELD_VALUE7 is the purchase price for that item, which is updated with another rule. I have it in this rule to make sure the ticket is only added once to the asset data.

Update query:

update ASSET_DATA_11
set FIELD_10003 = concat(FIELD_10003, '<TICKET_IDS>,')
where ID = 
(select ASSET.ASSET_DATA_ID from ASSET 
left join HD_TICKET on HD_TICKET.ASSET_ID = ASSET.ID
where HD_TICKET.ID = <TICKET_IDS>)

In this case, since I'm adding the ticket ID to the asset, there isn't a lot else involved. If you wanted to set the asset field to a field in the service desk, things would need to be different.

Here's an example of a rule that runs daily and sets the PO Date for computer assets based on the matching ticket in the service desk purchasing queue.

Select statement:

SELECT AD.ID as ID, AD.FIELD_32 as PONumber, AD.FIELD_10013 as POCreated,
HD_TICKET.ID as TICKETID, DATE(HD_TICKET.CREATED)
FROM ASSET
JOIN ASSET_DATA_5 AD on AD.ID = ASSET.ASSET_DATA_ID
JOIN HD_TICKET on HD_TICKET.ID = AD.FIELD_32
WHERE HD_TICKET.HD_QUEUE_ID = 11
and AD.FIELD_10013 is null
or AD.FIELD_10013 = "0000-00-00"

Update statement:

UPDATE ASSET_DATA_5
set FIELD_10013 = (select DATE(CREATED) from HD_TICKET where HD_TICKET.ID = FIELD_32)
WHERE ID in (<TICKET_IDS>)

In this example, the select statement is returning the ID numbers for the ASSET_DATA_5 table so I am updating that table directly based on the value in ASSET_DATA_5.FIELD_32.

I strongly recommend that if you are going to attempt any of this that you setup a dev SMA, which your license allows you to do without additional costs. Be sure to test, test, test. One technique I use to test is to set a limit on the select query to minimize the potential damage.