Creating, Testing and Troubleshooting Helpdesk Ticket Rules.
Notes: This document assumes you:
- 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.
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.
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.
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.
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:
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:
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.
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.
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.
Just like in testing, you can create a highly customized email. You might want to:
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.
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.
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.
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 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.
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
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.
