/bundles/itninjaweb/img/Breadcrumb_cap_w.png

Blog Posts tagged with K1000 Ticket Rules

Ask a question

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

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

View comments (1)

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

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

View comments (4)

KACE How To Video Series: Service Desk Module

Summary:  KACE video addressing Servicedesk with the K1000 appliance.  Video is 36:50 minutes is broken down into two sessions:

Session 1:  Ticketing System and Configuration Including Workflow Process (Parent/Child) - Ends at 29:13 Minutes

Session 2:  Software Library, Knowledge Base, and User Portal  - Begins at 29:15 Minutes

 

Link:http://www.youtube.com/watch?v=f5MzXzmTsaY&feature=relmfu

Be the first to comment

KACE Helpdesk: Launch Programs and Remote Support from Ticket

Hey everyone, just a heads up, not sure if there is a better way to do this but in our testing it has worked well for us.

The primary goal of this project was to be able to launch MSRA requests from our helpdesk tickets if someone selects that they wanted to receive one. So how were we to do this for staff which only use the helpdesk portal and dont have access to inventory cards? Well its a little tricky.

Requirements:

Group policy set up on the machine to allow RA group configurations for access to the machine.

Added a field to the ticket called Remote Assistance? With Yes, No, Request Sent as values and No as default.

Custom Ticket rule which detects the tickets that require a link to be created. Also sets field value to Request Sent

Configuration of Rule:

Ticket rule configured to act on save for tickets with particular machine name beginning (in our environment) and only if not closed and request is 'yes'. This was done via the wizard. Wizard also used to set field value to 'Request Sent' after processing.

In configuration added the following on one line as a comment for every detected ticket:

<ahref="javascript:LaunchMSRA()">Launch Remote Assistance</a><script>function LaunchMSRA(){if(!document.all){   alert ("Available only with Internet Explorer.");   return;}var ws =newActiveXObject("WScript.Shell"); var Mach=prompt("What is the machine name?","");ws.Exec("C:\\Windows\\System32\msra.exe /offerRA " + Mach);}</script>

 What the above does is create a comment on the ticket with a hyperlink 'Launch Remote Assistance', it uses a javascript in order to run the executable with a prompt for the machine name to connect to. This uses msra but I'm certain that you could do this with vnc etc, in fact, if you were funky enough you could probably pull the machine name automatically also.

Have only tested this in IE. Also another caveat is that once this function is created due to the way that it is embedded in the ticket contents you cannot edit it, ie, if you make changes to the script and re-call it with the same name you will still get the first one because of the read order I'm guessing. My thoughts there was to create the function dynamically with a name that was based of the time to now in seconds.

Havent moved this to production yet but its looking promising. There are all sorts of things that you could use this sort of scripting from within comment banks with. I dont imagine its something that KACE would support though....

Any thoughts or comments I'd be glad to hear them.

Cheers,

Col

 

Be the first to comment

KACE/Bomgar Integration Guide 101

Bomgar|KACE Integration 101 <--------Click to Download PDF  *Updated* 9/26/2012

This is a comprehensive guide on how to easily integrate your Bomgar appliance with your K1000.  This guide is for Admins who have purchased the Bomgar appliance and NOT a setup guide for a hosted account that comes with the two free licenses.

What is Bomgar?

Answer= A secure remote desktop control option for every computer in your organization.  It is a physical appliance or a Virtual Machine.  Licenses are based on techs logged into accounts that are created on the Bomgar Appliance.
After implementing the steps in this guide you will be able to launch the Bomgar Jump Client via a Machine Action or allow their Users to request a Bomgar Session via a KACE HelpDesk ticket that will show up in the Bomgar Queue.

System Requirements

Bomgar version 11.1 or greater
Dell KACE K1000 Management Appliance server version 5.2.38773; other versions may work but this is the tested version
Network / Firewall configured to allow:
HTTP/HTTPS connectivity from the Bomgar Appliance to the web server
HTTP/HTTPS connectivity from the web server to Bomgar Appliance

(Note:  I had success integrating with KBOX version 5.3.53053)

Configure Dell KACE 

In order to use the Bomgar Integration for the Dell KACE K1000 service desk capability, you must modify the ticket form and configure the ticket rules within the appropriate service desk queue as follows:

1.    From the administrator KACE Management Center > Service Desk > Configuration, select Queues from the listed options.


 

2.    Select your Service Desk queue by clicking on the name (e.g. Bomgar IT Help Desk, in this example).  



 
3.    Select Customize Fields and Layout. This will allow you to add the Bomgar Session Request field to the Ticket Form.

 



4.    Scroll down to the Ticket Layout section, configure a custom field and label the new field, Bomgar Session. Choose the appropriate CUSTOM_# that is available in your environment. Set permissions to Owners Only - Hidden from Users.  


 
(Note:  Jot down the CUSTOM_#.  You will need this information in the future)


5.    Once you save the custom field scroll down on the same page, change the Field Type of the custom field to Single Select, and change the Select Values option to Send Request. Leave the Default option blank. Scroll to the bottom and select Save.


 
(Note:  The CUSTOM_#’s should correspond)


6.    Once your changes are saved, you will be directed back to the Service Desk > Configuration page. From here, select Customize from the Custom Ticket Rules section. Select the Add Ticket Rule Action.




7.    Define any criteria to create a dummy, or temporary Ticket Rule (which will later be changed in the SQL query) and select Next.


 
(Note: As a placeholder, you can use Approval - contains – Approved)


8.    Similar to step 7, define the next items required with Define Ticket Rule, setting Priority and the Value, and select Done.

 
(Note: As a placeholder, you can use Priority - change value to – High)


9.    Once you complete step 8, you will be directed to the Queue Rule : Edit Detail page. Now you should customize the queries and actions. In this example, the Custom Ticket Rule, Bomgar Session Request, is shown. Now you can customize the rule.

a.  First, change the Title to Bomgar Session Request. Next, change the Frequency drop-down to on Ticket Save.

b.  SQL Query Enable is enabled and enable Results are tickets, add a comment to each one. Insert the following text in the Comments: Bomgar session has been requested via email.

c. Enable Owners Only.



d.    The Select Query area should contain:

// ------------------ COPY BETWEEN THESE LINES -------------------------
select
HD_TICKET.*,
owner.EMAIL as OWNER_EMAIL,
if ((LENGTH(owner.FULL_NAME) = 0), owner.USER_NAME, owner.FULL_NAME) as OWNER_NAME,
submitter.EMAIL as SUBMITTER_EMAIL
from
HD_TICKET
left outer join USER owner on owner.ID = HD_TICKET.OWNER_ID
left outer join USER submitter on submitter.ID = HD_TICKET.SUBMITTER_ID
where LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE3) > 0
// ------------------ COPY BETWEEN THESE LINES -------------------------

 

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Important Note: 
{HD_TICKET.CUSTOM_FIELD_VALUE3, where 3 is calculated from:
CUSTOM_4 – 1 = 3 This is because the custom fields in the ticket layout start with the number 1, while the query variables start with the number 0.}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


e.    Enable the checkbox Send an email for each result row and modify the text to include:

·    Subject contains  Remote Support Invitation from YourServiceDeskName - [TICK:$id] : $title
·    Email Column contains SUBMITTER_EMAIL
·    Email Body should contain:


$owner_name would like to start a support session with you. To do so, please click on the link below and follow the online instructions.

https://YourBomgarURL.com/api/start_session.ns?issue_menu=1&customer_name=$submitter_email&external_key=TICK:$id

Bomgar enables a support representative to view your screen in order to assist you. Session traffic is fully encrypted to protect your system's data. Once a session has begun, you will be able to end it at any time.



 

f.  Within the Send an email for each result row section, make sure that:

·    Subject contains Remote Support Invitation from Bomgar IT - [TICK:$id] : $title
·    Email body contains:

$owner_name would like to start a support session with you. To do so, please click on the link below and follow the online instructions.

https://YourBomgarURL.com/api/start_session.ns?issue_menu=1&customer_name=$submitter_email&external_key=TICK:$id

Bomgar enables a support representative to view your screen in order to assist you. Session traffic is fully encrypted to protect your system's data. Once a session has begun, you will be able to end it at any time.


g.  Enable Run an update query, using the results from the one above. The update query should contain:

update HD_TICKET as T
    set T.CUSTOM_FIELD_VALUE3 = ''
  where
        (T.ID in (<TICKET_IDS>))

(Note: Be sure to use the correct CUSTOM_FIELD_VALUE identified in the earlier steps.)


10.    Now you need to customize the email notifications. Scroll down to the Email on Events: [Customize Emails] section from the queue edit page. The emails you are to customize are Ticket Closed and New Ticket via Email. Select Customize Emails.


 
 
Scroll down to the Ticket Closed Notification and modify it, as shown below. Change the https://kboxdemo.bomgar.com to https://YourBomgarURL.com.



11.    Also, change the Email Ticket Creation Acknowledgement. Change the https://kboxdemo.bomgar.com to https://YourBomgarURL.com.


 
Congratulations!  You have configured your help desk to send the user a link via email to request a session after their ticket has been created!!

Let’s test it!


End User Initiated Request

1.    Ensure a representative is logged into the Bomgar Representative Console to receive the incoming Bomgar Support Session request.

2.    As the end user, send an email request to the address of your Dell KACE service desk (e.g. helpdesk@company.com).

3.    Look for the confirmation email indicating that your ticket has been created. Open this email, and select the link to start a Bomgar Remote Support Session.

 



4.    Download and run the resulting Bomgar Customer Client executable.  

(Note: If Click-to-chat is configured, the session will start instantly in Chat Mode in your web browser.)


5.    The representative should see the incoming support session request in the Bomgar Representative Console.  Double-click to accept the session.

(Note: Configure Bomgar Equilibrium settings for the General queue if you wish to automatically distribute incoming sessions to the next available representative (Bomgar Enterprise licenses only).)

 

a.    While in the session, click on the Summary tab and verify that the External_Key field was automatically populated with the ticket number.
b.    Enter a couple chat messages.
c.    Submit some session notes on the Summary tab.
d.    If you configured the Representative Custom Link, select the Custom Link icon   on the tool bar, in the Representative Console, to reference the corresponding ticket in the service desk.
e.    End the session and close the tab from the Bomgar Representative Console to formally close out the session.

6.    Refresh the ticket view to see the updated Bomgar session information.  Make sure your email processor is set to process ticket update emails frequently.

Was it a success?  If no email was generated for the submitter, check the syntax on your Custom Ticket Rule! :)
Also give the K1000 about 2 minutes to generate the email.



Unattended Support (Machine Action)

This section describes how to configure your Dell KACE environment to use “Machine Actions” to start a Bomgar unattended remote support session from the Dell KACE K1000 inventory feature. This will allow you to have fast, secure access to servers and desktops using Bomgar’s Jump technology. This patent-pending Jump technology is cross-platform and allows secure remote access to any supported system, even on remote networks. For more details about Jump technology, please see www.bomgar.com/docs.  

Jump Clients can be pre-installed on any Bomgar supported client, including Windows, Mac, and Linux. You can either obtain a mass installer Jump Client using /login > Configuration > Jump Clients, or you can install a Jump Client one at a time from the Bomgar Representative Console during a support session.  You can also use the push and start Jump function for windows systems; this is also described as a local jump.

(Note:  The jump client needs to be installed on the machine you are trying to initiate a session with)

Using Unattended Support

Either of the following methods can be used with Dell KACE Appliance “Machine Actions” to Jump to a remote system.

Determine which Bomgar Jump method you will use (pre-installed Jump Clients, or the Jump-To / Push method).

From the Dell KACE Management Center select Settings > Machine Actions [Edit Mode]


 


Pre-installed Jump Client Method:  To start a support session with a pinned or specific Jump Client, use the following API scripting command:
http://YOUR.BOMGAR.SITE/api/client_script.ns?type=rep&operation=generate&action=start_pinned_client_session&search_string=KACE_HOST_NAME

(Note:  Only edit "YOUR.BOMGAR.SITE" in the API scripting command)

Jump-To / Push Method:  To push and start a session with a Windows system within a local network, use the following API scripting command:
http://YOUR.BOMGAR.SITE/api/client_script.ns?type=rep&operation=generate&action=push_and_start_local&hostname=KACE_HOST_NAME

When either of these commands is executed a small .bcrs file is downloaded, and the Bomgar Representative Console launches and executes the Jump automatically.

You can use the Machine Actions from the KACE K1000 inventory feature, as shown below.

 


Script to Install the Bomgar Jump Start Client

 
·  In the KBox Inventory interface create a Smart Label. You can name this whatever you want but for this example we will use  "Needs Jump Client"
·  Then make a Smart Label using the following criteria:

o    " Software Title"
o    "Does not contain"
o    "Bomgar Jump Client" or "Bomgar Jump Client [support.example.com]" (where "support.example.com" is your site name)

·    Save the Smart Label and go to the Scripting tab
·    Create a new script and set it for "Online KScript"
·    Under Dependencies, add your Jump Client Mass Deployment file (If this is an MSI file, be sure you have the KEY_INFO string handy)
·    Add a Task to "Launch a program..."

o    Directory: C:\Windows\System32
o    File: msiexec.exe (if an MSI file), or the Jump Client installer (if using EXE)
o    Parameters: /qn /i "$(KACE_DEPENDENCY_DIR)\<Jump Client file name>.msi" KEY_INFO=<key info string from download screen>

(Note:  Remove the <> from the parameters.  The quotes “” stay and are important for the function of the script )

·    Set "Limit Deployment to selected labels:" for the Smart Label you created

(Note:  It is a Best Practice to run the script on a test machine before applying the label)

·    Run the script as an Admin User for best results “Domain\Username”
·    Set a Schedule for the script to run


At this point you should have a functioning deployment method for putting Jump Clients on computers that do not currently have Jump Clients installed. The premise being that the computer will check in and if there is no Bomgar Jump Client installed, KBox will apply the Smart Label to that computer. When the Script next runs for all systems with that Smart Label, a Jump Client will be installed thus removing the Smart Label since a Jump Client will be found based on the criteria stated above.
 
Additional Smart Label parameters and other options in the script may be added to further customize the deployment, but these are beyond the scope of this solution.
    




View comments (2)
Showing 6 - 10 of 44 results

Top Contributors

Talk About Microsoft