KACE - set ticket location (custom field) from user location?
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.
Thanks!
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
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.
