K1000 Service Desk - Setup, Tips & Things I Have Learned

Updated 2012-08-02 - new ticket rule for Notification On User Approval (HR Queue)

Disclaimer - I am *not* claiming that this is a "best practices" guide for setting up your service desk.  But I do know that having real-world examples (and accompanying explanations) can help.  As with any implementation, I would strongly encourage documenting your own processes, as well as allocating sufficient time for planning and testing.  Many settings I have in place came about as a result of trying several different approaches during testing (and after initial roll-out) and finding certain ones more efficient than others.  Fortunately, once you are comfortable with the settings, tweaks can be made fairly quickly (with the exception of certain custom ticket rules - which are where this forum and those supporting it become such tremendous resources).  Also, a number of the custom ticket rules (along with supporting comments by others such as dchristian and GillySpy) are in other posts on this forum.  Should you deem additional background info useful, just do a search on "jverbosk".

As I'm sure many of you can relate, one of the reasons I pushed for the K1000 purchase was for the service desk feature.  Over the course of 8 years in my current position, I had documented over 21,000 helpdesk calls in an Excel spreadsheet (mainly to save myself time/grief) but I knew eventually as my company kept growing that this was not a good long-term solution.  At other companies I'd used other ticketing systems (TrackIt and others), but was less than impressed with the customization options and lack of integration with other systems.  This, it turned out is where the K1000's service desk shines.  But I have to admit that it came as a complete shock when I initially went to into the screens and realized how much setup and customization would be required to get things working the way I needed - and the first thing I needed was a plan.

Once I got my head wrapped around what the system could potentially do (after reading a bit and looking at screens for a while), I found that developing categories to be a significant help in getting moving in the right direction.  After that, mapping out processes in Visio and Excel for the more involved stuff helped considerably.  As much as my (former) boss lamented about how much time was necessary to get everything setup, I have to say that I have ended up with a system that runs *exactly* the way I want without unwanted fluff - although I do have a couple of feature requests, like the ability to build checklists in the knowledgebase, with the intention of using these in tickets for more complex items, which I'll get into when I discuss my processes.

Currently at my company, there are 4 IT users (including myself) entering tickets into the Company IT Helpdesk queue, and we field issues from users via phone, email and office visits.  I *do* have things setup so users can send emails to the K1000 to create tickets or do so via the user (web) portal, but, as we're in the midst of the largest upgrade projects in the company's history, I'm not advising users of those options until we have things shored up - no sense having the ticket system devolve into a gripe board, in my opinion.

As for useful information (the whole point of this), I plan on covering all of my queue settings (and the reasons for them), custom ticket rules (and, again, the reasons for using them - I'll try to mark them with a *), my processes (for user hires/terms/changes) and some examples of the process outlines I built out prior to configuring the service desk - basically everything I have done to make my helpdesk ticketing system run the way I want.  I'm not conceited enough to believe it's currently perfect (there's always room for improvement) or to believe that this will be a cookie-cutter solution for other companies.  But hopefully something here will help someone out there either staring at a blank service desk (in disbelief), looking to get a few custom ticket rules to automate things, or ultimately to provide background for my own department should anything happen to me.  I know that reviewing my own config and documentation in depth again was worth the time, as I found a handful of settings that several months of experience of using the system helped to confirm should be changed.

Starting from the top....
________________________________
________________________________

Configuration - Settings
************************************
Based on a Kace video I watched and our own department terminology, I set things up like this.
************************************

Main Tab: Helpdesk

Queue: Department
Queues (plural): Departments

Ticket: Ticket
Tickets (plural): Tickets

Process: Process
Processes (plural): Processes

Default Department: Company IT Helpdesk

************************************
From there I built my first queue...
************************************
________________________________
________________________________

Queue 1 - IT Helpdesk
________________

Helpdesk -> Configuration -> Departments -> Company IT Helpdesk
________________

Name: Company IT Helpdesk
Email Address: helpdesk@kbox.company.net
Alt. Email Address: Helpdesk@Company.Com
************************************
Getting email flow into the K1000 really depends on the mail server that is being used. We are currently using Exchange 2007 with a Barracuda spam filter, and I have separate documentation I wrote on how I got this working with those.  If anyone needs it just let me know.
************************************

Customize Fields and Layout (see after this section)
Allow all users as submitters: yes
Allow all users as approvers: no
Restrict Approvers By Label: it
************************************
The it label is setup via an LDAP label. I have another post that goes into detail on LDAP labels, should you need help with the initial LDAP setup:
http://www.itninja.com/question/ldap-patching-sql-reports-using-all-three-for-efficient-managed-patching-and-other-cool-tric

Home - Label - LDAP Labels

Enabled: yes
Filter Type: User
Associated Label Name: it
Server Hostname: 192.168.1.x (Domain Controller IP)
LDAP Port Number: 389 (the default)
Search Base DN: DC=DomainName,DC=Net
Search Filter:
  (&(&(samaccountname=KBOX_USER_NAME)(objectClass=user))(department=IT))
* Note - this filter looks for "IT" in the User Properties in ADUC:
  User Properties - Organization tab - Department field
If it finds it, the user account in the K1000 gets this label *after* the user logs in to the K1000 (required for LDAP user labels).
 
LDAP Login: ldapaccount@company.net
LDAP Password: ********
Label Attribute: <blank>
Label Prefix: <blank>
************************************

Ticket Owners By Label: it
Accept email from unknown users: no
Allow ticket deletion: no
************************************
I have turned this on in the past to delete test tickets.
************************************

Allow parent ticket to close child tickets: no
Grant read/edit permissions to users with an Admin role (admin portal only): yes

Ticket Defaults: [Customize These Values]
************************************
Goes to Customize Fields and Layout page.
************************************

Category: Application
Status: New
Impact: 1 person can't work
Priority: Medium

Email on Events:
Any Change: Owner, Category CC
************************************
That's it for now, will turn up more once users are invited to use the system.
************************************

Helpdesk Email Customization
************************************
The only change here was to modify the Ticket Change Notification to include the entire ticket history in the email (i.e. all comments) so when a ticket is closed we have a nice summary in the email.

Ticket Change Notification

Subject: [$ticket_number] $ticket_title
 
Body:
Ticket Updated.

For complete details, see:
    $ticket_url

$ticket_history
************************************

System
 Ticket Rules:
************************************
See after the Customize Fields and Layout section.
************************************

Custom Ticket Rules: [Customize]
************************************
See after the System Ticket Rules section.
************************************
________________________________

Customize Fields and Layout
________________________________

Helpdesk Customization
________________

Category Values
************************************
I tried to strike a balance between having categories that covered the most common support areas, while not going too specific (which is far too easy to do).  Even so, this is still an area I tweak, as I find additions that should be made and other categories that aren't used as much as anticipated (and can fall under a similar and more frequently used category.  

It should be obvious that every category will not be applicable to every company, but hopefully these can help you map out your categories before you go through the task of entering them.  

I'm not listing the Default Owner or CC List, as this will depend entirely on who handles what in your company.  However, I will mention that the CC List here corresponds to the Email on Events - Category CC checklist on the main queue config page.
************************************

Name                               User Settable

Application                        true
Application::Adobe                 true
Application::Error                 true
Application::Excel                 true
Application::Finance               true
Application::Install               true
Application::Nuance                true
Application::Office                true
Application::Outlook               true
Application::Reinstall             true
Application::Update                true
Application::Word                  true
Application::Other                 true
Citrix                             true
Citrix::Error                      true
Citrix::Login                      true
Citrix::Other                      true
Computer                           true
Computer::Build
Computer::Error                    true
Computer::Extremely Slow           true
Computer::Freeze                   true
Computer::Hardware Repair          true
Computer::Maintenance              true
Computer::Name Change
Computer::Noise                    true
Computer::Power                    true
Computer::Reconfiguration
Computer::Reload
Computer::Replacement
Computer::Other                    true
Email                              true
Email::Attachment                  true
Email::Distro Group                true
Email::Error                       true
Email::Missing                     true
Email::Spam                        true
Email::Undeliverable               true
Email::Other                       true
ERP                                true
ERP::Data Correction               true
ERP::Menu Access                   true
ERP::Password                      true
ERP::Printing                      true
ERP::Printing::Excel               true
ERP::Printing::Network             true
ERP::Printing::RF                  true
ERP::Printing::Other               true
ERP::Programming
ERP::Programming::A - Severe Bug No Workaround
ERP::Programming::B - Bug With Workaround
ERP::Programming::C - Low-Level Bug (Cosmetic)
ERP::Programming::D - Report Request
ERP::Programming::E - Enhancement
ERP::Screen Access                 true
ERP::Screen Locked                 true
ERP::Sluggish                      true
ERP::Training                      true
ERP::Upgrade
ERP::Other                         true
File                               true
File::Access                       true
File::Error                        true
File::Locked                       true
File::Missing                      true
File::Other                        true
Internet                           true
Internet::Connectivity             true
Internet::Error                    true
Internet::Filter                   true
Internet::Other                    true
Intranet                           true
Intranet::Sales Reports            true
Intranet::Other                    true
KBOX
KBOX::Config
KBOX::Other
Mobile Device                      true
Mobile Device::Email Error         true
Mobile Device::Email Setup         true
Mobile Device::Other               true
Monitor                            true
Monitor::Power                     true
Monitor::Other                     true
Network                            true
Network::ASA
Network::Barracuda
Network::Connectivity              true
Network::Drive                     true
Network::Home Router               true
Network::Home Router::Install      true
Network::Home Router::Wireless     true
Network::Home Router::Other        true
Network::iPrism
Network::Login Script              true
Network::Outage                    true
Network::Router
Network::Sluggish                  true
Network::Other                     true
New
New::Desktop
New::Laptop
New::Monitor
New::Printer
New::Other
Password                           true
Password::Change                   true
Password::Error                    true
Password::Lockout                  true
Password::Other                    true
Peripherals                        true
Peripherals::Keyboard              true
Peripherals::Mouse                 true
Peripherals::Speakers              true
Peripherals::Other                 true
Personal                           true
Personal::Computer                 true
Personal::Network                  true
Personal::Security                 true
Personal::Other                    true
Phone                              true
Phone::Error                       true
Phone::Outage                      true
Phone::Voicemail                   true
Phone::Other                       true
Printer                            true
Printer::Error                     true
Printer::Hardware Repair           true
Printer::Install                   true
Printer::Maintenance               true
Printer::Noise                     true
Printer::Print Defects             true
Printer::Other                     true
Scanner                            true
Scanner::Error                     true
Scanner::Profile Build             true
Scanner::Other                     true
Security                           true
Security::Malware                  true
Security::Password                 true
Security::Theft                    true
Security::Update                   true
Security::Other                    true
Server
Server::Application
Server::Backup
Server::Configuration
Server::DHCP
Server::DNS
Server::Error
Server::Hardware
Server::Install
Server::Maintenance
Server::Update
Server::Other
User                               true
User::Change                       true
User::New                          true
User::Profile Build                true
User::Termed                       true
User::Other                        true
VPN                                true
VPN::Error                         true
VPN::Other                         true
Windows                            true
Windows::Config                    true
Windows::Error                     true
Windows::Update                    true
Windows::Other                     true
Other                              true
________________

Status Values
************************************
I personally use "Open" as "waiting on IT" with everything else referring to waiting on users or support.  I don't really use "New" or "Reopened", but some of the ticket rules do, so I left them in place.
************************************

Name                  State

New                   Opened
New - Reassigned      Opened
Open                  Opened
Waiting On User       Stalled
Waiting On PC         Stalled
Waiting On Approval   Stalled
Waiting - Overdue     Stalled
Closed                Closed
Reopened              Opened
________________

Priority Values
************************************
I consider most tickets to fall under "Medium".  I rank VP/CEO issues and single site outages as "High".  On the same note, I have a custom ticket rule for escalating VP/CEO issues, in case someone happens to set this incorrectly.  Multiple site outages or critical network/server outages are "Critical".  And personal issues are "Low".  In my opinion, no issues should be marked as "None", so I'll probably remove this in the future.
************************************

Name          Color       Escalation Time

Critical      Fuchsia     30 minutes
High          Red         1 hour
Medium        Black       none
Low           Grey        none
None          Silver      none
________________

Impact Values
________________

Name

Many people can't work
Many people inconvenienced
1 person can't work
1 person inconvenienced
________________

Ticket Layout
************************************
The survey isn't currently used, so it is Hidden to disable it.  Location is done via a single select list - I just find this useful.  Alt Phone Number is optional, but present just in case, as users often times are at different extensions or locations when they call.  I added Department and have a custom ticket rule to populate this ticket field on ticket save (pulled from ADUC).  I use this rule to help with a weekly report that I have to generate which lists all tickets associated with the company's Sales department.  Full Name may seem repetitive, but once you realize that the Submitter field gets set to "Unassigned" when a user is removed from the K1000 and you can no longer search on this user's name, its usefulness should become apparent.  This is also populated via a custom ticket rule - the Termed User Lookup rule.
************************************

Name            Label            Required            Permissions

SAT_SURVEY      Please tell...     Not Required        Hidden
SUBMITTER       Submitter          Not Required        User Create
TITLE           Title              Always Required     User Create
IMPACT          Impact             Not Required        User Create
CATEGORY        Category           Required on Close   Owners Only - Visible to Users
STATUS          Status             Not Required        Owners Only - Visible to Users
PRIORITY        Priority           Not Required        Owners Only - Visible to Users
OWNER           Owner              Not Required        Owners Only - Visible to Users
MACHINE         Machine            Not Required        Owners Only - Visible to Users
ASSET           Asset              Not Required        Hidden
CUSTOM_1        Location           Not Required        User Create
CUSTOM_2        Alt Phone Number   Not Required        User Create
CUSTOM_3        Full Name          Not Required        Read Only
CUSTOM_4        Department         Not Required        Read Only
CUSTOM_5        Notes              Not Required        Hidden
CUSTOM_6        Notes              Not Required        Hidden
CUSTOM_7        Notes              Not Required        Hidden
CUSTOM_8        Notes              Not Required        Hidden
CUSTOM_9        Notes              Not Required        Hidden
CUSTOM_10       Notes              Not Required        Hidden
CUSTOM_11       Notes              Not Required        Hidden
CUSTOM_12       Notes              Not Required        Hidden
CUSTOM_13       Notes              Not Required        Hidden
CUSTOM_14       Notes              Not Required        Hidden
CUSTOM_15       Notes              Not Required        Hidden
DUE_DATE        Due Date           Not Required        Hidden
CC_LIST         CC List            Not Required        Owners Only - Hidden From Users
CREATED         Create             Not Required        Owners Only - Visible to Users
MODIFIED        Modified           Not Required        Owners Only - Visible to Users
APPROVAL_INFO   Approver           Not Required        Hidden
PARENT_INFO     Parent Ticket      Not Required        Owners Only - Visible to Users
SEE_ALSO        See Also           Not Required        Owners Only - Hidden From Users
REFERRERS       Referrers          Not Required        Owners Only - Hidden From Users
RESOLUTION      Resolution         Not Required        Owners Only - Visible to Users
________________

Custom Fields
************************************
The single select field makes populating the Location field pretty quick.
************************************

Name       Field Type      Select Values                              Default

CUSTOM_1   Single Select   Chateauguay,East Canton,Greensboro,        Pittsburgh
                           Hammond,Hillsborough,King of Prussia,
                           Marelan,Moulton,New Cumberland,Oak Hill,
                           Pittsburgh,Remote,Santa Fe Springs,
                           Shenango,Tarentum,UK,Warren,
                           Warren Warehouse,Wellston
CUSTOM_2   Text
CUSTOM_3   Text
CUSTOM_4   Text
CUSTOM_5   Text
CUSTOM_6   Text
CUSTOM_7   Text
CUSTOM_8   Text
CUSTOM_9   Text
CUSTOM_10  Text
CUSTOM_11  Text
CUSTOM_12  Text
CUSTOM_13  Text
CUSTOM_14  Text
CUSTOM_15  Text
________________

Ticket List Layout
________________

Name        Width

TICK:       10
Modified    20
Title       40
Priority    9
Status      9
Submitter   12
Owner       12
Machine     20
________________________________

System Ticket Rules
************************************
All of the system ticket rules are enabled, with a couple of tweaks.
************************************

WaitingOverdue Rule
************************************
I have this set to 30 days, as the default seemed too short.  As my remote users are often on the road for days at a time, it can sometimes be a week or two before they have a chance to call in during business hours (which is when we operate).
************************************

OverdueClose Rule
************************************
Same idea as above, set to 6 months.
************************************
________________________________

Custom Ticket Rules
************************************
These are rules I wrote (sometimes with the built-in wizard, often with assistance) to make everything work the way I needed it.
************************************

Title:
VIP Rule
************************************
In the actual rules using this template I have defined actual users' names, but this should sufficient if you wanted to use it. This one was definitely built with the wizard based on all of the extra SQL code.
************************************

Order:
1

Notes:
Escalate VIP's tickets to High priority status.
Create this ticket rule in Queue 1 (IT Helpdesk).

Frequency:
on Ticket Save

Select Query:
select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
                        HD_STATUS.NAME AS STATUS_NAME,
                        HD_STATUS.ORDINAL as STATUS_ORDINAL,
                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
                        STATE,
                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
                        case upper(STATE)
                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
                        U1.FULL_NAME as OWNER_FULLNAME,
                        U1.EMAIL as OWNER_EMAIL,
                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
                        U2.FULL_NAME as SUBMITTER_FULLNAME,
                        U2.EMAIL as SUBMITTER_EMAIL,
                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
                        Q.NAME as QUEUE_NAME                        
                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
                        where HD_PRIORITY.ID = HD_PRIORITY_ID
                        and HD_STATUS.ID = HD_STATUS_ID
                        and HD_IMPACT.ID = HD_IMPACT_ID
                        and HD_CATEGORY.ID = HD_CATEGORY_ID
                        and ((  (1  in (select 1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID and USER.USER_NAME like '%vipname%')) ) and HD_TICKET.HD_QUEUE_ID = 1 )

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

Update Query:
update HD_TICKET as T, HD_PRIORITY as T5
    set T.HD_PRIORITY_ID = T5.ID
  where T5.NAME = 'High' and
T.HD_QUEUE_ID = T5.HD_QUEUE_ID and
        (T.ID in (<TICKET_IDS>))
________________

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>
________________

Title:
Autopopulate Termed User Lookup Field
************************************
This is the other rule I referenced in the Ticket Layout section above.
************************************

Order:
10

Notes:
Automatically populates termed user lookup field (custom field 3 in UI, custom field 2 in SQL) with submitter's full name.  This is to ensure searches done for termed users' tickets by name can be done after they have been removed from the system (removing users clears the Submitted By field and sets it to Unassigned).
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_VALUE2 = S.FULL_NAME WHERE T.ID=<TICKET_IDS>
________________

Title:
Unassigned Tickets
************************************
I believe I borrowed this from an AppDeploy post, modified for my own use.
************************************

Order:
11

Notes:
Sends an email when a ticket has sat in queue longer than specified period (1 hour).
Create this ticket rule in Queue 1 (IT Helpdesk).

Frequency:
15 minutes

Select Query:
Select 'itdept@company.com' as SUPPORT, T.ID as ID, T.TITLE as Issue, S.FULL_NAME as Submitter, T.CREATED as Created FROM HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR)
AND T.OWNER_ID = 0
ORDER BY T.ID ASC

X Send an email for each result row

Subject:
ATTENTION - Unassigned Ticket in KBOX Helpdesk Queue

Email Column:
SUPPORT

Email Body:
Unassiged ticket in KBOX Helpdesk queue, please review.

Created:    $created
Ticket ID:    $id
Issue:        $issue
Submitter:    $submitter
________________

Title:
Autopopulate Child Ticket Fields (User Name & Date)
************************************
This rule was created with the combined assistance of dchristian and GillySpy.
************************************

Order:
12

Notes:
Copies specified data from Parent ticket (in queue 2) to Child ticket (in queue 1) on ticket save of Child ticket, NOT RLIKE included in WHERE statement so subsequent CONCATs do not occur.
Create rule in Queue 1 (IT Helpdesk)

AppDeploy (ITNinja) forum note:
PARENT.CUSTOM_FIELD_VALUE2 is where the HR person types in the new user's full name in the Parent ticket. This rule tacks this on the end of the Child ticket's title field on ticket save.  Child tickets setup with titles for network add, computer setup & ERP system setup.

Frequency:
on Ticket Save

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE2 AS 'Parent New User Name', PARENT.CUSTOM_FIELD_VALUE6 AS 'Date of Hire', HD_TICKET.TITLE AS 'Child Title Field', HD_TICKET.ID AS 'ID'
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

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

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE, ' - ', PARENT.CUSTOM_FIELD_VALUE2, ' (', PARENT.CUSTOM_FIELD_VALUE6, ')')
WHERE (((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.TITLE NOT RLIKE '^.+ [[.-.]].*') AND HD_TICKET.ID = <TICKET_IDS>)
________________

Title:
Autopopulate Child Ticket Fields (Location)
************************************
Same credits as other parent->child ticket rule above.
************************************

Order:
12

Notes:
Copies specified data from Parent ticket (in queue 2) to Child ticket (in queue 1) on ticket save of Child ticket.
Create this ticket rule in Queue 1 (IT Helpdesk)

Frequency:
on Ticket Save

Select Query:
SELECT PARENT.CUSTOM_FIELD_VALUE0 AS 'Parent Location', HD_TICKET.CUSTOM_FIELD_VALUE0 AS 'Child Location', HD_TICKET.ID AS 'ID'
FROM HD_TICKET PARENT, HD_TICKET
WHERE PARENT.ID = HD_TICKET.PARENT_ID

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

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET
SET HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0
WHERE ((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)
________________

Title:
Reassign Reopened Tickets
************************************
I learned that removing a termed ticket owner reopens all of his tickets.  This is my rule for dealing with those tickets (Status - Reopened, Owner - Unassigned).  Created using the ticket wizard.
************************************

Order:
100

Notes:
Changes any tickets with a status of Reopened tickets so that existing admin (Lastname, Firstname in Update Query) is made the owner and the status is changed to Closed.  This is used to cleanup tickets when a ticket owner is removed from the K1000.
Create this ticket rule in Queue 1 (IT Helpdesk)

Frequency:
Manually (not enabled)

Select Query:
select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
                        HD_STATUS.NAME AS STATUS_NAME,
                        HD_STATUS.ORDINAL as STATUS_ORDINAL,
                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
                        STATE,
                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
                        case upper(STATE)
                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
                        U1.FULL_NAME as OWNER_FULLNAME,
                        U1.EMAIL as OWNER_EMAIL,
                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
                        U2.FULL_NAME as SUBMITTER_FULLNAME,
                        U2.EMAIL as SUBMITTER_EMAIL,
                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
                        Q.NAME as QUEUE_NAME                        
                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
                        where HD_PRIORITY.ID = HD_PRIORITY_ID
                        and HD_STATUS.ID = HD_STATUS_ID
                        and HD_IMPACT.ID = HD_IMPACT_ID
                        and HD_CATEGORY.ID = HD_CATEGORY_ID
                        and ((  HD_STATUS.NAME = 'Reopened') and HD_TICKET.HD_QUEUE_ID = 1 )

Update Query:
update HD_TICKET, USER as T5, HD_STATUS as T6
    set HD_TICKET.OWNER_ID = T5.ID,
HD_TICKET.HD_STATUS_ID = T6.ID,
HD_TICKET.TIME_OPENED  = IF(T6.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED  = IF(T6.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T6.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.SATISFACTION_RATING = IF(T6.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
HD_TICKET.SATISFACTION_COMMENT = IF(T6.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)
  where T5.FULL_NAME = 'Lastname, Firstname' and
T6.NAME = 'Closed' and
HD_TICKET.HD_QUEUE_ID = T6.HD_QUEUE_ID and
        (HD_TICKET.ID in (<TICKET_IDS>))
________________

There are a few more custom ticket rules I used to import my existing 21,000+ tickets (logged in an Excel spreadsheet) after cleaning them up and converting to a CSV file.  Directions can be found here:

http://www.kace.com/support/kb/index.php?action=artikel&cat=8&id=1049&artlang=en

My one contribution here is that if you try to import more than 2,998 tickets at a time, some will drop out during the import.  Actually, when I tried importing the entire 21,000+, then 10,000, then 5,000, the browsers would just hang. I tried IE8, IE9 & Firefox, same for all.  Note, this is the limit I determined when I was running a version of 5.2 so it may be different now.  But my advice is to just be aware of the possibility if importing a large number of tickets.
________________________________
________________________________

Queue 2 - HR Helpdesk
________________

So there's Queue 1, the IT Helpdesk queue.  This is where the majority of the tickets are entered.  However, the requirement for a second queue was determined shortly after my current boss decided that our current method of tracking user hires/changes/terms in spreadsheets was not ideal, as these could always be altered after the fact, and it was messy to tie them to emails documenting approval for remote access (which our CEO made a requirement).  Thus was born Queue 2, the HR Helpdesk queue.

Fortunately, by this time there was a smooth process for duplicating queues and I was pretty happy that I didn't have to redo everything from scratch, as was done for the IT Helpdesk queue.  This process is as follows:

Exporting & reimporting a queue
http://www.appdeploy.com/messageboards/tm.asp?m=81173&mpage=1&key=queues&#81176

Can duplicate a queue via Resources
1) Export the existing queue via Settings – Resources – Export K1000 Resources
2) Access the samba share via \\kbox\clientdrop and rename from queue1 to queue2
3) Import the renamed queue via Settings – Resources – Import K1000 Resources
4) Confirm the duplicate queue in Helpdesk – Configuration – Queues
5) Rename the duplicate queue and adjust settings as necessary

Using the copied first queue as a foundation, I built the second queue.  You'll see pretty quickly that this queue is far sparser than the IT queue, since its scope is much more limited.  However, it integrates very tightly with the IT queue and many of the custom ticket rules (parent->child rules in Queue 1, auto-populate title field in Queue 2) let data flow between the parent tickets (HR queue) and child tickets (IT queue).  The actual application of these rules should become clearer as I outline my Processes for user hires/changes/terms later.
________________

Helpdesk -> Configuration -> Departments -> Company HR Helpdesk
________________

Name: Company HR Helpdesk
Email Address: hr@kbox.company.net
Alt. Email Address: HR@Company.Com
************************************
Again - getting email flow into the K1000 really depends on the mail server that is being used.  We are currently using Exchange 2007 with a Barracuda spam filter, and I have separate documentation I wrote on how I got this working with those.  If anyone needs it just let me know.
************************************

Customize Fields and Layout (see after this section)

Allow all users as submitters: yes
Allow all users as approvers: no
Restrict Approvers By Label: approvers
************************************
The approvers label is setup via a standard label (i.e. manually assigned to users).
************************************

Ticket Owners By Label: it
Accept email from unknown users: no
Allow ticket deletion: no
************************************
I have turned this on in the past to delete test tickets.
************************************

Allow parent ticket to close child tickets: no
Grant read/edit permissions to users with an Admin role (admin portal only): yes

Ticket Defaults: [Customize These Values]
************************************
Goes to Customize Fields and Layout page.
************************************

Category: User::New
Status: Waiting On Approval
Impact: 1 person can't work
Priority: Medium

Email on Events:
Any Change: Owner
Approval Change: Owner
New Ticket Via Email: Submitter
************************************
That's it for now, may change in the future.
************************************

Helpdesk Email Customization
************************************
The only change here was to modify the Ticket Change Notification to include the entire ticket history in the email (i.e. all comments) so when a ticket is closed we have a nice summary in the email.

Ticket Change Notification

Subject: [$ticket_number] $ticket_title
 
Body:
Ticket Updated.

For complete details, see:
    $ticket_url

$ticket_history
************************************

System
 Ticket Rules:
************************************
See after the Customize Fields and Layout section.
************************************

Custom Ticket Rules: [Customize]
************************************
See after the System Ticket Rules section.
************************************
________________________________

Customize Fields and Layout
________________________________

Helpdesk Customization
________________

Category Values
************************************
As mentioned above, much more limited as the usage is extremely focused.
************************************

Name                               User Settable

User::Change                       true
User::New                          true
User::Termed                       true
User::Other                        true
Other                              true
________________

Status Values
************************************
You'll notice the addition of Approved and Rejected values.  These are in place to help the IT Director (who is the owner of these tickets) determine if approval (or rejection) has taken place and can thus close the HR queue ticket.   On that note, I ran into a potential bug where some tickets would get the correct Approved or Rejected status and some would appear as Reopened, so I created a custom ticket rule (documented below) to address this.
************************************

Name                  State

New                   Opened
New - Reassigned      Opened
Open                  Opened
Waiting On Approval   Stalled
Waiting On User       Stalled
Waiting - Overdue     Stalled
Closed                Closed
Reopened              Opened
Approved              Opened
Rejected              Opened
________________

Priority Values
________________

Name          Color       Escalation Time

High          Red         1 hour
Medium        Black       none
Low           Grey        none
________________

Impact Values
________________

Name

Many people can't work
Many people inconvenienced
1 person can't work
1 person inconvenienced
________________

Ticket Layout
************************************
The survey isn't currently used, so it is Hidden to disable it.  The custom fields were set to match our current user change forms; many are referenced in the custom ticket rules below.  These fields also match up with some of the custom fields in the IT queue (in order for the custom ticket rules to work), but needed to be in a specific order in the HR queue, which is why the ordering may appear strange at first glance.  Also note that the required status has changed for a number of custom fields, as some of these are required by the custom ticket rules (and others are required to know how the related IT tickets should be handled (as outlined further in Processes below).
************************************

Name            Label                         Required            Permissions

SAT_SURVEY      Please tell...                Not Required        Hidden
SUBMITTER       Submitter                     Not Required        User Create
TITLE           Title                         Always Required     User Create
IMPACT          Impact                        Not Required        Hidden
CATEGORY        Category                      Always Required     User Modify
STATUS          Status                        Not Required        User Modify
PRIORITY        Priority                      Not Required        User Modify
OWNER           Owner                         Not Required        User Modify
MACHINE         Machine                       Not Required        Hidden
ASSET           Asset                         Not Required        Hidden
CUSTOM_3        Full Name                     Always Required     User Create
CUSTOM_5        Job Title                     Always Required     User Create
CUSTOM_1        Location                      Always Required     User Create
CUSTOM_4        Department                    Always Required     User Create
CUSTOM_6        Manager Name                  Always Required     User Create
CUSTOM_2        Alt Phone Number              Not Required        Hidden
CUSTOM_7        Date of Hire/Term             Not Required        User Create
CUSTOM_8        Type of User                  Always Required     User Create
CUSTOM_9        Equipment                     Not Required        User Create
CUSTOM_10       ERP Access                    Not Required        User Create
CUSTOM_11       User To Copy (ERP)            Not Required        User Create
CUSTOM_12       Network Access                Not Required        User Create
CUSTOM_13       User To Copy (Network)        Not Required        User Create
CUSTOM_14       Remote Access (VPN, Citrix)   Not Required        Hidden
CUSTOM_15       none                          Not Required        Hidden
APPROVAL_INFO   Approver                      Not Required        User Modify
DUE_DATE        Due Date                      Not Required        Hidden
CC_LIST         CC List                       Not Required        User Create
CREATED         Create                        Not Required        Owners Only - Visible to Users
MODIFIED        Modified                      Not Required        Owners Only - Visible to Users
PARENT_INFO     Parent Ticket                 Not Required        Owners Only - Visible to Users
SEE_ALSO        See Also                      Not Required        Owners Only - Hidden From Users
REFERRERS       Referrers                     Not Required        Owners Only - Hidden From Users
RESOLUTION      Resolution                    Not Required        Owners Only - Visible to Users
________________

Custom Fields
************************************
The single select field makes populating the Location field pretty quick.
************************************

Name       Field Type      Select Values                              Default

CUSTOM_1   Single Select     Chateauguay,East Canton,Greensboro,        Pittsburgh
                             Hammond,Hillsborough,King of Prussia,
                             Marelan,Moulton,New Cumberland,Oak Hill,
                             Pittsburgh,Remote,Santa Fe Springs,
                             Shenango,Tarentum,UK,Warren,
                             Warren Warehouse,Wellston
CUSTOM_2   Text
CUSTOM_3   Text
CUSTOM_4   Text
CUSTOM_5   Text
CUSTOM_6   Text
CUSTOM_7   Text
CUSTOM_8   Single Select     Company Employee,Temporary Employee,       Company Employee
                             Consultant
CUSTOM_9   Multiple Select   Desktop Computer,Laptop Computer,Email,    Email
                             Voicemail,No Change
CUSTOM_10  Single Select     None,Same As User,Groups Required,         None
                             No Change
CUSTOM_11  Text
CUSTOM_12  Single Select     None,Same As User,Groups Required,         Same As User
                             No Change
CUSTOM_13  Text
CUSTOM_14  Text
CUSTOM_15  Text
________________

Ticket List Layout
________________

Name        Width

TICK:       10
Modified    20
Title       25
Priority    9
Status      9
Submitter   12
Owner       12
Machine     18
________________________________

System Ticket Rules
************************************
All of the system ticket rules are enabled, with a couple of tweaks.
************************************
________________

WaitingOverdue Rule
************************************
I left this set at 30 days, as in the IT queue.
************************************

OverdueClose Rule
************************************
Same idea as above, set to 6 months.
************************************
________________________________

Custom Ticket Rules
************************************
These are rules I wrote (sometimes with the built-in wizard, often with assistance) to make everything work the way I needed it.
************************************

Title:
Autopopulate Title Field (HR Queue)
************************************
I found having the user's name and change date present in the title field to be extremely useful (and time-saving, as that info is already entered in the ticket's custom fields), particularly when the child tickets are created.  Rather than having to go into the parent ticket for this info, it's already in the child tickets' title.  The CONCAT clause checks to make sure this ticket rule hasn't already run, so it doesn't keep appending ad infinitum.
************************************

Order:
10

Notes:
Automatically populates title field with Full Name (custom field 3 in UI, custom field 2 in SQL) and Date of Hire (custom field 7 in UI, custom field 6 in SQL).  Example of result is:  New User - John Smith (2012-01-26)

Frequency:
on Ticket Save


Select Query:
SELECT HD_TICKET.TITLE AS 'Title', HD_TICKET.CUSTOM_FIELD_VALUE2 AS 'Full Name', HD_TICKET.CUSTOM_FIELD_VALUE6 AS 'Date of Hire', HD_TICKET.ID AS 'ID' FROM HD_TICKET  
WHERE (HD_TICKET.HD_QUEUE_ID = 2)  

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

Update Query:
UPDATE HD_TICKET T
SET T.TITLE = CONCAT(T.TITLE, ' - ', T.CUSTOM_FIELD_VALUE2, ' (', T.CUSTOM_FIELD_VALUE6, ')')
WHERE ((T.TITLE NOT RLIKE '^.+ [[.-.]].*') AND T.ID = <TICKET_IDS>)

________________

Title:
Waiting On Approval
************************************
I actually have two of these ticket rules in place, depending on whether a user needs remote access or not.  As I mentioned earlier, our CEO has mandated that anyone requiring remote access first have his explicit approval before said access is granted.  So in the actual tickets, there are two things that need set (1) Remote Access - Yes or No, and (2) Approver - CEO or IT Director.  When set correctly (i.e. Remote Access - yes, Approver - CEO ~ or vice versa), this ticket rule fires off on ticket save and an email is sent to the appropriate person, who just needs to click on the link in the email to update the approval field from None to Approved or Rejected.  Inbound KBOX email routing needs to be working for this to go through.  If you want the opposite rule (i.e. Remote Access - No), just change the valued of HD_TICKET.CUSTOM_FIELD_VALUE13 in the last line of the select query from "Yes" to "No".
************************************

Order:
12

Notes:
Sends an email to CEO when a ticket's approval has not been specified and the Remote Access (VPN, Citrix) is Yes.

Frequency:
on Ticket Save

X Send an email for each result row

Subject:
[TICK:$id] NEW TICKET: $status_name

Email Column:
APPROVER

Email Body:
A ticket in the HR Helpdesk queue needs your approval, please review.

Ticket ID:                    $id
Created:                      $created
Issue:                        $issue
Category:                     $category
Status:                       $status_name
Remote Access (VPN/Citrix):   $remote

User:                         $fullname
Date of Hire:                 $dateofhire
Job Title:                    $jobtitle
Department:                   $department
Location:                     $location
Manager:                      $manager
___________________________________________________________________

To APPROVE opening this ticket, please click here:
<mailto:hr@company.com?subject=[TICK:$id]&body=@approval%20=%20Approved>

To REJECT opening this ticket, please click here:
<mailto:hr@company.com?subject=[TICK:$id]&body=@approval%20=%20Rejected>
___________________________________________________________________

Thanks,

Company IT

Select Query:

select 'ceo@company.com' as APPROVER, HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE6 as DATEOFHIRE, HD_TICKET.CUSTOM_FIELD_VALUE5 as MANAGER, HD_TICKET.CUSTOM_FIELD_VALUE3 as DEPARTMENT, HD_TICKET.CUSTOM_FIELD_VALUE4 as JOBTITLE, HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE13 as REMOTE, HD_TICKET.TITLE as Issue, HD_CATEGORY.NAME as CATEGORY, HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
                        HD_STATUS.NAME AS STATUS_NAME,
                        HD_STATUS.ORDINAL as STATUS_ORDINAL,
                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
                        STATE,
                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
                        case upper(STATE)
                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
                        U1.FULL_NAME as OWNER_FULLNAME,
                        U1.EMAIL as OWNER_EMAIL,
                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
                        U2.FULL_NAME as SUBMITTER_FULLNAME,
                        U2.EMAIL as SUBMITTER_EMAIL,
                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
                        Q.NAME as QUEUE_NAME                        
                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
                        where HD_PRIORITY.ID = HD_PRIORITY_ID
                        and HD_STATUS.ID = HD_STATUS_ID
                        and HD_IMPACT.ID = HD_IMPACT_ID
                        and HD_CATEGORY.ID = HD_CATEGORY_ID
                        and (((  HD_TICKET.APPROVAL = '') AND HD_TICKET.CUSTOM_FIELD_VALUE13 = 'Yes') and HD_TICKET.HD_QUEUE_ID = 2 )
________________

Title:
Approval Status Field Update (Approved)
************************************
As mentioned earlier, I ran across an issue with the ticket status field not always updating correctly when the approval setting was updated.  As a solution/workaround, I created three rules to run hourly so the status field is updated correctly.  To create a rule for Rejected status, just change "Approved" (towards the end of both queries) to "Rejected".  To create a rule for "Waiting on Approval", just change the last line of the Select Query to this:

and (((  HD_TICKET.APPROVAL not rlike 'Approved|Rejected') AND HD_STATUS.NAME != 'Closed') and HD_TICKET.HD_QUEUE_ID = 2 )

and change the "Approved" entry at the end of the Update Query to "Waiting on Approval".  Note that you have to have status values of Approved, Rejected and Waiting on Approval in order for these rules to work.
************************************

Order:
14

Notes:
Updates Status field to "Approved" if approval status is "approved".

Frequency:
Hourly

Select Query:
select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
                        HD_STATUS.NAME AS STATUS_NAME,
                        HD_STATUS.ORDINAL as STATUS_ORDINAL,
                        HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
                        HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
                        HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
                        STATE,
                        if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
                        if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
                        if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
                        if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
                        if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
                        if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
                        if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
                        if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
                        case upper(STATE)
                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
                        if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
                        U1.FULL_NAME as OWNER_FULLNAME,
                        U1.EMAIL as OWNER_EMAIL,
                        if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
                        if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
                        U2.FULL_NAME as SUBMITTER_FULLNAME,
                        U2.EMAIL as SUBMITTER_EMAIL,
                        if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
                        if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
                        if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
                        Q.NAME as QUEUE_NAME                        
                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
                        LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
                        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
                        LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
                        where HD_PRIORITY.ID = HD_PRIORITY_ID
                        and HD_STATUS.ID = HD_STATUS_ID
                        and HD_IMPACT.ID = HD_IMPACT_ID
                        and HD_CATEGORY.ID = HD_CATEGORY_ID
                        and (((  HD_TICKET.APPROVAL like '%Approved%') AND HD_STATUS.NAME != 'Closed') and HD_TICKET.HD_QUEUE_ID = 2 )

Update Query:
update HD_TICKET, HD_STATUS as T5
    set HD_TICKET.HD_STATUS_ID = T5.ID,
HD_TICKET.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)
  where T5.NAME = 'Approved' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
        (HD_TICKET.ID in (<TICKET_IDS>))
________________________________

Title:
Notification On User Approval (HR Queue)
************************************
I created this rule later to save the child ticket owners (in the IT Queue) from having to check the parent ticket's approval status in the HR queue.  The trickiest part was determining the best way to get the rule to fire off when the ticket was approved, but not again if the parent ticket was saved again - this is what the STATE not rlike 'closed|reopened' statement at the end accomplishes.

************************************

Order:
20

Notes:
Sends an email to child ticket owners when an HR ticket's approval has been specified.

Frequency:
on Ticket Save
__________________________________________

X Send an email for each result row

Subject:
[TICK:$id] USER CHANGE APPROVAL: $approval


Email Column:
OWNER_EMAIL

Email Body:

A ticket's approval status in the HR Helpdesk queue has been updated, please review.

Ticket ID:                $id
Created:                $created
Issue:                    $issue
Remote Access (VPN/Citrix):        $remote
Approval:                $approval

User:                    $fullname
Date of Hire:                $dateofhire
Job Title:                $jobtitle
Department:                $department
Location:                $location
Manager:                $manager
___________________________________________________________________

Thanks,

Company IT
_____________________________________

Select Query:

select USER.EMAIL as OWNER_EMAIL, HD_TICKET.ID AS ID, HD_TICKET.TITLE as ISSUE, HD_TICKET.APPROVAL as APPROVAL,
HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE3 as DEPARTMENT,
HD_TICKET.CUSTOM_FIELD_VALUE4 as JOBTITLE, HD_TICKET.CUSTOM_FIELD_VALUE5 as MANAGER, HD_TICKET.CUSTOM_FIELD_VALUE6 as DATEOFHIRE,
HD_TICKET.CUSTOM_FIELD_VALUE13 as REMOTE, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
if ((LENGTH(USER.FULL_NAME) = 0), USER.USER_NAME, USER.FULL_NAME) as OWNER_NAME, USER.EMAIL as OWNER_EMAIL, STATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE
from (HD_TICKET, HD_STATUS)
JOIN HD_TICKET CHILD on CHILD.PARENT_ID=HD_TICKET.ID
LEFT JOIN USER on USER.ID = CHILD.OWNER_ID
where HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
and (STATE not rlike 'closed|reopened' and HD_TICKET.APPROVAL != '' and CHILD.HD_QUEUE_ID = 1 and HD_TICKET.HD_QUEUE_ID = 2)
________________________________

And that wraps up Queue 2, the HR Helpdesk queue.  Again, this aim of this queue is to facilitate audit-ready, unalterable user change "forms" (per the IT Director's directive).  Now onto Processes, which I'm currently only using for user hires/changes/terms.  This is the entire reason for having the second queue, as the parent ticket is created in the HR queue and then generates the applicable child IT tickets (user, computer, ERP system) in the IT queue once the parent ticket is saved.  Hopefully this will help tie everything together.  If not, I might suggest making a flow chart based on these processes (i.e. this event fires off the next event which is then updated by these ticket rules, etc).
________________________________
________________________________

Processes
************************************
I'll just cover the User - New process for brevity... The User - Term and User - Change processes are basically the same with the exception of the title and the category.
************************************

Helpdesk -> Configuration -> Processes -> User - New
________________

Process Configuration

Name: User - New
Description: Process for new users.
Display to all users:
Restricted Users By Label: hr, it
Enabled: X

Parent Ticket: New User Setup

Child Activities:

Stage   Title                  Status      Submitter      Owner
1       New User Setup         New         Unassigned     Helpdesk Coordinator
1       New User (Computer)    New         Unassigned     Helpdesk Coordinator
1       New User Setup (ERP)   New         Unassigned     IT Director
________________

Parent Ticket
*New User Setup - Company HR Helpdesk*

Process: User - New
Child Tickets: <listed>
Submitter: Unassigned
Title: New User Setup
Category: User::New
Status: Waiting On Approval
Priority: Medium
Owner: IT Director
Full Name:
Job Title:
Location: Pittsburgh
Department:
Manager Name:
Date of Hire/Term:
Type of User: Company Employee
Equipment: Desktop Computer
           Laptop Computer
           Email *highlighted*
           Voicemail
           No Change
ERP Access: None
User to Copy (ERP):
Network Access: Same As User
User to Copy (Network):
Remote Access (VPN, Citrix): No
Approver: IT Director
          Approval required before Close
CC List: None
Resolution: None
Owners only: <unchecked>
Comment: <blank>
________________

Child Ticket
*New User Setup - Company IT Helpdesk*

Process: User - New
Parent Ticket: New User Setup
Child Tickets: <listed>
State: 1
Submitter: Unassigned
Title: New User Setup
Impact: 1 person can't work
Category: User::New
Status: New
Priority: Medium
Owner: Helpdesk Coordinator
Machine: Unassigned
Location: Pittsburgh
Alt Phone Number: <blank>
Full Name:
Department:
CC List: None
Resolution: None
Owners only: <unchecked>
Comment: <blank>
________________

Child Ticket
*New User Setup - Company IT Helpdesk*

Process: User - New
Parent Ticket: New User Setup
Child Tickets: <listed>
State: 1
Submitter: Unassigned
Title: New User (Computer)
Impact: 1 person can't work
Category: Computer::Build
Status: New
Priority: Medium
Owner: Helpdesk Coordinator
Machine: Unassigned
Location: Pittsburgh
Alt Phone Number: <blank>
Full Name:
Department:
CC List: None
Resolution: None
Owners only: <unchecked>
Comment: <blank>
________________

Child Ticket
*New User Setup - Company IT Helpdesk*

Process: User - New
Parent Ticket: New User Setup
Child Tickets: <listed>
State: 1
Submitter: Unassigned
Title: New User Setup (ERP)
Impact: 1 person can't work
Category: User::New
Status: New
Priority: Medium
Owner: IT Director
Machine: Unassigned
Location: Pittsburgh
Alt Phone Number: <blank>
Full Name:
Department:
CC List: None
Resolution: None
Owners only: <unchecked>
Comment: <blank>
________________________________
________________________________

And that concludes my ticket system config.  I've been using it for about 6 months now, and it has been working very well.  Again, the only request I currently have is to include some type of checklist/form system within the KB articles so that they can easily be imported into the tickets as task lists and provide me a solid way of confirming these tasks have been completed - rather than creating tickets for each item, or (as I'm doing now) just eyeballing a list.  OK for me as I've been doing this stuff for a while and have all of this in my head, but not for a new IT dept hire who needs this type of system as he grinds through every item (and gets interrupted in between items).

As an example of what I'm referring to and how I'm currently dealing with this (in my own opinion) shortcoming, I offer the following list of possible Use Setup scenarios I have at my company - each of these is a KB article that I copy into the Comments field of a ticket after the user/machine specific items have been entered by hand.  I have similar KB articles for Computer Build, Termed User and User Name Change scenarios.  Hopefully, it becomes readily apparent why a built-in checklist system would be useful.  I submitted this request at Advisory Kouncil at the last Konference and it seemed to be received positively, so hopefully something comes of it.

User Setup - Branch Site Desktop
User Setup - Branch Site Desktop (No Local Server)
User Setup - Branch Site Laptop
User Setup - IPSec VPN Branch Site Desktop
User Setup - IPSec VPN Branch Site Desktop (No Local Server)
User Setup - IPSec VPN Branch Site Laptop
User Setup - Pittsburgh Desktop
User Setup - Pittsburgh Laptop
User Setup - Remote Desktop
User Setup - Remote Laptop
________________

User Setup - Pittsburgh Laptop
************************************
This is the actual KB article for this user setup scenario - checklist functionality for these items after they are copied from the K1000's KB into a ticket would be outstanding.
************************************

Standard Account Setup
* Confirm spelling of name
* Confirm manager and site
* Confirm PC or laptop
* Confirm user account to user as template
* Create new user checklist
* Create network account (ADUC)
* Update network account fields
* Adjust perms on user folder
* Create mailbox (Exchange console)
* Request ERP account setup
* Update new user checklist

Standard Profile Build
* Display Properties
* Taskbar and Start Menu Properties
* Fold Options & Properties
* Office Picture Manager (default for JPG, activate MS Office)
* Windows Media Player (setup)
* Word (adjust toolbars)
* Excel (adjust toolbars, macro security)
* Registry imports (Add IE to Desktop, Turn Off Office Validation Add-In)
* Outlook (profile build)
* Adobe Reader (agreement & test in IE)
* Desktop shortcuts (apps, bookmarks, guides)

Remote Apps
* Get remote access approval from CEO
* AT&T dial-up (assign account & update dialer list)
* WiFi (setup & test connection)
* VPN (specify domain, set DNS servers)
* Citrix (build user profile, configure apps)
* Test AT&T & WiFi + OW, Citrix & VPN
* Remove WiFi test profile

Backup App
* Setup backup folders & files
* Update backup lists
* Update Grep PAR files
* Run backup app
* Review backup log
* Email Backup FAQ to user

Laptop Specific
* Acrobat Standard (license agreement, activate, *v6 - no to opening PDFs in IE)
* CD Burning App (license agreement)

Mobile Device
* Determine make and model
* Send applicable Company email setup instructions

All Company Offices
* Install network printers
* Install local printer

Pittsburgh
* Setup office phone and voicemail
* Send Company phones and welcome email

Lanier or Xerox Copier Scanner Profile Build
* Create Scanner folder in user folder
* Set permissions on Scanner folder (Lanier - Write Only, Xerox - Change)
* Build scanner profile on copier(s)
* Backup scanner profiles
* Create Desktop shortcut to Scanner folder

Department Specific
* Finance - Excel 2003 subtotal fix (reg import)
* Accounting - Accounting app (DB setup & connectivity test)
* Engineering - AutoCAD (check with lead engineer), install plotter(s)
* Manufacturing/QA - enable Lotus (reg imports)

Standard Maintenance
* MS Updates
* IE, User & System temp file cleanup
* Clear recent files & apps (Taskbar and Start Menu properties)
* Clear System Restore points
* Defrag paging file, system files & regular files (Diskeeper, O&O Defrag)

User Orientation
* Send Company IT welcome emails
* Provide network login
* Review system with user
* Update manager with status
________________________________
________________________________

Hope that helps!

John