K1000 Service Desk - Equipment Request Queue (Config & Custom Ticket Rules)

This is the complete configuration for a Service Desk queue I created to help my company's IT department track equipment requests and purchases, along with custom ticket rules that automate notifications (i.e. advise the approver that a ticket needs approval, allow the approver to approve/reject via email, advise the submitter that the approval has been approved/rejected, etc).  It's a pretty simple setup with only one approver hard-coded into the ticket rules.  For more flexibility, check out my Time Tracking Queue (which I'll be posting shortly), which is a lot more flexible and does some more tricks. Hopefully helpful for others looking to do a little more with their K1000 Service Desk.
________________________________

Again, I'm using this queue to track equipment requests and purchases.  I have these broken into three main scenarios:

1) Request - we already own the item (hardware, software) and it needs deployed to a user, we are just tracking the request & approval.

2) Purchase - equipment needs purchased, but not being assigned to any particular user (needs approval & PO# for purchase.

3) Purchase/Request - equipment needs purchased and deployed, basically a combination of the previous two.
________________________________

Equipment Request queue processes and ticket rule flow:

1) Submitter creates ticket

2) IT Director set as Approver
************************************
Autopopulate Approver ticket rule
************************************

3) Email sent to IT Director to approve/reject request (and enter PO#, if applicable)
************************************
Email Alert on Waiting on Approval ticket rule
************************************

4) IT Director's approval email received by KBOX, approval field updated

5) 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
************************************

6a) On approval field value change (2nd action), ticket Status changed from "Waiting on Approval" to "Open" for tickets in Purchase category
************************************
Status Field Update on Approval Update - Purchase ticket rule
************************************

6b) On approval field value change (2nd action), ticket Status changed from "Waiting on Approval" to "Closed" for tickets in Request category
************************************
Status Field Update on Approval Update - Request ticket rule
************************************

Note - the Email Alert on Approval ticket rule *must* be set to fire off before the Status Field Update on Approval ticket rules, as it depends on the ticket status to *NOT* be set to Open or Closed (and the Status Field Update rule *changes* the Status field to Open on approval)
________________________________

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 3 - Equipment Request
________________

Helpdesk -> Configuration -> Departments -> Equipment Request
________________

Name: Equipment Request
Email Address: it.er@kbox.company.net
Alt. Email Address: IT.ER@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.
************************************

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: it director
************************************
it director is a regular label populated by hand
************************************

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: Purchase/Request::Equipment
Status: Waiting On Approval
Impact: 1 person inconvenienced
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

Purchase/Request::Equipment        true
Purchase/Request::Software         true
Purchase/Request::Other            true
Purchase::Equipment                true
Purchase::Software                 true
Purchase::Other                    true
Request::Equipment                 true
Request::Software                  true
Request::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

Open                  Opened
Waiting On Approval   Stalled
Waiting - Overdue     Stalled
Closed                Closed
Reopened              Opened
________________

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

Many people can't work
Many people inconvenienced
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                   Always Required     User Create
IMPACT          Impact                  Not Required        Hidden
CATEGORY        Category                Always Required     User Modify
CUSTOM_5        undefined               Not Required        Hidden
STATUS          Status                  Not Required        User Modify
PRIORITY        Priority                Not Required        User Modify
OWNER           Owner                   Not Required        Owners Only - Hidden from users
MACHINE         Machine                 Not Required        Hidden
ASSET           Asset                   Not Required        Hidden
CUSTOM_3        Requestor's Full Name   Always Required     User Create
CUSTOM_1        Requestor's Location    Always Required     User Create
CUSTOM_4        Requestor's Department  Always Required     User Create
CUSTOM_2        Equipment Type          Always Required     User Create
CUSTOM_6        Software Type           Not Required        User Create
CUSTOM_15       Quantity                Not Required        User Create
CUSTOM_7        Reason                  Always Required     User Create
CUSTOM_8        Reason Comments         Not Required        User Create
CUSTOM_9        Vendor                  Not Required        User Create
CUSTOM_10       Vendor Link             Not Required        User Create
CUSTOM_11       Price (-S&H)            Not Required        User Create
CUSTOM_12       Make                    Not Required        User Create
CUSTOM_13       Model                   Not Required        User Create
CUSTOM_14       PO#                     Not Required        User Create
DUE_DATE        Due Date                Not Required        User Create
APPROVAL_INFO   Approver                Not Required        User Modify
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,        
                           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   Single Select   Software,Laptop,Desktop,Monitor,BW
                           Laser Printer,Color Laser Printer,
                           Inkjet Printer,Phone,Fax,Desktop Copier,
                           Office Copier,Docking Station, Router,
                           Other
CUSTOM_3   Text
CUSTOM_4   Single Select   Engineering,Finance,HR,IT,Management,
                           Marketing,Office,Purchasing,QA,Sales
                           Shipping
CUSTOM_5   Text
CUSTOM_6   Single Select   N/A,MS Office 2007,MS Office 2010,         N/A
                           PDF Create,PDF Converter Enterprise,
                           Adobe Acrobat Std,Other
CUSTOM_7   Single Select   Other - Specify In Comments,               Other -
                           New Employee,Replacement,Upgrade,          Specify In Comments
                           Helpdesk Stock
CUSTOM_8   Text
CUSTOM_9   Single Select   N/A,CDW,Dell,Consultants,Other             N/A
CUSTOM_10  Text
CUSTOM_11  Text
CUSTOM_12  Text
CUSTOM_13  Text
CUSTOM_14  Text
CUSTOM_15  Single Select   1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,       1
                           16,17,18,19,20,21,22,23,24,25,26,27,
                           28,29,30,31,32,33,34,35,36,37,38,39,
                           40,41,42,43,44,45,46,47,48,49,50,51,
                           52,53,54,55,56,57,58,59,60,61,62,63,
                           64,65,66,67,68,69,70,71,72,73,74,75,
                           76,77,78,79,80,81,82,83,84,85,86,87,
                           88,89,90,91,92,93,94,95,96,97,98,99,100+
________________

Ticket List Layout
________________

Name        Width

TICK:       10
Modified    20
Title       40
Priority    9
Status      9
Submitter   12
Owner       12
Category     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 Approver
************************************
This queue only has one approver (the IT Director), so I specify him by his User ID number (1855) and just assign that.  Goal is one less field for submitters to have to complete.
************************************

Order:
10

Notes:
Automatically sets approver field to IT Director 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,
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.APPROVER_ID = 0) and HD_TICKET.HD_QUEUE_ID = 3 )

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

Update Query:
update HD_TICKET
    set HD_TICKET.APPROVER_ID = 1855
  where
        (HD_TICKET.ID in (<TICKET_IDS>))
________________

Title:
Email Alert on Waiting on Approval
************************************
Sends an email to IT Director when ticket is first created and saved.  He clicks on the applicable link (includes a PO# if it's a purchase), hits Send and the ticket gets approved/rejected (and the PO# field gets populated) accordingly.
************************************

Order:
11

Notes:
Sends an email to IT Director when an Equipment Request ticket's approval has not been specified.

Frequency:
on Ticket Save

Select Query:
select 'it.director@company.com' as APPROVER, HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE3 as DEPARTMENT,
HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE1 as EQUIPMENT, HD_TICKET.CUSTOM_FIELD_VALUE5 as SOFTWARE,
HD_TICKET.CUSTOM_FIELD_VALUE6 as REASON, HD_TICKET.CUSTOM_FIELD_VALUE7 as RCOMMENTS, HD_TICKET.CUSTOM_FIELD_VALUE8 as VENDOR,
HD_TICKET.CUSTOM_FIELD_VALUE9 as VLINK, HD_TICKET.CUSTOM_FIELD_VALUE10 as PRICE, HD_TICKET.CUSTOM_FIELD_VALUE11 as MAKE,
HD_TICKET.CUSTOM_FIELD_VALUE12 as MODEL, HD_TICKET.CUSTOM_FIELD_VALUE13 as PONUMBER, HD_TICKET.CUSTOM_FIELD_VALUE14 as QUANTITY,
HD_TICKET.DUE_DATE as DUE_DATE, 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.HD_QUEUE_ID = 3 )
__________________________________________

X Send an email for each result row

Subject:
[TICK:$id] EQUIPMENT PURCHASE/REQUEST: $status_name

Email Column:
APPROVER

Email Body:

A ticket in the Equipment Request queue needs your approval, please review.

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

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

Equipment Type:       $equipment
Software Type:        $software
Quantity:             $quantity
Reason:               $reason
Reason Comments:      $rcomments

Vendor:               $vendor
Vendor Link:          $vlink
Price (-S&H):         $price
Make:                 $make
Model:                $model
PO#:                  $ponumber

Due Date:             $due_date

___________________________________________________________________

To APPROVE this request, please click here and enter PO# (if applicable):
<mailto:it.er@company.com?subject=[TICK:$id]&body=@approval%20=%20Approved%0d%0a%0d%0a@CUSTOM_14%20=%20>

To REJECT this request, please click here:
<mailto:it.er@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 rules due to this.
************************************

Order:
11

Notes:
Sends an email to submitter when an Equipment Request ticket's approval has been specified.

Frequency:
on Ticket Save

Select Query:
select U2.EMAIL as SUBMITTER, HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE3 as DEPARTMENT,
HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE1 as EQUIPMENT, HD_TICKET.CUSTOM_FIELD_VALUE5 as SOFTWARE,
HD_TICKET.CUSTOM_FIELD_VALUE6 as REASON, HD_TICKET.CUSTOM_FIELD_VALUE7 as RCOMMENTS, HD_TICKET.CUSTOM_FIELD_VALUE8 as VENDOR,
HD_TICKET.CUSTOM_FIELD_VALUE9 as VLINK, HD_TICKET.CUSTOM_FIELD_VALUE10 as PRICE, HD_TICKET.CUSTOM_FIELD_VALUE11 as MAKE,
HD_TICKET.CUSTOM_FIELD_VALUE12 as MODEL, HD_TICKET.CUSTOM_FIELD_VALUE13 as PONUMBER, HD_TICKET.CUSTOM_FIELD_VALUE14 as QUANTITY,
HD_TICKET.DUE_DATE as DUE_DATE, 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 ((STATE not rlike 'closed|open') and HD_TICKET.APPROVAL != '' and HD_TICKET.HD_QUEUE_ID = 3 )
__________________________________________

X Send an email for each result row

Subject:
[TICK:$id] EQUIPMENT PURCHASE/REQUEST APPROVAL: $approval

Email Column:
SUBMITTER

Email Body:

A ticket in the Equipment Request queue that you submitted has been updated, please review.

Ticket ID:            $id
Created:              $created
Issue:                $issue
Category:             $category
Approval:             $approval

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

Equipment Type:       $equipment
Software Type:        $software
Quantity:             $quantity
Reason:               $reason
Reason Comments:      $rcomments

Vendor:               $vendor
Vendor Link:          $vlink
Price (-S&H):         $price
Make:                 $make
Model:                $model
PO#:                  $ponumber

Due Date:             $due_date
___________________________________________________________________

Thanks,

Company IT
________________


Title:
Status Field Update on Approval Update - Purchase
************************************
Status field automation.  The submitter tracks purchases and only close the ticket once the purchase has been received.
************************************

Order:
20

Notes:
Changes Status field from "Waiting On Approval" to "Open" after IT Director updates approval status for tickets in Purchase categories.

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_CATEGORY.NAME like 'Purchase%' and HD_TICKET.HD_QUEUE_ID = 3 )

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

Title:
Status Field Update on Approval Update - Request
************************************
More status field automation.  A ticket in the Request category is automatically closed after being approved.
************************************

Order:
20

Notes:
Changes Status field from "Waiting On Approval" to "Closed" after IT Director updates approval status for tickets in Request categories.

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_CATEGORY.NAME like 'Request%' and HD_TICKET.HD_QUEUE_ID = 3 )

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>))
________________________________
________________________________

Hope that helps!

John