Creating () variable array - Correct Syntax
Title should read : Creating ( < TICKET_IDS >
) variable array - Correct Syntax (with no spaced in the parentheses. The original text keeps getting hidden.)
Does the select statement in the custom ticket rules create the array or do I have to still define that information? After searching on here, in the knowledge case on Quest's website, and reviewing the 8.0 admin guide, I cannot find anything that helps me understand how to set this up.
Example: I am trying to set up a statement that will update the asset information using information already in the inventory table. I have the update statement:
(select MACHINE.OS_NAME from MACHINE
JOIN ASSET ON ASSET.NAME = MACHINE.NAME
WHERE (ASSET.ID in ( < TICKET_IDS >
) AND ASSET.ASSET_DATA_ID = ASSET_DATA_5.ID)
There are no spaces in my SQL statement in KACE around the < and > in the WHERE statement but when this gets posted here, whatever is in the parenthesis is hidden.
So I need to have the Select statement correct and I know that incorrect statements can have negative repercussions on the system. Normally, I try to figure it out on my own, but I do not want to impact performance if I can avoid it. I have seen examples if the select statement for rules that are directly related to help desk tickets but since my statements will not have anything to do with the tickets themselves, I am not sure how to structure it correctly. I believe my select statement will target the Asset_Data_5 table since that is where I will be making the updates. Are there any examples that can be provided for me to review?
Please log in to answer
Posted by: chucksteel 3 years ago
The select statement needs to return a column named ID, and the values in that column will be placed in a comma separated list accessed with the TICKET_IDS table. For example, this statement will select records in ASSET_DATA_5 that don't have a value in FIELD_10014:
SELECT ID FROM ASSET_DATA_5 where FIELD_10014 = ""
Then you can update just those rows using something like this:
set FIELD_10014 = (select
when ASSET.NAME = "" then MACHINE.NAME
end as SERIAL
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>)
Regarding the formatting, this site doesn't automatically escape open and closing angle brackets, so your browser is seeing the variable inside of them as a HTML tag.
Posted by: chucksteel 3 years ago
Here is an example showing a relationship between the ticket table and one of the asset tables. This rule is from our purchasing queue. All purchased items are created as an asset first and when an item is purchased it is selected in the asset field of the ticket. This rule appends the ticket ID to a field on that asset type.
Here is the select statement:
ASSET_DATA_11.FIELD_10003 as FIELD_10003
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
There is a join statement that relates the asset table to the ticket, which is followed by another join statement to the ASSET_DATA_11 table. The rule runs on ticket save and only runs if the custom field 8 is the default value 'Set automatically for Items'. Another rule populates that custom field with the price of the purchased item based on the data in the purchased item asset.
Here is the update statement:
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>)
The update statement uses a sub select statement to find ID of the asset which has been purchased, based on the ticket ID. In this case, the TICKET_IDS variable will contain the ID of the ticket which was just saved.