Version 12.1 or 13 seems to have broken my script
Went from 12.0straight on to 13 (12.1 was done first). I thought I looked at schema changes and saw nothing that would affect me.. I cant seem to find the schema changes now.. if anyone has a link Id appreciate it.
But anyway, when a process ticket is created a script builds the title properly using the below code. Now it is blank. Im at a loss.. it behaves the same in my dev environment but because Im an idiot I didnt catch it in dev before I upgraded.
The below is in the update section and is one of many SET commands. The other commands worth fine which copy the parent fields over to the child in another queue.. but this one which concats the fields is broken.. so its probably nothing to do with the schema but something to do with the concat.. what is infuriating me is it ran ONCE in my dev server.. I cant make it run again.
#BUILD THE TITLE
,HD_TICKET.TITLE = if (SERVICE.ID = 39, concat(PARENT.TITLE," ::: ",U.FULL_NAME," (",PARENT.CUSTOM_FIELD_VALUE7," - ",PARENT.CUSTOM_FIELD_VALUE76," - ",PARENT.CUSTOM_FIELD_VALUE12,") ","::: Effective day is ", PARENT.CUSTOM_FIELD_VALUE13),
concat(PARENT.TITLE," ::: ",PARENT.CUSTOM_FIELD_VALUE15," ",PARENT.CUSTOM_FIELD_VALUE17," (", PARENT.CUSTOM_FIELD_VALUE7," - ",PARENT.CUSTOM_FIELD_VALUE76," - ",PARENT.CUSTOM_FIELD_VALUE12,") ","::: Start day is ", PARENT.CUSTOM_FIELD_VALUE13))
Answers (1)
I figured this out. the new version of kace does not concat correctly if one of the fields is blank. So I have to break this out into 3 if statements which I am struggling with.. its not as easy as it sounds. When I try to use if then else elseif it just doesnt work.
Comments:
-
please report this to support as bug report. - Nico_K 1 year ago
hd_ticket.title = "hello world" and it works fine. the process creates tickets in about 7 queues.. the same script wont run on that process.
I am kinda in a hard place because I know KACE is not gonna touch this because it is a custom script.. Ive burned 6 hrs on this.. if anyone can reformulate the concat differently Id appreciate it.. I even tried pulling the if statement out so it is just the concat. I cant even speculate why it is not working when the same script runs on other processes fine. Basically there are 2 processes that fall under the "does not equal service.id 39".. they are 31, 21. process 21 runs fine and so does the process that the if statement finds true (39.). So the same script when running process 39 or 21 work
Here is the entire script so there is context. All of the set commands work below, it is only the title which does not get updated, it is made blank which is even more odd.
select
distinct HD_TICKET.ID
from
HD_TICKET
join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID and C.ID = <CHANGE_ID>
join HD_SERVICE_TICKET ST on HD_TICKET.SERVICE_TICKET_ID = ST.ID
join HD_SERVICE SERVICE on ST.HD_SERVICE_ID = SERVICE.ID
join HD_TICKET PARENT on HD_TICKET.PARENT_ID = PARENT.ID and PARENT.IS_PARENT
join HD_STATUS S on HD_TICKET.HD_STATUS_ID = S.ID
,(select S.`VALUE` ticket_label from SETTINGS S where S.`NAME` = "HD_TICKET_LABEL") settings
where
HD_TICKET.HD_QUEUE_ID = '5'
and
(S.STATE = "Opened" or S.STATE = "Stalled")
and (SERVICE.ID = 21 or SERVICE.ID = 31 or SERVICE.ID = 39)
and (ST.ORDINAL = 1 OR ST.ORDINAL = 3)
update
HD_TICKET join HD_TICKET PARENT on HD_TICKET.PARENT_ID = PARENT.ID
and PARENT.IS_PARENT LEFT JOIN USER U ON (U.ID = PARENT.CUSTOM_FIELD_VALUE79)
join HD_SERVICE_TICKET ST on HD_TICKET.SERVICE_TICKET_ID = ST.ID
join HD_SERVICE SERVICE on ST.HD_SERVICE_ID = SERVICE.ID
join HD_STATUS S on HD_TICKET.HD_STATUS_ID = S.ID
set
HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE23,
HD_TICKET.CUSTOM_FIELD_VALUE9 = PARENT.CUSTOM_FIELD_VALUE15 ,
HD_TICKET.CUSTOM_FIELD_VALUE10 = PARENT.CUSTOM_FIELD_VALUE16 ,
HD_TICKET.CUSTOM_FIELD_VALUE11 = PARENT.CUSTOM_FIELD_VALUE17 ,
HD_TICKET.CUSTOM_FIELD_VALUE18 = PARENT.CUSTOM_FIELD_VALUE13 ,
HD_TICKET.CUSTOM_FIELD_VALUE20 = PARENT.CUSTOM_FIELD_VALUE6 ,
HD_TICKET.CUSTOM_FIELD_VALUE19 = PARENT.CUSTOM_FIELD_VALUE22 ,
HD_TICKET.CUSTOM_FIELD_VALUE21 = PARENT.CUSTOM_FIELD_VALUE7 ,
HD_TICKET.CUSTOM_FIELD_VALUE22 = PARENT.CUSTOM_FIELD_VALUE12 ,
HD_TICKET.CUSTOM_FIELD_VALUE23 = PARENT.CUSTOM_FIELD_VALUE0 ,
HD_TICKET.CUSTOM_FIELD_VALUE25 = PARENT.CUSTOM_FIELD_VALUE68,
HD_TICKET.CUSTOM_FIELD_VALUE26 = PARENT.CUSTOM_FIELD_VALUE50 ,
HD_TICKET.CUSTOM_FIELD_VALUE24 = PARENT.CUSTOM_FIELD_VALUE48 ,
HD_TICKET.CUSTOM_FIELD_VALUE27 = PARENT.CUSTOM_FIELD_VALUE18,
HD_TICKET.CUSTOM_FIELD_VALUE28 = PARENT.CUSTOM_FIELD_VALUE1 ,
HD_TICKET.CUSTOM_FIELD_VALUE29 = PARENT.CUSTOM_FIELD_VALUE19,
HD_TICKET.CUSTOM_FIELD_VALUE31 = PARENT.CUSTOM_FIELD_VALUE31 ,
HD_TICKET.CUSTOM_FIELD_VALUE30 = PARENT.CUSTOM_FIELD_VALUE33 ,
HD_TICKET.CUSTOM_FIELD_VALUE37 = PARENT.CUSTOM_FIELD_VALUE77 ,
HD_TICKET.CUSTOM_FIELD_VALUE34 = PARENT.CUSTOM_FIELD_VALUE76 ,
HD_TICKET.CUSTOM_FIELD_VALUE38 = PARENT.CUSTOM_FIELD_VALUE79
,HD_TICKET.CUSTOM_FIELD_VALUE46 = PARENT.CUSTOM_FIELD_VALUE71,
HD_TICKET.CUSTOM_FIELD_VALUE45 = PARENT.CUSTOM_FIELD_VALUE72,
HD_TICKET.CUSTOM_FIELD_VALUE47 = PARENT.CUSTOM_FIELD_VALUE73,
HD_TICKET.CUSTOM_FIELD_VALUE48 = PARENT.CUSTOM_FIELD_VALUE84
#BUILD THE TITLE
,HD_TICKET.TITLE = if (SERVICE.ID = 39, concat(PARENT.TITLE," ::: ",U.FULL_NAME," (",PARENT.CUSTOM_FIELD_VALUE7," - ",PARENT.CUSTOM_FIELD_VALUE76," - ",PARENT.CUSTOM_FIELD_VALUE12,") ","::: Effective day is ", PARENT.CUSTOM_FIELD_VALUE13), concat(PARENT.TITLE," ::: ",PARENT.CUSTOM_FIELD_VALUE15," ",PARENT.CUSTOM_FIELD_VALUE17," (", PARENT.CUSTOM_FIELD_VALUE7," - ",PARENT.CUSTOM_FIELD_VALUE76," - ",PARENT.CUSTOM_FIELD_VALUE12,") ","::: Start day is ", PARENT.CUSTOM_FIELD_VALUE13))
where
HD_TICKET.ID = <TICKET_IDS> - barchetta 1 year ago