K1000 Service Desk - Time Tracking Queue (Config, Custom Ticket Rules & SQL Reports)

This is the complete configuration for a Service Desk queue I created to allow my company to track employees time off (vacation, personal & sick days), along with custom ticket rules that automate notifications and reports that summarize everything cleanly (and flexibly).  I built this system to replace the current system that's been used for years:

1) Receptionist sends weekly email request for itineraries to all employees
2) Employees reply to request with days off listed in email body
3) Receptionist goes through all emails and enters days off into ERP system

Needless to say, the current process has been neither efficient nor accurate.  Last year alone, they were off (short) on my vacation time by 5.5 days due to inaccuracies in the current system.  Fortunately for my company, I'm an honest guy and track my own time carefully, but the main point is that we needed something less error prone.
________________________________

The K1000 Time Tracking queue, on the other hand, uses managers to drive the processes, instead of one receptionist:

1) Submitter creates ticket
************************************
Specifies Category (Request::Vacation, Request::Personal Day, Sick Day)
Specifies Manager via drop-down list
Specifies the start date, end date and total days of the request
All other fields are automatically populated by ticket rules
************************************

2) On initial ticket save, remaining fields automatically populated
************************************
Autopopulate Department Field ticket rule
Autopopulate Location Field ticket rule
Autopopulate Termed User Lookup Field ticket rule
************************************

3) Manager set as Approver
************************************
Autopopulate Approver ticket rule
Rule correlates manager's name via email address in Manager field
************************************

4) Email sent to manager to approve/reject request
************************************
Email Alert on Waiting on Approval ticket rule
************************************

5) Manager's approval email received by KBOX, approval field updated

6) On approval field value change (1st action), email sent to Submitter to advise on approval
************************************
Email Alert on Approval ticket rule
This ticket rule *must* be set to fire off before the Status Field Update on Approval ticket rule
************************************

7) On approval field value change (2nd action), ticket Status changed from "Waiting on Approval" to "Closed"
************************************
Status Field Update on Approval Update ticket rule
************************************

Two reports (which can be easily tweaked to only focus on a particular department, location or manager's employees) provide concise and detailed listings.  The detailed report lists each employee's request (along with dates & duration), while the concise report summarizes each employee's vacation, personal and sick days.
________________________________

Some of the settings may/will overlap with what I have setup in my main IT/HR "helpdesk" queues, so please forgive any repetition of information.  I'm including everything here to provide a complete setup guide.
________________________________
________________________________

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

________________________________
________________________________

Queue 4 - Time Tracking
________________

Helpdesk -> Configuration -> Departments -> Time Tracking
________________

Name: Time Tracking
Email Address: it.tt@kbox.company.net
Alt. Email Address: IT.TT@Company.Com
************************************
Email flow for this queue was setup in Exchange 2007 using a forwarding rule on the company.com mailbox to send all messages to the kbox.company.net address.  For more info on setting up email flow between the K1000 and Exchange 2007, please see this blog:

http://www.itninja.com/blog/view/k1000-email-setup-exchange-2007-barracuda-spam-filter
************************************

Customize Fields and Layout (see after this section)
Allow all users as submitters: 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>
************************************

Allow all users as approvers: no
Restrict Approvers By Label: managers
************************************
managers is a regular label populated by hand
************************************

Ticket Owners By Label: it director
************************************
it director is a regular label populated by hand
************************************

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: Request::Vacation
Status: Waiting On Approval
Impact: 1 person can't work
Priority: Medium

Email on Events:
None
************************************
All email flow is handled via custom ticket rules
************************************

Helpdesk Email Customization
************************************
None
************************************

System
 Ticket Rules:
************************************
None
************************************

Custom Ticket Rules: [Customize]
************************************
See after the Customize Fields and Layout 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 in all of my queues, 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

Request::Vacation       true
Request::Personal Day   true
Request::Other          true
Sick Day                true
Other                   true

________________

Status Values
************************************
I only use a handful of options in this queue, in comparison to my main IT Helpdesk queue, as the scope here is much narrower.
************************************

Name                  State

Waiting On Approval   Stalled
Open                  Opened
Closed                Closed
________________

Priority Values
************************************
Just went with the defaults for this queue, again because of the limited scope.
************************************

Name          Color       Escalation Time

High          Red         1 hour
Medium        Black       none
Low           Grey        none
________________

Impact Values
________________

Name

1 person can't work
1 person inconvenienced
________________

Ticket Layout
************************************
My custom fields are all over the place as I copied my existing HR queue, tweaked the names/settings a bit and moved them around in an order that made sense.  Decisions were also made to add more fields, move them around, etc and many of the custom fields were already coded for in the ticket rules, so I've left these as is.  Of course, doing things over I would rearrange them in order, but honestly it's just aesthetics (this being a "self-contained" queue).
************************************

Name            Label                                         Required          Permissions

SAT_SURVEY      Please tell...                                Not Required      Hidden
SUBMITTER       Submitter                                     Not Required      User Create
TITLE           Title                                         Not Required      Hidden
IMPACT          Impact                                        Not Required      Hidden
CATEGORY        Category                                      Always Required   User Modify
STATUS          Status                                        Not Required      Owners Only - Hidden from users
PRIORITY        Priority                                      Not Required      Hidden
OWNER           Owner                                         Not Required      Read Only
MACHINE         Machine                                       Not Required      Hidden
ASSET           Asset                                         Not Required      Hidden
CUSTOM_1        Full Name                                     Not Required      Read Only
CUSTOM_8        Manager                                       Always Required   User Create
CUSTOM_2        Department                                    Not Required      Read Only
CUSTOM_3        Location                                      Not Required      Read Only
CUSTOM_4        Enter Date as "YYYY/MM/DD" (ex: 2012/06/21)   Always Required   Read Only
CUSTOM_5        Start Date                                    Always Required   User Create
CUSTOM_6        End Date                                      Always Required   User Create
CUSTOM_7        Total Days                                    Always Required   User Create
CUSTOM_9        undefined                                     Not Required      Hidden
CUSTOM_10       undefined                                     Not Required      Hidden
CUSTOM_11       undefined                                     Not Required      Hidden
CUSTOM_12       undefined                                     Not Required      Hidden
CUSTOM_13       undefined                                     Not Required      Hidden
CUSTOM_14       undefined                                     Not Required      Hidden
CUSTOM_15       undefined                                     Not Required      Hidden
APPROVAL_INFO   Approver                                      Not Required      User Modify
DUE_DATE        Due Date                                      Not Required      Hidden
CC_LIST         CC List                                       Not Required      Hidden
CREATED         Create                                        Not Required      Owners Only - Visible to Users
MODIFIED        Modified                                      Not Required      Owners Only - Visible to Users
PARENT_INFO     Parent Ticket                                 Not Required      Hidden
SEE_ALSO        See Also                                      Not Required      Hidden
REFERRERS       Referrers                                     Not Required      Hidden
RESOLUTION      Resolution                                    Not Required      Hidden
________________

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

Name       Field Type      Select Values                           Default

CUSTOM_1   Text
CUSTOM_2   Text
CUSTOM_3   Text
CUSTOM_4   Text
CUSTOM_5   Text
CUSTOM_6   Text
CUSTOM_7   Single Select   0.5,1.0,1.5,2.0,2.5,3.0,3.5,4.0,        1.0
                           4.5,5.0,5.5,6.0,6.5,7.0,7.5,8.0,
                           8.5,9.0,9.5,10.0,10.5,11.0,11.5,12.0,
                           12.5,13.0,13.5,14.0,14.5,15.0              
CUSTOM_8   Single Select   manager1@company.com,
                           manager2@company.com,
                           etc
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
Submitter    12
Category     20
State Date   9
End Date     9
Total Days   9
Approver     12
Approval     12
Modified     20
________________________________

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

Title:
Autopopulate Department Field
************************************
Goal is one less field for submitters to have to complete.
************************************

Order:
10

Notes:
Automatically populates department field (custom field 2 in UI, custom field 1 in SQL) with department name.

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.HD_QUEUE_ID = 4 and 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_VALUE1 = S.CUSTOM_2 WHERE T.ID=<TICKET_IDS>
________________

Title:
Autopopulate Location Field
************************************
Goal is one less field for submitters to have to complete.
************************************

Order:
10

Notes:
Automatically populates location field (custom field 3 in UI, custom field 2 in SQL) with location name.

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.HD_QUEUE_ID = 4 and 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.LOCATION WHERE T.ID=<TICKET_IDS>
________________

Title:
Autopopulate Termed User Lookup Field
************************************
This rule is for creating a placeholder for the submitter's name so it is still trackable after the user has been termed and removed from the system.
************************************

Order:
10

Notes:
Automatically populates termed user lookup field (custom field 1 in UI, custom field 0 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).

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.HD_QUEUE_ID = 4 and 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_VALUE0 = S.FULL_NAME WHERE T.ID=<TICKET_IDS>
________________

Title:
Autopopulate Approver
************************************
Rule correlates manager's name via email address in Manager field (CUSTOM_8)

Preliminary setup notes:
1) Need to add manager's user account in K1000 to "managers" label (only label allowed to approve in this queue)
2) Need to add manager's email address to this queue's CUSTOM_8 single select field like this:
manager1@company.com,manager2@company.Com,etc
************************************

Order:
10

Notes:
Automatically sets approver field to user's manager when ticket is saved.

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,
U4.EMAIL as MANAGER_EMAIL,
U4.ID as MANAGER_ID,
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 USER U4 on U4.EMAIL = HD_TICKET.CUSTOM_FIELD_VALUE7
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.APPROVER_ID = 0) and HD_TICKET.HD_QUEUE_ID = 4 )

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

Update Query:
Update HD_TICKET
JOIN USER U4 on (U4.EMAIL = HD_TICKET.CUSTOM_FIELD_VALUE7)
SET HD_TICKET.APPROVER_ID = U4.ID
WHERE (HD_TICKET.ID in (<TICKET_IDS>))
________________

Title:
Email Alert on Waiting on Approval
************************************
Sends an email to specified manager when ticket is first created and saved.  The manager clicks on the applicable link in the email, hits Send and the ticket gets approved/rejected.
************************************

Order:
20

Notes:
Sends an email to manager when a Time Tracking ticket's approval has not been specified.

Frequency:
on Ticket Save

Select Query:
select HD_TICKET.CUSTOM_FIELD_VALUE0 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE1 as DEPARTMENT,
HD_TICKET.CUSTOM_FIELD_VALUE2 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE4 as SDATE, HD_TICKET.CUSTOM_FIELD_VALUE5 as EDATE,
HD_TICKET.CUSTOM_FIELD_VALUE6 as TDAYS, HD_TICKET.CUSTOM_FIELD_VALUE7 as MANAGER, 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.HD_QUEUE_ID = 4 )
__________________________________________

X Send an email for each result row

Subject:
[TICK:$id] TIME OFF REQUEST: $status_name

Email Column:
MANAGER

Email Body:

A ticket in the Time Tracking queue needs your approval, please review.

Ticket ID:         $id
Created:           $created
Category:          $category
Status:            $status_name

Requesting User:   $fullname
Manager:           $manager
Department:        $department
Location:          $location

Start Date:        $sdate
End Date:          $edate
Total Days:        $tdays

___________________________________________________________________

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

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

Thanks,

Company IT
________________

Title:
Email Alert on Approval
************************************
This rule sends an email to the ticket submitter when the ticket's approval status changes (but only if the ticket isn't in Open or Closed status, this way it only gets sent once).  Must run before Status Field Update ticket rule due to this.
************************************

Order:
25

Notes:
Sends an email to submitter when a Time Tracking ticket's approval has been specified.

Frequency:
on Ticket Save

Select Query:
select U2.EMAIL as SUBMITTER, HD_TICKET.CUSTOM_FIELD_VALUE0 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE1 as DEPARTMENT,
HD_TICKET.CUSTOM_FIELD_VALUE2 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE4 as SDATE, HD_TICKET.CUSTOM_FIELD_VALUE5 as EDATE,
HD_TICKET.CUSTOM_FIELD_VALUE6 as TDAYS, HD_TICKET.CUSTOM_FIELD_VALUE7 as MANAGER, 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 ((STATE not rlike 'closed|open') and HD_TICKET.APPROVAL != '' and HD_TICKET.HD_QUEUE_ID = 4 )
__________________________________________

X Send an email for each result row

Subject:
[TICK:$id] TIME OFF REQUEST APPROVAL: $approval


Email Column:
SUBMITTER

Email Body:

A ticket in the Time Tracking queue that you submitted has been updated, please review.

Ticket ID:         $id
Created:           $created
Category:          $category
Approval Status:   $approval

Requesting User:   $fullname
Manager:           $manager
Department:        $department
Location:          $location

Start Date:        $sdate
End Date:          $edate
Total Days:        $tdays
___________________________________________________________________

Thanks,

Company IT
________________


Title:
Status Field Update on Approval Update
************************************
Status field automation, ticket is closed after manager approves/rejects request.
************************************

Order:
30

Notes:
Changes Status field from "Waiting On Approval" to "Closed" after manager approval status.

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 (  HD_TICKET.APPROVAL rlike 'Approved|Rejected' and HD_STATUS.NAME = 'Waiting On Approval' and HD_TICKET.HD_QUEUE_ID = 4 )

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

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 = 'Closed' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
(HD_TICKET.ID in (<TICKET_IDS>))
________________________________

Custom SQL Reports
************************************

*Title*
Time Tracking - Detailed Listing

*Category*
Time Tracking (Custom)

*Description*
Detailed listing of days off by employee.

*SQL Select Statement*
SELECT T.CUSTOM_FIELD_VALUE0 as EMPLOYEE,
REPLACE(REPLACE(C.NAME, 'Request::Personal Day', 'Personal Day'),'Request::Vacation', 'Vacation Day') as CATEGORY,
T.CUSTOM_FIELD_VALUE4 as START_DATE, T.CUSTOM_FIELD_VALUE5 as END_DATE, T.CUSTOM_FIELD_VALUE6 as TOTAL_DAYS,
T.CUSTOM_FIELD_VALUE1 as DEPARTMENT, T.CUSTOM_FIELD_VALUE2 as LOCATION, U.FULL_NAME AS MANAGER
FROM HD_TICKET T
JOIN HD_CATEGORY C ON (C.ID = HD_CATEGORY_ID)
JOIN USER U on (U.EMAIL = T.CUSTOM_FIELD_VALUE7)
WHERE T.HD_QUEUE_ID = 4
ORDER BY EMPLOYEE, CATEGORY, START_DATE

*Break on Columns*
EMPLOYEE
________________________________

Example text output:

Title: Time Tracking - Detailed Listing
Description: Detailed listing of days off by employee.
Category: Time Tracking (Custom)
Server Hostname: kbox.company.net
Generated: 2012/06/26 11:19:41

Category      Start Date  End Date    Total Days  Department  Location    Manager            
8 of Employee: Smith, John
Personal Day  2012/05/15  2012/05/15  1.0         IT          Pittsburgh  Johnson, Dave  
Sick Day      2012/02/27  2012/02/28  2.0         IT          Pittsburgh  Johnson, Dave  
Sick Day      2012/04/23  2012/04/23  1.0         IT          Pittsburgh  Johnson, Dave  
Vacation Day  2012/01/13  2012/01/13  0.5         IT          Pittsburgh  Johnson, Dave  
Vacation Day  2012/02/20  2012/02/20  1.0         IT          Pittsburgh  Johnson, Dave  
Vacation Day  2012/05/03  2012/05/03  1.0         IT          Pittsburgh  Johnson, Dave  
Vacation Day  2012/05/14  2012/05/14  1.0         IT          Pittsburgh  Johnson, Dave  
Vacation Day  2012/06/04  2012/06/04  1.0         IT          Pittsburgh  Johnson, Dave  

1 of Employee: Green, Dan
Personal Day  2012/06/21  2012/06/21  1.0         IT          Pittsburgh  Johnson, Dave  
________________

*Title*
Time Tracking - Concise Listing

*Category*
Time Tracking (Custom)

*Description*
Concise listing of days off by employee.

*SQL Select Statement*
SELECT T.CUSTOM_FIELD_VALUE0 as EMPLOYEE,
SUM(CASE WHEN C.NAME like '%Vacation%' then T.CUSTOM_FIELD_VALUE6 else 0 END) as VACATION_DAYS,
SUM(CASE WHEN C.NAME like '%Personal%' then T.CUSTOM_FIELD_VALUE6 else 0 END) as PERSONAL_DAYS,
SUM(CASE WHEN C.NAME like '%Sick%' then T.CUSTOM_FIELD_VALUE6 else 0 END) as SICK_DAYS,
T.CUSTOM_FIELD_VALUE1 as DEPARTMENT, T.CUSTOM_FIELD_VALUE2 as LOCATION, U.FULL_NAME AS MANAGER
FROM HD_TICKET T
JOIN HD_CATEGORY C ON (C.ID = HD_CATEGORY_ID)
JOIN USER U on (U.EMAIL = T.CUSTOM_FIELD_VALUE7)
WHERE T.HD_QUEUE_ID = 4
GROUP BY EMPLOYEE
________________________________

Example text output:

Title: Time Tracking - Concise Listing
Description: Concise listing of days off by employee.
Category: Time Tracking (Custom)
Server Hostname: kbox.company.net
Generated: 2012/06/26 11:17:34

Employee     Vacation Days  Personal Days  Sick Days  Department  Location    Manager            
Smith, John            4.5              1          3  IT          Pittsburgh  Johnson, Dave
Green, Dan               0              1          0  IT          Pittsburgh  Johnson, Dave
________________________________
________________________________

Hope that helps!

John