Custom ticket rule to auto populate assets assigned to the user
I want to show all assets assigned to the ticket submitter as a multiple select field in the ticket. I am using the following:
Select query:
select ID, submitter_ID, MACHINE_ID, CUSTOM_FIELD_VALUE0 from HD_TICKET where HD_QUEUE_ID=19
Update query:
update HD_TICKET
set HD_TICKET.CUSTOM_FIELD_VALUE0 = (select NAME from ASSET where ASSET.OWNER_ID = submitter_ID)
where
(HD_TICKET.ID in (<TICKET_IDS>))
I am getting the error:
mysql error: [1242: Subquery returns more than 1 row] in EXECUTE("update HD_TICKET
set HD_TICKET.CUSTOM_FIELD_VALUE0 = (select NAME from ASSET where ASSET.OWNER_ID = submitter_ID)
where
(HD_TICKET.ID in (24363,24362,24381,24382))")
So how can I handle the CUSTOM_FIELD_VALUE0 to grap all the outputs from my update query?
Many thanks.
Al
Answers (1)
A couple of ideas:
1) Try putting DISTINCT before NAME in your Update's subquery. Assuming it's only pulling multiple matches of the same name, this should work.
2) You could also try using a Select query to populate your multi-select custom field rather than using a ticket rule. Try this in the custom field's Select Values field:
query: select DISTINCT NAME from ASSET join HD_TICKET on (HD_TICKET.SUBMITTER_ID = ASSET.OWNER_ID)
John
Comments:
-
I am afraid both did not work .. - aeliraqi 11 years ago
-
Unfortunately we do not use the Asset module here, so I'm sorry I can't help more at this point - I'm starting to build out some computer assets, but have not gotten to the point where I have things assigned to users (which is where I would be able to help more here - at this point my ASSET.OWNER_ID values are all zero, so that precludes any useful joins on my side). Perhaps this might help:
http://www.itninja.com/question/kace-1000-assets-assigned-to-a-user
John - jverbosk 11 years ago-
No worries, thanks anyway John. - aeliraqi 11 years ago
-
I redid the query - try this in the CUSTOM_1 field, and make sure the Field Type is set to Multiple Select:
query: SELECT DISTINCT A.NAME FROM ASSET A JOIN HD_TICKET T ON (T.SUBMITTER_ID = A.OWNER_ID)
John - jverbosk 11 years ago -
Hi John, have you looked into this any more? That kind of ticket rule would be really handy right now. I just did a helpdesk revamp and brought the Asset field back into existence so the tickets would allign to the equipment or software causing the issue. - GeekSoldier 11 years ago
-
No, I haven't, sorry. And I'm no longer working for the same company so I no longer have access to the KACE appliances I configured. Hopefully, though, I'll have a lab built out in the near future so I can provide more help.
John - jverbosk 11 years ago
