Hello all,

We have been trying to make a rule that populates the department field automatically when a user creates a ticket. Ive seen another similar requests here for reporting purposes but that didnt work out for us. We have the department field being pulled from AD but when we run the following update rule -

 

UPDATE ORG1.HD_TICKET
JOIN ORG1.`USER` S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.SUBMITTER_ID <>0
and S.CUSTOM_1 != ""
SET ORG1.HD_TICKET.CUSTOM_FIELD_VALUE1 = S.CUSTOM_1

 

It doesnt do anything in KACE.

Ticket layout Custom_1 is the Department field and custome field Custom_1 are the different types of departments. When we use MySQL the query runs fine but doesnt work in KACE. Any tips would be helpful, im not familar with SQL at all, its mostly what I found here on the forums that helps me.

 

If you need more info ill do by best to provide that. Thanks ahead of time!

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

I've got Department auto-populate ticket rules in my IT and Time Tracking queues, both of which are documented completely (with tips) in these blogs:

http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned

http://www.itninja.com/blog/view/k1000-service-desk-time-tracking-queue-config-custom-ticket-rules-sql-reports

Also, if you need some pointers in getting started with SQL, try this one:

http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

Here's the ticket rule from my IT queue, just be sure to adjust the targeted custom field and be aware that the SQL number is one lower than the GUI:

Title:
Autopopulate Department Field
************************************
This is one of the rules I referenced in the Ticket Layout section above.
************************************

Order:
10

Notes:
Automatically populates department field (custom field 4 in UI, custom field 3 in SQL) with department name.  This is to help facilitate creation of weekly Sales reports.
Create this ticket rule in Queue 1 (IT Helpdesk).

Frequency:
on Ticket Save

Select Query:
SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.SUBMITTER_ID <>0

X Run an update query, using the results from the one above

Update Query:
UPDATE HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE3 = S.CUSTOM_2 WHERE T.ID=<TICKET_IDS>

Hope that helps!

John

Answered 10/09/2012 by: jverbosk
Red Belt

  • Thanks! Ill give this a shot.
  • This solved my problem. Thank you!
Please log in to comment

Answers

1

Check to see if ticket layout Custom_1 actually is the right field, there have been some mismatches from the DB side. 

Answered 10/08/2012 by: nshah
Red Belt

  • I double checked and it looked good.
Please log in to comment
1

I have a rule that pulls data based on the submitter ID from the user table (just different fields than yours).

In my example below, the custom field is set to the user.location of the submitter on save.

update HD_TICKET
    set HD_TICKET.CUSTOM_FIELD_VALUE4 = (SELECT LOCATION from USER where HD_TICKET.SUBMITTER_ID = USER.ID)
  where
        (HD_TICKET.ID in (<TICKET_IDS>))

NOTE the ticket's submitter location field would not update when testing the ticket field using is null or !="" in the select statement as shown below.

 and (((  HD_TICKET.CUSTOM_FIELD_VALUE4 !="") AND (1  in (select 1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID and USER.USER_NAME is not null)) ) and HD_TICKET.HD_QUEUE_ID = 2 )

I had to change the custom ticket field from single select to a text field and make a default value. Looking for ticket location fields that match the default value "Submitters Department" and the update rule above seemed to do the trick.

 and (((  HD_TICKET.CUSTOM_FIELD_VALUE4 ="Submitters Department") AND (1  in (select 1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID and USER.USER_NAME is not null)) ) and HD_TICKET.HD_QUEUE_ID = 2 )
Answered 10/08/2012 by: pwonson
Senior Yellow Belt

  • Thanks for the input, ill look into this as well
Please log in to comment
Answer this question or Comment on this question for clarity