Blog Posts tagged with K1000 Ticket Rules

Ask a question

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

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

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

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

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

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

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

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

Starting from the top....
________________________________
________________________________

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

Main Tab: Helpdesk

Queue: Department
Queues (plural): Departments

Ticket: Ticket
Tickets (plural): Tickets

Process: Process
Processes (plural): Processes

Default Department: Company IT Helpdesk

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

Queue 1 - IT Helpdesk
________________

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

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

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

Home - Label - LDAP Labels

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

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

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

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

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

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

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

Ticket Change Notification

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

For complete details, see:
    $ticket_url

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

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

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

Customize Fields and Layout
________________________________

Helpdesk Customization
________________

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

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

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

Name                               User Settable

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

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

Name                  State

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

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

Name          Color       Escalation Time

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

Impact Values
________________

Name

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

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

Name            Label            Required            Permissions

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

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

Name       Field Type      Select Values                              Default

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

Ticket List Layout
________________

Name        Width

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

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

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

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

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

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

Order:
1

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

Frequency:
on Ticket Save

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

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

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

Title:
Autopopulate Department Field
************************************
This is one of the rules I referenced in the Ticket Layout section above.
************************************

Order:
10

Notes:
Automatically populates department field (custom field 4 in UI, custom field 3 in SQL) with department name.  This is to help facilitate creation of weekly Sales reports.
Create this ticket rule in Queue 1 (IT Helpdesk).

Frequency:
on Ticket Save

Select Query:
SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.SUBMITTER_ID <>0

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

Update Query:
UPDATE HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE3 = S.CUSTOM_2 WHERE T.ID=<TICKET_IDS>
________________

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

Order:
10

Notes:
Automatically populates termed user lookup field (custom field 3 in UI, custom field 2 in SQL) with submitter's full name.  This is to ensure searches done for termed users' tickets by name can be done after they have been removed from the system (removing users clears the Submitted By field and sets it to Unassigned).
Create this ticket rule in Queue 1 (IT Helpdesk).

Frequency:
on Ticket Save

Select Query:
SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
WHERE HD_TICKET.SUBMITTER_ID <>0

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

Update Query:
UPDATE HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
SET T.CUSTOM_FIELD_VALUE2 = S.FULL_NAME WHERE T.ID=<TICKET_IDS>
________________

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

Order:
11

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

Frequency:
15 minutes

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

X Send an email for each result row

Subject:
ATTENTION - Unassigned Ticket in KBOX Helpdesk Queue

Email Column:
SUPPORT

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

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

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

Order:
12

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

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

Frequency:
on Ticket Save

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

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

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

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

Order:
12

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

Frequency:
on Ticket Save

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

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

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

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

Order:
100

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

Frequency:
Manually (not enabled)

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

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

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

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

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

Queue 2 - HR Helpdesk
________________

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

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

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

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

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

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

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

Customize Fields and Layout (see after this section)

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

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

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

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

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

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

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

Ticket Change Notification

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

For complete details, see:
    $ticket_url

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

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

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

Customize Fields and Layout
________________________________

Helpdesk Customization
________________

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

Name                               User Settable

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

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

Name                  State

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

Priority Values
________________

Name          Color       Escalation Time

High          Red         1 hour
Medium        Black       none
Low           Grey        none
________________

Impact Values
________________

Name

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

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

Name            Label                         Required            Permissions

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

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

Name       Field Type      Select Values                              Default

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

Ticket List Layout
________________

Name        Width

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

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

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

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

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

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

Order:
10

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

Frequency:
on Ticket Save


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

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

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

________________

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

Order:
12

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

Frequency:
on Ticket Save

X Send an email for each result row

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

Email Column:
APPROVER

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

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

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

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

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

Thanks,

Company IT

Select Query:

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

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

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

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

Order:
14

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

Frequency:
Hourly

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

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

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

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

Order:
20

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

Frequency:
on Ticket Save
__________________________________________

X Send an email for each result row

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


Email Column:
OWNER_EMAIL

Email Body:

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

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

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

Thanks,

Company IT
_____________________________________

Select Query:

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

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

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

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

Process Configuration

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

Parent Ticket: New User Setup

Child Activities:

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

Parent Ticket
*New User Setup - Company HR Helpdesk*

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

Child Ticket
*New User Setup - Company IT Helpdesk*

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

Child Ticket
*New User Setup - Company IT Helpdesk*

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

Child Ticket
*New User Setup - Company IT Helpdesk*

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

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

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

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

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

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

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

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

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

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

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

All Company Offices
* Install network printers
* Install local printer

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

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

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

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

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

Hope that helps!

John

View comments (27)

Creating a ticket rule that will allow hardware inventory deletions without reopening closed tickets in 5.3

These instructions are for duplicating the reopen ticket rule, and adding to the SQL query, then disabling the original Reopen Ticket rule. 

  1. With the K1000 Open in a web browser, click on Service Desk or Help Desk
  2. Click on the Configuration Tab
  3. Click on Queues
  4. Select the email queue you want to edit.
  5. Scroll to the bottom, under System Ticket Rules: click on Reopen Ticket
  6. Scroll to the bottom and click Duplicate.
  7. Scroll to the bottom, next to Custom Ticket Rules: click Customize.
  8. Click Copy of Reopen Ticket
  9. In the Select Query: box add the exact code below:

    and DESCRIPTION NOT LIKE '%Changed Ticket Machine from "%" to %Unassigned%' 
    and DESCRIPTION NOT LIKE '%Changed Ticket Submitter from "%" to Unassigned%'
    and COMMENT NOT LIKE '%Machine % was deleted%' 
    and COMMENT NOT LIKE 'User % was deleted%

     

  10. Verify that the Enabled Checkbox is checked, and click Save to finalize the changes.
  11. Click on Reopen Ticket and uncheck the Enabled Checkbox to disable that email rule
  12. Click Save to finalize these changes.
Be the first to comment

KACE HELP DESK: How to setup Auto Fill the MACHINE and SUBMITTER information when a submitter creates a ticket

In order for the MACHINE field and SUBMITTER field to automatically contain the machine and submitter information, there first needs to be assets created and associated to the systems listed in Inventory.  This will allow you the option to have a drop down selection to choose from along with a Filter.

If you want to go the extra step and have it auto populate the MACHINE name and/or the SUBMITTER, then a Ticket Rule will need to be created to auto populate this information.  Below is an example Custom Ticket Rule:

SELECT

  HD_TICKET.ID

FROM

  HD_TICKET

WHERE

  HD_TICKET.MACHINE_ID = 0 AND

  HD_TICKET.SUBMITTER_ID <>

UPDATE

  HD_TICKET Inner Join

  USER On HD_TICKET.SUBMITTER_ID = USER.ID Inner Join

  MACHINE On USER.USER_NAME = MACHINE.USER SET HD_TICKET.MACHINE_ID = MACHINE.ID WHERE HD_TICKET.ID IN (<TICKET_IDS>)

NOTE: If you would like any other field to auto populate, a custom ticket rule would need to be created for that field name as well or added to the above.

KACE Technical Support does not actively support the creation of custom ticket rules. Custom ticket rules can be created with the help of free support from ITNINJA bloggers or a request ticket submitted to Dell KACE ProServices for a quote in developing custom ticket rule(s) for your environment. 

View comments (5)

Passing Data From Parent Tickets To Child Tickets.

Valid for K1 Appliances Versions 5.3.*

Hello Boxers,

If you use Processes in your Help Desk module of the k1000 then you probably experience a situation where you would have liked manually entered information from the Parent Ticket to be passed down the Child Tickets. 

An Example of this could be a Process for a New Employee. Since each employee as unique information (Names, Badge Numbers, Employee IDs and example) you would have to fill this in into each child ticket manually. However there is a way to automate this using a Custom Ticket Rule and a little bit of SQL.

We will go ahead and use the above scenario as the basis for this post so we will pull data from a parent ticket down to a child ticket in a process that is all about setting up an Employee Account. Our goal will be to pull the data from 3 custom fields down to the child ticket.

Keep in mind that when using SQL it is always best to test them out before applying them to your production environment. KACE Technical Support nor IT Ninja can be held responsible if any damage occurs from using the following SQL. 

Now let's get down to business. Here is a Screen Cap of my Custom Ticket Rule. You can Create CTRs by going to Help Desk -> Configuration -> Queue -> QUEUE_NAME_HERE -> Scroll down to buttom and click "Custom Ticket Rules:[Customize]" 

You will need to Create a CTR using the Drop Down menus but it does not matter what you use as we will be overwriting it.

Note: It is important to have a Frequency of "On Ticket Save"

 

 

Here are the queries so you can copy and paste them:

Select Query:

SELECT
PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.TITLE,
HD_TICKET.ID
FROM
HD_TICKET PARENT, HD_TICKET
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
AND HD_TICKET.RESOLUTION not like '%Fields Pulled From Parent Ticket%'

 

Update Query:

UPDATE
HD_TICKET PARENT,
HD_TICKET
SET
HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.CUSTOM_FIELD_VALUE1 = PARENT.CUSTOM_FIELD_VALUE1,
HD_TICKET.CUSTOM_FIELD_VALUE2 = PARENT.CUSTOM_FIELD_VALUE2,
HD_TICKET.RESOLUTION = CONCAT(HD_TICKET.RESOLUTION,' Fields Pulled From Parent Ticket.')
WHERE
((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

 

First thing you should notice is that the Update Query is using the fields CUSTOM_FIELD_VALUE followed by a number. These are the names for the custom fields in the SQL Database the K1 appliance uses. These fields will not be on by default. You can enable these fields through the specific Queue's configuration page. 

I went ahead and made these fields to be used for Name, ID, and Password for the new employee. Here is a screenshot of a blank ticket for an example.

 

We are going to use these 3 fields as the data we will want to pull from the Parent ticket down to the Child ticket; However, you can transfer the data any field possible (title, owner, priority, even a comment) if you wanted.

In my above screen shot 'Name' has the Database field name of CUSTOM_FIELD_VALUE0.

'ID' has the name of CUSTOM_FIELD_VALUE1.

'Password' has the name of CUSTOM_FIELD_VALUE2.

Here is the logic behind the Ticket Rule:

1. Look for a ticket that has as Parent Ticket and that has not yet been ran through this Ticket Rule:

WHERE

PARENT.ID = HD_TICKET.PARENT_ID     <---------- Has Parent Ticket 
AND HD_TICKET.RESOLUTION not like '%Fields Pulled From Parent Ticket%' <----------  Checks Resolution Field to see if the CTR ran for this ticket already.

2. Grab data from the fields of the parent ticket and pass them to the child

SET
HD_TICKET.CUSTOM_FIELD_VALUE0 = PARENT.CUSTOM_FIELD_VALUE0, <---------- Passes the 'Name'
HD_TICKET.CUSTOM_FIELD_VALUE1 = PARENT.CUSTOM_FIELD_VALUE1, <---------- Passes the 'ID'
HD_TICKET.CUSTOM_FIELD_VALUE2 = PARENT.CUSTOM_FIELD_VALUE2, <---------- Passes the 'Password'
HD_TICKET.RESOLUTION = CONCAT(HD_TICKET.RESOLUTION,' Fields Pulled From Parent Ticket.') <---------- Marks the ticket letting us know that it was updated by this CTR.

So any time a Parent Ticket of a process is saved it will automatically pass these fields down to the child tickets without the need to manually add them. This is extremely helpful if each child ticket is meant to be handled by different individuals or even seperate queues altogether!

Adding Data from Parent Ticket to the Child Ticket's Title.

Here is another example of a CTR to pass information from the Parent ticket down to the child ticket. The following CTR will grab the same "Name" field we used above ( CUSTOM_FIELD_VALUE0 ) and then appends it to the Title of the child ticket. So not only are we pulling data from the Parent Ticket, but we are now adding it to a completely different field of HD_TICKET.TITLE which is where the Title of the ticket is stored in the Database.

SELECT
PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.TITLE,
HD_TICKET.ID
FROM
HD_TICKET PARENT, HD_TICKET
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
AND HD_TICKET.RESOLUTION not like '% Name added to title.%'

 

UPDATE
HD_TICKET PARENT,
HD_TICKET
SET
HD_TICKET.TITLE = CONCAT(HD_TICKET.TITLE," ",PARENT.CUSTOM_FIELD_VALUE0),
HD_TICKET.RESOLUTION = CONCAT(HD_TICKET.RESOLUTION,' Name added to title.')
WHERE
((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)

So if we had a Parent ticket with the custome "Name" field filled with "Steven" it will then add "Steven" the Title of the Child ticket! Keep in mind this will not replace the title altogether but simply add it to the end.

View comments (6)

Primer for Writing SQL Select Statement Queries on the K1000 (w/Custom SQL Report example)

Primer for Writing SQL Select Statement Queries on the K1000 (wCustom SQL Report example)
__________________________________________________________________________________

The K1000's Reports, Smart Labels and Custom Ticket Rules are tremendously powerful and useful - if you know how to write MySQL queries.  For example, the most common situation I've encountered with custom reports is having the information that I want in two or more different Report Topics (i.e. Computers, Patch, Software) or not accessible at all, except in the canned reports (i.e. Warranty Info).  If only I could pull info from all of these....

Well, the good news is - using JOIN statements, you can!  

In this breakdown, I will attempt to explain some of the most common parts of a Select statement (a query that requests information from specified tables and formats it accordingly) and how you can write your own for the K1000.  Once you understand the basic format and functionality, tweaking the K1000 wizard-generated SQL statements will be less like a mystery and more like a puzzle that you just need to add your own pieces to in order to make it work the way you want.

If you've researched MySQL tutorials to try and find an "easy answer", you may have ended up more frustrated than enlightened due to the very simple examples given in most tutorials (along with the fact that there are multiple ways to write the same query).  I always thought, "If only there was a K1000-specific write up...", and that's why I'm doing this now.  On the bright side, since the K1000's tables are static, you don't need to be concerned about messing up anything (at least until you get into update queries with Custom Ticket Rules...).

One point I want to stress is that in order to save time and avoid too much repetition, this guide jumps around a bit.  However, hopefully any questions about earlier points will become clearer once you've read through everything.  In fact, I'd recommend going through this twice, just to be sure of that.
___________________________________________

Disclosure - I am *not* by any means a MySQL guru (i.e. someone who can write and troubleshoot queries blind).  I have had a ton of assistance in the past from others such as GillySpy, dchristian and airwolf to help me with my SQL reports, especially when I first started.  I'm a bit more comfortable these days with getting things the way I want, but please keep this in mind if/when you find things that could/should be done differently and/or better.  I'm only trying to help others get started with this, and hopefully it's more like a black belt student helping a no-belt student (with the sensei making sure everything is being done correctly) than the blind leading the blind.

Hope something here helps!

John
__________________________________________________________________________________

Primer
__________________________________________________________________________________

Since I'll be using these terms a *lot*, it's probably only fair to explain them a bit.  These are my own definitions, so please bear this in mind.
___________________________________________

Column - a list of data specific to one characteristic

Examples:
BIOS_SERIAL_NUMBER = service tag
CS_MODEL = model
RAM_TOTAL = memory
etc
___________________________________________

Table - a group of related columns

Examples:
The MACHINE table contains columns for BIOS_SERIAL_NUMBER, CS_MODEL, RAM_TOTAL, etc
The SOFTWARE table contains columns for DISPLAY_NAME, PUBLISHER, INSTALL_DATE, etc
The HD_TICKET table contains columns for TITLE, CREATED, OWNER_ID, SUBMITTER_ID, etc
___________________________________________

Database - the main container for related tables

Examples:
The ORG1 database contains tables for MACHINE, SOFTWARE, HD_TICKET, etc
The KBSYS database contains tables for DELL_ASSET, DELL_WARRANTY, PATCHLINK_PATCH, etc  
__________________________________________________________________________________

Example report
__________________________________________________________________________________

This is the custom report code I'll be dissecting and analyzing in an attempt to explain how things work.  While this example is by no means the most sophisticated, it will hopefully serve the purpose of illustrating the common parts of Select statements and how JOIN statements are used to pull data from multiple tables (i.e. something the report wizard currently does not do, as discussed above).  

The tables used in this report are:

MACHINE - contains general data on all of your machines
KBSYS.DELL_ASSET - has Dell-specific data, like warranty info
MACHINE_LABEL_JT - a MACHINE & LABEL "join table" (my own term?)
LABEL - consists of all of the various labels you have created
___________________________________________

*Title*
Company Production Systems

*Category*
Hardware (Custom)

*Description*
Lists all machines by name, make, model, service tag, location and ship date.

*SQL Select Statement*
SELECT DISTINCT M.NAME AS NAME, M.CHASSIS_TYPE AS MAKE, M.CS_MODEL AS MODEL, M.BIOS_SERIAL_NUMBER AS SERVICE_TAG, L.NAME AS LOCATION, DA.SHIP_DATE
FROM MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE (L.NAME rlike 'East Canton computers|Greensboro computers|Hammond computers|Hillsborough computers|King Of Prussia computers|Marelan computers|Moulton computers|Oak Hill computers|Pittsburgh computers|Remote computers|Santa Fe Springs computers|Shenango computers|Tarentum computers|Testnet computers|UK computers|Warren computers|Warren Warehouse computers|Wellston computers')
ORDER BY NAME
___________________________________________

Here is a sample of the output (in text format, with column sizes slightly reduced for this article):

Title: Company Production Systems
Description: Lists all company machines by name, make, model, service tag, location and ship date.
Category: Hardware (Custom)
Server Hostname: kbox.company.net
Generated: 2012/05/22 08:36:19

#    Name    Make     Model           Service Tag  Location              Ship Date            
  1  AALLEN  laptop   Latitude D520   14QZ2C1      Pittsburgh computers  2007-03-04 00:00:00  
  2  ABELL   desktop  OptiPlex 160L   46ZQD11      Marelan computers     2004-02-18 00:00:00  
  3  ACOX    laptop   Latitude D510   56Y7Q91      Remote computers      2006-03-26 01:00:00  
  4  AHALL   desktop  Dimension 4700  24X1L61      Tarentum computers    2005-01-18 00:00:00  
  5  AHILL   desktop  OptiPlex 210L   1RDJ0B1      Shenango computers    2006-10-27 00:00:00  
  6  AKING   laptop   Latitude D530   1Q3NJF1      Remote computers      2008-01-26 00:00:00  
  7  ALEE    desktop  OptiPlex 360    DK3KWH1      Moulton computers     2008-12-22 00:00:00  
  8  AREED   desktop  Dimension 4550  3NF8T31      Pittsburgh computers  2003-05-13 00:00:00  
  9  ASCOTT  laptop   Latitude D520   79BE1C1      Remote computers      2006-11-01 01:00:00  
 10  AYOUNG  desktop  OptiPlex 170L   JNMXH31      East Canton computers 2005-05-07 00:00:00  
etc...
__________________________________________________________________________________

Analysis and explanations
__________________________________________________________________________________

(01) Using aliases to customize column headers
___________________________________________

In the first part of a Select statement, the columns we wish to target are listed.  For example:

SELECT DISTINCT MACHINE.NAME, MACHINE.CHASSIS_TYPE, MACHINE.CS_MODEL, MACHINE.BIOS_SERIAL_NUMBER, LABEL.NAME, KBSYS.DELL_ASSET.SHIP_DATE

Using these, the report columns will subsequently be:

Name, Chassis Type, Cs Model, Bios Serial Number, Name, Ship Date
___________________________________________

Those are pretty straightforward to understand, but may not be how you want the columns to appear in your report.  This is where the alias command (AS) is used.  You follow a column name with what you want to appear - that's about all there is to it.  For example:

SELECT DISTINCT MACHINE.NAME AS NAME, MACHINE.CHASSIS_TYPE AS MAKE, MACHINE.CS_MODEL AS MODEL, MACHINE.BIOS_SERIAL_NUMBER AS SERVICE_TAG, LABEL.NAME AS LOCATION, KBSYS.DELL_ASSET.SHIP_DATE

Now the report columns will be:

Name, Make, Model, Service Tag, Location, Ship Date            
___________________________________________

You may notice that I'm aliasing MACHINE.NAME even though it already resolves to Name.  This is just a personal preference, as I want my statement to be perfectly clear (to me, at least) due to another "name" column being used (LABEL.NAME).

Another note is that I've also used (and have seen others use) aliasing in the following format - MACHINE.NAME as 'Name'.  I primarily used this format with the Classic Reports, but have switched formats with the current Reports module (again, as a personal choice).  As I mentioned earlier, MySQL is pretty flexible and there are multiple ways of writing the same thing, something you'll notice the more you look at others' code.
___________________________________________

Points to be aware of when using aliases:

Some words are "special" in MySQL and if used as aliases will generate (obtuse) errors when the query is run.  These are typically functions and operators in the MySQL language - words like SELECT, CASE and TYPE.  Some may be obvious since there are used frequently in queries, while others... not so much.  The best way to avoid issues completely is to test the query using the MySQL Query Browser (section 07 below), as this will highlight these "special words" in blue.  So if you see an alias in blue - change it.

Another point recently came up when a fellow ninja was trying to alias a CASE statement with the original column name (OS_NAME in this case) and the query produced unexpected results (the GROUP BY statement referenced this alias and grouped on the original column, not the alias).  The idea here is to be sure to use unique aliases and not existing column names, otherwise the query may not work as intended.
__________________________________________________________________________________

(02) Using aliases to abbreviate table names (i.e. save time typing)
___________________________________________

You'll notice in the example statements above that each column has the table name spelled out completely (i.e. MACHINE._____).  Fortunately, in practice, this isn't necessary to do as MySQL also provides table name aliasing to cut down on repetition.  Rather than using AS (as with the column name aliasing above), the abbreviated name is simply listed following the table name when it gets called.  The thing to keep in mind is that this is typically done later in the statement, with the FROM and JOIN statements:

FROM MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
___________________________________________

Concerning only the aliases, what this basically says is:

Abbreviate "MACHINE" as "M"
Abbreviate "KBSYS.DELL_ASSET" as "DA"
Abbreviate "MACHINE_LABEL_JT" as "ML"
Abbreviate "LABEL" as "L"

...which means the initial Select statement can be written more expeditiously as:

SELECT DISTINCT M.NAME, M.CHASSIS_TYPE, M.CS_MODEL, M.BIOS_SERIAL_NUMBER, L.NAME, DA.SHIP_DATE

Or with our column header aliases, as:

SELECT DISTINCT M.NAME AS NAME, M.CHASSIS_TYPE AS MAKE, M.CS_MODEL AS MODEL, M.BIOS_SERIAL_NUMBER AS SERVICE_TAG, L.NAME AS LOCATION, DA.SHIP_DATE
__________________________________________________________________________________

(03) Using FROM to specify the main table for data queries
___________________________________________

The FROM statement is used to indicate the main table used for pulling data.  For example, if you want to include all of your machines, I'd recommend using the MACHINE table as this will have all of the machines regardless:

FROM MACHINE M

Likewise, if you want to include all of your software, tickets or users, you'd want to specify the SOFTWARE, HD_TICKET or USER tables, respectively.  Following the FROM statement, use JOIN statements to include other tables as necessary.

In reviewing other Select statements, sometimes you'll see multiple tables listed in the FROM statement, such as:

FROM MACHINE M, KBSYS.DELL_ASSET DA

This is actually one way to write a JOIN statement, the most common of which are summarized further below.
___________________________________________

For those running multiple ORGs (databases), be aware that by default the query will target the ORG1 database.  When you need to pull results from additional ORGS, just specify the ORG# which contains the tables you want to target.  Note that this is not necessary for the KBSYS database, as it is a separate entity.

For example, the FROM and JOIN statements from original example query could be rewritten as follows (for clarity) to indicate that ORG1 is being targeted:

FROM ORG1.MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
JOIN ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN ORG1.LABEL L ON (ML.LABEL_ID = L.ID)

For targeting ORG2 (for example), it would be:

FROM ORG2.MACHINE M
LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)
JOIN ORG2.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN ORG2.LABEL L ON (ML.LABEL_ID = L.ID)

Again, note that KBSYS.DELL_ASSET remains the same throughout.
__________________________________________________________________________________

(04) Filter results using WHERE
___________________________________________

If you have used the filter in the report wizard, you'll be familiar with the conditions you can use to filter report results.  In the actual SQL code, these end up in the WHERE statement(s).  What I typically do for figuring out what code to use in the SQL report is to use the report wizard, (specifying the filter(s) I want to use), and then scavenge the corresponding code from the resulting SQL version of the report.  It's an optional statement, which is why you won't see them in every report.
___________________________________________

In my example report, I'm filtering on specific labels using a REGEX statement so that only the labels I specify (which indicate the machine's location) will be listed in the report:

WHERE (L.NAME rlike 'East Canton computers|Greensboro computers|Hammond computers|Hillsborough computers|King Of Prussia computers|Marelan computers|Moulton computers|Oak Hill computers|Pittsburgh computers|Remote computers|Santa Fe Springs computers|Shenango computers|Tarentum computers|Testnet computers|UK computers|Warren computers|Warren Warehouse computers|Wellston computers')
___________________________________________

Another example from a canned warranty report specifies that only Dell machines without a blank service tag number will be included in the report:

WHERE M.CS_MANUFACTURER LIKE '%dell%'
AND M.BIOS_SERIAL_NUMBER!=''
__________________________________________________________________________________

(05) Specify how results are organized with ORDER BY
___________________________________________

Typically the last statement in a Select statement is ORDER BY, which (as the name suggests) specifies which column should be used to sort everything in the report.  It's also optional, but can be useful if results aren't ordered correctly by default.
___________________________________________

In my example report, I specify the MACHINE.NAME alias (NAME) so that machines are sorted by name:

ORDER BY NAME
___________________________________________

The default sort order is ascending, but you can specify descending order by adding "desc" after the column name, i.e.:

ORDER BY LAST_SYNC desc

This is useful in reports dealing with time, for example listing all machines by warranty date or last sync time, in which having the oldest ones at the top makes the most sense.
__________________________________________________________________________________

(06) Understanding MySQL JOIN statements
___________________________________________

OK, this is a little out of order, but goes fairly deep so I wanted to save it for last.  Before diving into a JOIN breakdown and examples, I want to share the article that ultimately helped me understand MySQL Joins.  Reading this helped me understand exactly what I was trying to do, along with terms such as tables, etc:

http://www.devshed.com/c/a/MySQL/MySQL-Left-and-Right-Joins/

In short, here are the three main types of joins in terms of K1000 tables:

1) A plain JOIN statement lists every possible combination between tables, which generally isn't very useful (aside from the concept).

2) An INNER JOIN statement only pulls records that have matches in the two tables specified in the query, that is to say it retrieves rows that meet a specified condition.  An important point to make here is that if any records have a NULL (empty) entry, the report won't include them.

3) A LEFT JOIN statement is basically the same as an INNER JOIN, but also includes any records that have NULL entries.
___________________________________________

For example, if we want a report to pull info from the K1000's MACHINE table (i.e. MACHINE.NAME, MACHINE.CHASSIS_TYPE, MACHINE.CS_MODEL - NAME, MAKE & MODEL in plain terms) and also list the ship date (i.e. date of initial service) for these machines, we will need to include results from the KBSYS.DELL_ASSET table (where warranty, ship date and other Dell-specific items are stored).

An INNER JOIN statement like this will *only* list the PCs that have all of these items (i.e. only Dell machines with a ship date listed).  For example:

JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)

In plain English, this says "include the KBSYS.DELL_ASSET table data, match up the service tag data with the MACHINE table's bios serial number data, but exclude any machines that aren't listed in the KBSYS.DELL_ASSET table".

A LEFT JOIN statement will list all machines - even non-Dell machines and Dell machines without a ship date listed.  Each has its place, depending on what you want your report to list.

LEFT JOIN KBSYS.DELL_ASSET DA ON (DA.SERVICE_TAG = M.BIOS_SERIAL_NUMBER)

In plain English, this one says "include the KBSYS.DELL_ASSET table data, match up the service tag data with the MACHINE table's bios serial number data, but don't filter out any machines, even if they aren't in the KBSYS.DELL_ASSET table".
___________________________________________

Multiple JOIN statements can add a layer of complexity, so it's often useful to run test reports using only a single JOIN statement when expected results aren't being returned.  But if you keep in mind the differences between INNER JOIN and LEFT JOIN statements and are able to figure out which columns to match up, the rest should just be a matter of fine tuning.
__________________________________________________________________________________

(07) Connecting to the K1000 databases
___________________________________________

In order to gain a better understanding of how the K1000's data is arranged (and to figure out what different tables are available, when dissecting someone else's SQL statement or planning your own), it's essential to gain access to the K1000's tables.  This is where the MySQL Query Browser becomes an indispensable tool.

A Kace KB on the subject:

How to connect to the K1000 appliance database using MySQL Query Browser
http://www.kace.com/support/resources/kb/article/How-to-connect-to-the-K1000-appliance-database-using?action=artikel&cat=9&id=10&artlang=en

In short, download from here:
http://dev.mysql.com/downloads/workbench/

* I recommend the "Without installer" version, so you can unzip it and just run the MySQLQueryBrowser.exe program directly.
___________________________________________

Another Kace KB on connecting to the K1000's database:

Can I access the K1000 appliance database using a third-party reporting tool?
http://www.kace.com/support/resources/kb/article/Can-I-access-the-K1000-appliance-database-using?action=artikel&cat=9&id=9&artlang=e

* Here are the (default) settings I'm using to connect:

Server Host: K1000 (or your K1000 DNS name or IP)
Port: 3306
Username: R1
Password: box747
Default Schema: ORG1

Assuming this connects, you should see a list on the right under Schemata for information_schema, KBSYS, ORG1, etc, with ORG1 items expanded (ADVISORY, ADVISORY_LABEL_JT, etc).  If you see this, you are all set for browsing the K1000's tables and finding out what's in them.
___________________________________________

Note that if you want to connect to additional ORGs (ORG2, ORG3, etc), you will need to change the username to correspond (R2, R3, etc).  For example:

Server Host: K1000 (or your K1000 DNS name or IP)
Port: 3306
Username: R2
Password: box747
Default Schema: ORG2
__________________________________________________________________________________

(08) Pulling info from the K1000 databases
___________________________________________

Drag an item from the Schemata frame on the right (for example, ORG1 - MACHINE) into the SQL Query Area (top frame).  It should automatically create a SQL query that says "SELECT * FROM MACHINE M;".  To see what this query returns, hit the green "Execute the entered query" (lightning bolt) button to run the query, and you should now see a listing of all of your machines in the results frame below.  Use the scrollbar at the bottom and you can review all of the columns for the MACHINE table.  Note that some are marked NULL and that INNER JOIN statements between tables will exclude machines with NULL entries from the results.

If you go back to the Schemata frame (on the right) and expand the MACHINE table, the very first entry you'll see (ID) has a yellow key icon.  This indicates that this is the Primary Key for the table, which is used as a unique identifier for the table.  Typically, this is what you'll see referenced in JOIN statements, such as this:

JOIN MACHINE_LABEL_JT ON (MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID)

This statement basically says to include data from the MACHINE_LABEL_JT table where the MACHINE table's primary key (ID) matches the MACHINE_LABEL_JT table's primary key (MACHINE_ID).  
___________________________________________

A side note - the MACHINE_LABEL_JT table itself is a "Join Table" of the MACHINE and LABEL tables, the latter of which keeps track of all of the K1000's labels.  Just clear any existing statements out, drag the LABEL table over (to create a "SELECT * FROM LABEL L;" query), run the query (lightning bolt button) and you'll see all of your labels (machine and otherwise).
___________________________________________

It should also be noted that other columns can be referenced in JOIN statements as long as data correlates directly, such as:

LEFT JOIN MACHINE ON (MACHINE.BIOS_SERIAL_NUMBER = KBSYS.DELL_ASSET.SERVICE_TAG)

In this case, MACHINE.BIOS_SERIAL_NUMBER is not the primary key for the MACHINE table, but the data in the MACHINE.BIOS_SERIAL_NUMBER column corresponds directly with the data in the KBSYS.DELL_ASSET.SERVICE_TAG primary key column.
__________________________________________________________________________________

(09) How to find out which tables and columns to use
___________________________________________

* Look at example SQL reports in the K1000

There are number of canned reports that come with the K1000 that are in SQL format (Open Tickets last 7 days by Owner, for example).  If there's already an existing report that targets what you are after (but just doesn't do *exactly* what you need), you can use the Duplicate button to copy it and play with the code, determine which tables you need to target and pick up MySQL tricks for use in your other tickets (like using CONCAT statements and DATE_FORMAT, using the referenced report's code as an example).
___________________________________________

* Look at others' reports posted on ITNinja

Same idea as point 1, run a search on ITNinja for "report", "smart label" or "custom ticket rule", and whatever else it is you are trying to do.
___________________________________________

* Create test reports using the Report wizards

If you aren't having any luck with points 1 or 2, or are pretty comfortable with playing around and just need to know which tables to use or a template to tweak, this is probably the most useful approach.  

For reports, start out with the Add New Report wizard to target the items you want to include in the report.  Then once you have your report, select it from the Reports list and hit the Edit SQL link.  This will take you to the SQL code view of the report and let you see exactly what column and table names are being used, as well as any filters you have defined (in the WHERE section).  Depending on the number of tables you need to target, you may need to do this several times.  Once you have all of your columns and tables picked out, it's just a matter of determining how to write the JOIN statements - something that points 1 and 2 can usually clarify, along with test runs.
___________________________________________

* Use the KBSYS database for Dell-specific data

If you need to target warranty info, ship date, patches or other Dell-specific info, look at the KBSYS database.  This is not accessible from the report wizard, so SQL reports are currently the only way to access it.  There are some canned reports on the K1000 that should give you a good start, as well as some great examples on ITNinja.
__________________________________________________________________________________

(10) In conclusion...
___________________________________________

The most important things to keep in mind when trying to write your own SQL Select statements are:

1) Don't expect to get it right the first time.  SQL syntax errors are common (unless you are a MySQL guru), so double-checking for typos and making sure everything corresponds correctly (particularly when cobbling together existing code) are vital.  

2) When results aren't exactly what you expect, review the tables themselves and make sure you are targeting the table that contains everything you want in the FROM statement.  I ran into this situation with one report where not all of my machines were being listed, and it turned out to be due to the first table being called not containing all of my machines.

3) Simplify things and focus on basics - i.e. remove the WHERE statements (filters), test with minimum JOIN statements.  This often helps when running into errors that I don't completely understand.  Sometimes building multiple reports that each get partially what I need and then combining them into a more complex report have helped me understand why my original was throwing errors.

4) Use the MySQL Query Browser to test your Select statement.  This can be a great time saver by letting you see the exact results you'll get in the report without having to actually save and run the report on the K1000.  It can also be fun!  (Depending on your definition of fun, of course...)

5) When all else fails, post a question on ITNinja under the Dell KACE K1000 and K1000 Reporting or K1000 Labels.  This is where I've picked up most of the code I use for reference, have been assisted countless times, and is by far the greatest resource for getting things working.
__________________________________________________________________________________

updated 2012-10-17 - jv

View comments (11)
Showing 1 - 5 of 44 results

Top Contributors

Talk About Security