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
Comments