Creating, Testing and Troubleshooting Helpdesk Custom Ticket Rules (4294265)

- Understand it is possible to cause a negative impact to the performance of the appliance if the ticket rule is written improperly and will test with a    test environment prior to full implimentation in a live environment.

1. Backup your KACE SMA appliance database. Please see How to manually run the backup script and retrieve for more information.
2. When creating a ticket rule with the wizard make sure to disable the rule if you intend to do further editing. If you do not disable it then the events that trigger it might occur before completion and cause undesirable results.
3. Consider creating a separate help desk queue for testing your rule. Go to Service Desk | Configuration | Queues to create one. A duplicate of the current help desk can also take place if you are running a 5.5 version or newer.

Examine Business Rules

All of this should be done under the guise of business rules. You should think through as many business rules as possible before implementing your ticket rules. It can be very difficult to accommodate for certain assumptions later on.

Consider the Built-in Events for Email-based Rules

If all you want to do is send notification emails then consider some of the built-in events for email triggers on change, closure, etc. Each category can have a different default email address, etc.

Creating & Using Help desk Ticket Rules

The Wizard

While this document does not cover the wizard you should be sure that the wizard does not accomplish what you want before proceeding. It is a much easier, faster and reliable way to create a rule if possible.

The Select Query: Database and the help desk tables

Knowledge of the database and schema will be a big help. Refer to our other articles about accessing the database using MySQL Query Browser or other tools and the database schema. Contact technical support with any questions.

The most commonly accessed help desk-related tables are:

• HD_TICKET (most of the attributes of a ticket)
• HD_STATUS (details of status choices)
• HD_PRIORITY (details of priority choices)
• HD_IMPACT (details of the case impact)
• HD_CATEGORY (details of priority choices)
• USER (submitter, owner and updater information)
• HD_TICKET_CHANGE (ticket history)
• LABEL [and HD_TICKET_LABEL_JT] (details about labels)

Testing Results of your Query

Viewing and Emailing Query Results

Query results can be sent to an email. This is only useful if you need to keep a record in your email, do not have another tool to test queries (like MySQL Query Browser) instead, or are doing batch processing on a schedule and need to work with many rows. The two techniques are:

1. Use the View Ticket Search Results link under the query pane.
2. Use the checkbox Send query results to someone.

Send custom email to any account

SELECT 'testuser@testdomain.com' AS TESTEMAIL FROM HD_TICKET

Or

SELECT 'testuser@testdomain.com, testuser2@testdomain2.com' AS TESTMULTIPLEEMAIL 
FROM HD_TICKET

Now configure the email to go to TESTEMAIL or TESTMULTIPLEEMAIL. See more in the next section on how to customize the data in the body of the email.

Note: Embedded images are not supported in custom ticket rules.

Test queries with MySQL Query Browser

The MySQL Query Browser can be used to test query results in advance. This is a much faster way of testing and can receive instant feedback on syntax errors and missing data.

Logging Entries to a Ticket

A customized message can be logged as a ticket entry and it could be a simple debug message for your testing.

Note: This entry cannot have any data from the query results in it, but it can contain any text you want. See documentation for more. Use custom emails for data debugging.

Implementing Actions

Sending email for a result row

Just like in testing, you can create a highly customized email. You might want to:

• notify a customer of a password update
• notify about new releases
• notify of bug fixes
• link back to the support user portal
• Ask a customer to verify their contact information

Here’s an example of a rule that compares due dates with the current date. If the due date is in the past then it will flag that case as high importance and send a reminder email.

Below is the query. Many of the columns in this query was generated by the wizard. Select all the columns that we are going to use in the rule at some point. Compare the due date with the current time when this is run. Do not run against closed cases.

select HD_TICKET.*, 
   HD_TICKET.ID as TICKNUM, 
   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, 
   STATE, 
   U1.USER_NAME as OWNER_NAME, 
   U1.FULL_NAME as OWNER_FULLNAME, 
   U1.EMAIL as OWNER_EMAIL, 
   U2.USER_NAME as SUBMITTER_NAME, 
   U2.FULL_NAME as SUBMITTER_FULLNAME, 
   U2.EMAIL as SUBMITTER_EMAIL

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                   

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.DUE_DATE < NOW() and   
   HD_STATUS.NAME 

Now the custom email can be sent to the owner with dynamic content. This example below will generate an email as shown.

Doing an update

An Update Statement can be performed which will modify the data in the database. Now that we have constructed an email to notify the owner we should do the update as promised, which is to set the priority to high.

update HD_TICKET as T  set T.HD_PRIORITY_ID = 2 where T.ID in ()

Note: this is automatically passed as a delimited list of all the values of a column called ID which in our case was HD_TICKET.ID or specifically 6288.

• Note: You cannot perform an update that will trigger another email or another rule.

Combining Rules and Creating Workflow

The biggest factor here is what data do you want to act on. Changes to a specific ticket on-demand, or a scheduled based, batch approach.

Notes on Scheduling Tickets: There is a known issue with the KACE SMA appliance preventing the ability to schedule what time of day a ticket rule to run. However, it will always run at the same time of day based on when the rule was last modified. Therefore, to have it run daily at 10:00pm, re-save it at 10:00pm.

Ticket rules can be combined by using the “*ticket rule ordering*”. See What are ticket rules for more information about this.

More troubleshooting

Using the Run Log

The run log shows at the bottom of the screen any progress of your actions (query, email actions, ticket entry and update statements). It will show only the most recent information.

Here is an example from a successful run of the above query and email and update. Nothing changed the last time this was run. 

The Run Now button

The Run Now button at the bottom is useful if you are testing or executing a rule that is a batch based rule.

Note: It is not useful for testing most ticket save rules because they depend on a specific ticket number and often take action on data in the change.

Other Simple Techniques

Limit your test results by using specific ticket id numbers

If you are testing using a specific open ticket # or ticket # range then consider adding this to your query and/or update to limit the scope of your test. You can remove these when your testing yields confident results

Accessing Users

• The OWNER_ID coumn in HD_TICKET refers to the current owner of record, which can be changed.
• The SUBMITTER_ID column in HD_TICKET refers to the current submitter of record, which can be changed.
• The USER_ID column in HD_TICKET_CHANGE refers to the updater. This is not necessarily either the case owner nor the submitter.
• An audit on the original submitter would be very difficult, if not impossible, to track with our SQL statement rule editor. You should look at the case manually and read the history.

Getting access to tickets in other queues

It would be possible by adding the following to the where clause of your query:

and HD_QUEUE_ID=X

Where X is the ID number of the other queue which can be determined by looking at the HD_QUEUE_ID table.