Scripting

Hello,

I have a process for our employee exit...

Basically on the ticket we enter the windows login to a field like "jsmith" then hit apply changes, and then a CTR pulls info from the USER into fields within the ticket (so we know what account the user has that we need to remove)

Then once we are finished with the exit of the user, we close the ticket..

I would like a CTR so when the ticket closes: it takes the user name in the same field "jsmith" for example.. as well as sets the user that applies the archive to variable.. then sets the user to archived..

Select:

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>

  left join HD_TICKET_CHANGE_FIELD cf on c.ID = cf.HD_TICKET_CHANGE_ID

  join HD_STATUS S on HD_TICKET.HD_STATUS_ID = S.ID

  join HD_SERVICE_TICKET st on HD_TICKET.SERVICE_TICKET_ID = st.ID

  join HD_SERVICE serv on st.HD_SERVICE_ID = serv.ID

  join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME

  ,(select S.`VALUE` ticket_label from SETTINGS S where S.`NAME` = "HD_TICKET_LABEL") settings

where

  (

    c.DESCRIPTION like concat("%", settings.ticket_label, " Created%")

    or ifnull(cf.FIELD_CHANGED, "") = "CUSTOM_FIELD_VALUE0"

  )

#  and HD_TICKET.PARENT_ID = 0 and HD_TICKET.IS_PARENT # Parent Ticket

  and HD_TICKET.PARENT_ID > 0 and !HD_TICKET.IS_PARENT # Child Ticket

  and HD_TICKET.TITLE like "Exit-User%" # title starts with "Exit-User"

  and serv.NAME = "Exit Employee Process" # of process "Exit Employee Process"

and S.STATE = "closed" # on ticket close...

  and (

    c.DESCRIPTION like concat("%", settings.ticket_label, " Created%")

    or ifnull(cf.FIELD_CHANGED, "") = "STATUS_NAME"

  )                               # ...on ticket close

group by

  HD_TICKET.ID

Update:

HD_TICKET

  join USER u on HD_TICKET.CUSTOM_FIELD_VALUE0 /* Windows Username */ = u.USER_NAME

(NEED TO SET USER to USR1? as well as USR2 to an account like "(SELECT uID.ID FROM USER uID WHERE uID.USER_NAME = 'admin') AS USR2,"??

SET USR1.MODIFIED = now(), USR1.IS_ARCHIVED = '1', USR1.ARCHIVED_DATE = now(), USR1.ARCHIVED_BY = USR2.ID

IS THIS NEEDED?

WHERE USR1.ID = u.USER_NAME??? AND USR1.IS_ARCHIVED = '0'; ??

where

  HD_TICKET.ID = <TICKET_IDS>

Anyone have any knowledge on how to do this safely?

Much appreciated

Jason