/build/static/layout/Breadcrumb_cap_w.png
07/23/2018 489 views
Hello,

We have a custom field called Location in our IT Service Desk queue, if someone emails a ticket in, that field doesn't get populated until one of us updates it. I wanted to see if it was possible to set the location of that field equal to the user's location field, so then I could also update our new ticket email rule so that the location text is within that email as well.

Here is our current email ticket rule which sends an email to all owners when a new ticket is created. Ideally we would like to only send emails to appropriate owners depending on locations as well.

select
  HD_TICKET.ID,
  HD_TICKET.ID ticket_number,                -- $ticket_number
  HD_TICKET.TITLE ticket_title,              -- $ticket_title
  SUBMITTER.FULL_NAME ticket_submitter_name, -- $ticket_submitter_name
  SUBMITTER.EMAIL ticket_submitter_email,    -- $ticket_submitter_email
  CAT.NAME ticket_category,                  -- $ticket_category
  IMPACT.NAME ticket_impact,                 -- $ticket_impact
  PRIORITY.NAME ticket_priority,             -- $ticket_priority
  STATUS.NAME ticket_status,                 -- $ticket_status
  group_concat(OWNERS.EMAIL) as EMAILCOLUMN
from
  HD_TICKET
  join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID
    and  C.ID =
  left join USER SUBMITTER on HD_TICKET.SUBMITTER_ID = SUBMITTER.ID
  left join HD_CATEGORY CAT on HD_TICKET.HD_CATEGORY_ID = CAT.ID
  left join HD_IMPACT IMPACT on HD_TICKET.HD_IMPACT_ID = IMPACT.ID
  left join HD_PRIORITY PRIORITY on HD_TICKET.HD_PRIORITY_ID = PRIORITY.ID
  left join HD_STATUS STATUS on HD_TICKET.HD_STATUS_ID = STATUS.ID
  join HD_QUEUE_OWNER_LABEL_JT HDQOLJT on HD_TICKET.HD_QUEUE_ID = HDQOLJT.HD_QUEUE_ID
  join USER_LABEL_JT ULJT on HDQOLJT.LABEL_ID = ULJT.LABEL_ID
  join USER OWNERS on ULJT.USER_ID = OWNERS.ID
where
  C.DESCRIPTION like '%Ticket Created%'

Email Code:

A new ticket has been added to the IT Service Desk.
[Ticket Number]:   $ticket_number
[Title]:   $ticket_title
[Submitter Name]:   $ticket_submitter_name
[Submitter Email]:   $ticket_submitter_email
[Impact]:   $ticket_impact
[Priority]:   $ticket_priority
[Status]:   $ticket_status

This is what we are looking for:

A new ticket has been added to the IT Service Desk.
[Ticket Number]:   $ticket_number
[Title]:   $ticket_title
[Submitter Name]:   $ticket_submitter_name
[Submitter Email]:   $ticket_submitter_email
[Impact]:   $ticket_impact
[Priority]:   $ticket_priority
[Status]:   $ticket_status
[Location] $location (from custom field)

I already have this working if I submit a ticket from the KACE portal, but if someone emails a ticket, then that will be blank, so trying to fix that.

Anyone familiar with doing this?

Thanks!
0 Comments   [ + ] Show comments

Comments


All Answers

1
Create a new rule that runs on ticket save to set the user's location. I have my custom field's default value set to "Set on Save" and my rule selects tickets where the custom field is that value.

Use this for the update statement:
update HD_TICKET as T
    set T.CUSTOM_FIELD_VALUEX = (
select ASSET.NAME FROM USER
JOIN ASSET on ASSET.ID = USER.LOCATION_ID
where USER.ID = SUBMITTER_ID ) where 
        T.ID = <TICKET_IDS>;
Change the X to the appropriate field number. Make sure that the order number is less than your email rule so that this rule will run first.

Answered 07/30/2018 by: chucksteel
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share