KACE Product Support

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

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

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 )