/build/static/layout/Breadcrumb_cap_w.png

Blog Posts tagged with Custom Ticket Rules

Ask a question

The battle of the autoresponders!

Recently we got into a battle with a vendor's autoreponder and our service desk "New Ticket Via Email" configuration.  We accept email from unknown emails as our customers are students and faculty who tend to use gmail or other email accounts; our staff use the provided email address.  

A vendor sent out an email stating how wonderful their latest release is and it came into our support email address.  Our service desk responded to their Reply-To email address, which was the vendor's ticking system.  The vendor's ticketing system then sent a brand new "We got your email blah, blah, blah..." which our service desk responded to with a "We got your email blah, blah, blah."

When this first happend I just temporarily disabled the "New Ticket Via Email" button for the queue impacted.  This stoped the loop.  Then it happend a second time over the weekend, luckly I caught it after two hours but we still had to clean up a ton of emails.

So I set out to fix the "New Ticket Via Email" and only send a response out to domains of our choosing; in this case, company provided.  If someone sends in an email from a gmail account they will not get the auto response message but they will see our comments as we work with the ticket.  We are currently testing this out, but so far it seems to work.  We know that the "New Ticket Via Email" is sent out instantly whereas Custom Ticket Rules run every 3 minutes; we can live with this.    

 

To do this:

1. disable the "New Ticket Via Email"

2. Create a new Custom Ticket Rule where the frequency is "on Ticket Save"

3. Here is the SQL

Select
HD_TICKET.ID As ticket_number,
HD_TICKET.ID As TICKNUM,
'<SomeCommaSeperatedEmailsGoesHereWithoutTheBrackets>' As EMAILCC,
HD_TICKET.TITLE As TICKET_TITLE,
U1.USER_NAME As OWNER_NAME,
U3.USER_NAME As LASTINPUTNAME,
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_STATUS.STATE,
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,
U3.EMAIL As UPDATEREMAIL,
U3.FULL_NAME As UPDATERNAME,
Unix_Timestamp(TICKETCHANGE.TIMESTAMP),
TICKETCHANGE.COMMENT As COMMENT,
TICKETINITIAL.COMMENT As INITIAL_COMMENT,
TICKETCHANGE.DESCRIPTION As CHANGE_DESCRIPTION,
HD_CATEGORY.CC_LIST As CATEGORYCC,
HD_CATEGORY.NAME As CATEGORY_NAME,
U2.LOCATION As SUBMITTER_LOCATION,
U2.WORK_PHONE As SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME As TICKET_PRIORITY,
HD_QUEUE.NAME As QUEUE_NAME
From
HD_TICKET Join
HD_TICKET_CHANGE TICKETCHANGE On TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID Join
HD_TICKET_CHANGE TICKETINITIAL On TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID
And TICKETINITIAL.ID = (Select
Min(HD_TICKET_CHANGE.ID)
From
HD_TICKET_CHANGE
Where
HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) 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 = TICKETCHANGE.USER_ID Left Join
HD_QUEUE On HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY
Where
HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID And
HD_STATUS.ID = HD_TICKET.HD_STATUS_ID And
HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID And
HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID And
(HD_STATUS.NAME Not Like '%Closed%' And
U2.EMAIL Like '%@someDomainGoesHere%' And
TICKETCHANGE.DESCRIPTION Like '%Ticket Created%' And
HD_TICKET.HD_QUEUE_ID = 9)

4. Select "Send an email for each result row"

5. For subject we used "Service Desk - [$ticket_number] $ticket_title"

6. For the Email Column use "SUBMITTER_EMAIL"

7. For the body use something like this:

Hello,

We wanted to let you know that we have received your email, "$ticket_title", and will be taking care of it as soon as possible.

There is no need to reply right now, this is automatically generated to let you know we received your email.

If you would like to follow up about this issue please make sure that "Service Desk - [$ticket_number] $ticket_title" is in the subject line of all the emails you send regarding this.

Note that system outages are posted here: <someWebSite>

For urgent issues, please call:
<someNumbers>
Thanks,
<someOne>
<someURL>

 


Let me know what you think!  

 

 

 

Updated on 7.9.2013, I found an error in my sql.  

Be the first to comment

Adding Manager information to your KACE user table

First off, this depends on you using the LDAP function to bring all users into your KACE USER database.  This is done by adding 'manager' to the attributes to receive box.

Now you will notice that once the managers ARE imported from AD (Direct reports) that it comes in very ugly and sloppy, CN=PERSON,OU=SLOPPY,OU=UGH.

Now we need to trim that, and doing it by hand is not a doable task.
I have my LDAP set to run every day, just in case someone new gets added, but this also means that the manager field will update for anyone that doesn't have the correct manager.  This means, after the trim takes place, when the import runs the sloppy name will return, since LDAP thinks it's out of date.  This means the trim rule will have to run everytime your ldap runs.  I schedule the rule to run 5 minutes after the ldap, just to be safe.

Andddd here is the rule:

update `USER`
Set `CUSTOM_3` =
 IF(
        LOCATE('CN=', `CUSTOM_3`)  > 0,
        SUBSTRING(`CUSTOM_3`, 4, LOCATE(',', `CUSTOM_3`) - 4),
        `CUSTOM_3`
    ) 

This goes into the SELECT query area, and is enabled and scheduled.  No other checkboxes or actions needed.  You'll notice it goes to CUSTOM_3, this is CUSTOM_3 on the USER table as that is where I currently have MANAGER mapped to in the LDAP import.  This will need to be adjusted dependent on your own import mapping.

This should do the trick, you now have manager actual names in your user table for each user.  Why is this useful?  Well, with those names there, I can now reference them for ticket rules, such as "Send manager an email when ticket is idle."  By having that user name, you can reference that FULL_NAME table and get the manager email address, viola!

Hope this helps someone or leads them to bigger and greater ticket rules/notifications!

View comments (2)

Scheduling Ticket Creation

The instructions on this page are provided "as-is," without any express or implied warranty. In no event shall the author, or Dell be held liable for any damages arising from the use of the information provided on this page.

WARNING: Always review "Precautions when creating ticket rules" before writing any rules.
*Created on K1000 v6.2.109329.
This method for auto-creating tickets on a regular / scheduled / recurring basis requires creating a Custom Ticket Rule in a second queue (we'll call "Q2") to send an email to the queue we want the ticket created in (we'll call "Q1").  This is due to a previous K1000 update that restricts a Service Desk queue from emailing itself.

The email address you've configured for Q2 will need to be a valid submitter for Q1.
Pick one of the options below to make Q2 a valid submitter in Q1:
  • Option 1: Set Q1 to "Allow all users as submitters" and "Accept email from unknown users"
        oJdzWV.png
  • Option 2: Create a new user with Q2's email address.  This removes the need to "Accept email from unknown users"
        vSS9IX.png
  • Option 3: Create a new user with Q2's email address AND add that user to your submitter label for Q1.  This removes the need to check "Accept email from unknown users" and "Allow all users as submitters."
        0ytpDz.png
Now that Q2 is allowed to submit tickets to Q1, we'll configure the Custom Ticket Rule to send an email to create the ticket.  This rule must be created in Q2.

  1. Select Q2 from Service Desk > Configuration > Queues
  2. Scroll to the bottom of the page and click [Customize] next to "Custom Ticket Rules"
  3. Select Choose Action > New (SQL)
  4. Set the Name and Description (optional) as you like
  5. Copy the SQL below to the "Select SQL" box and replace "servicedesk" with the first part of Q1's Email Address (NOT the first part of Alternate Email Address) - the part of the Email Address before "@":
    Queue Email Address for reference, continue below for SQL query:

    SQL query and screenshot:
    select
    "servicedesk" as EMAILCOLUMN


  6. Check "Email each recipient in query result" and make sure no other boxes are checked except "Enabled"
  7. Enter a Subject for the email - this will become the Title for the generated ticket
  8. For "Column containing email addresses:" enter: "EMAILCOLUMN" (without quotes)
  9. For "Message:" the first line must be "@submitter=unassigned" to remove Q2 as the submitter of the ticket and avoid an email loop between the two queues.  The lines after the first will be added as the first comment of the generated ticket.  Here is a sample email:
    @submitter=unassigned
    This will be the initial comment on the created ticket.

    This line will also be included in the initial comment on the created ticket as well as all lines following this one.


  10. Set the Frequency and Next Run to define how often, and when to create the recurring ticket (DO NOT select 'On Ticket save' or a new ticket will be created every time any change is made to any ticket! ).
  11. Click the   Save   button.

You may now go back in to the rule you just created to click the   Run Now   button to test.

View comments (3)

Alert Queue when ticket is moved between queues.

My users are still getting use to KACE and often submit tickets to the wrong queue. When this happens owners from a team will move tickets to the appropriate queue. Unfortunately there is no email sent to the owners of the queue where the ticket was moved to alerting them of this fact. To fix this I have created a ticket rule that will email the owners in the new queue (by using the secondary email address of the queue) whenever a ticket is moved to their queue. 

Select SQL:

select distinct(HD_TICKET.ID), U.EMAIL as EMAIL from HD_TICKET
left join HD_TICKET_CHANGE TC on TC.HD_TICKET_ID = HD_TICKET.ID
left join HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = TC.ID
left join USER U on U.ID = HD_TICKET.SUBMITTER_ID
where CF.AFTER_VALUE = 'Opened'
and CF.FIELD_CHANGED = 'STATUS_NAME'
and TC.TIMESTAMP > subdate(NOW(), INTERVAL 10 SECOND)
and HD_TICKET.HD_QUEUE_ID = 1

Email each recipient in query results:
Subject: (TICK: $id) was moved
Column containing email addresses: EMAIL
Subject: 
The Ticket with ID: $id was moved to the Trouble Ticket team. Please review and address.

http://KBOXAddress/adminui/ticket.php?ID=$id

View comments (1)

Custom rule to format Hardware Address

A question was posted in the KACE Slack Community regarding the possibility of having a custom field for a computer's hardware address (aka MAC) be formatted in IEEE 802 format using colons. Always being up for a challenge, I created the following custom service desk rule to do that. To create this rule on your SMA, go to Service Desk, Rules and select the relevant queue. Under Action, select New (SQL)

A couple of notes:
My custom field is CUSTOM 19, in the database that means it is called CUSTOM_FIELD_VALUE18. You will have to adjust the statements below to match your custom field, notice that the database column is one less than the UI column.
My queue ID is 1, you will have to adjust that for your queue.

Here is the Select statement for the rule:

SELECT ID FROM HD_TICKET WHERE 
CUSTOM_FIELD_VALUE18 not regexp BINARY '([0-9A-F]{2}[:]){5}([0-9A-F]{2})'
AND HD_QUEUE_ID = 1

Credit to https://stackoverflow.com/questions/4260467/what-is-a-regular-expression-for-a-mac-address for the regular expression.

Here is the update statement:

UPDATE HD_TICKET SET CUSTOM_FIELD_VALUE18 = 
CASE
WHEN CHAR_LENGTH(CUSTOM_FIELD_VALUE18) = 12 THEN
UPPER(concat_ws(':', substr(CUSTOM_FIELD_VALUE18, 1,2), 
substr(CUSTOM_FIELD_VALUE18,3,2), 
substr(CUSTOM_FIELD_VALUE18,5,2),
substr(CUSTOM_FIELD_VALUE18,7,2),
substr(CUSTOM_FIELD_VALUE18,9,2),
substr(CUSTOM_FIELD_VALUE18,11,2)))
WHEN CHAR_LENGTH(CUSTOM_FIELD_VALUE18) = 17 THEN
UPPER(concat_ws(':', substr(CUSTOM_FIELD_VALUE18, 1,2), 
substr(CUSTOM_FIELD_VALUE18,4,2), 
substr(CUSTOM_FIELD_VALUE18,7,2),
substr(CUSTOM_FIELD_VALUE18,10,2),
substr(CUSTOM_FIELD_VALUE18,13,2),
substr(CUSTOM_FIELD_VALUE18,16,2)))
ELSE 'Please enter a valid hardware address'
END
WHERE ID = <TICKET_IDS>
The rule handles three scenarios:
The user entered twelve characters - I'm assuming that the user entered twelve characters that are valid for a hardware address.
The user entered seventeen characters - I'm assuming that the user entered seventeen characters that are valid for a hardware address, but they are lower case or are using the wrong separator.
The user did not enter a value or it is neither twelve or seventeen characters - The field is set to 'Please enter a valid hardware address'

Those rules aren't by any means going to cover all possible scenarios in the best way, but they will get the basic job done.

Set the rule to run on ticket save and watch the magic happen.


Be the first to comment
Showing 1 - 5 of 5 results

Top Contributors

Talk About KACE 1000