Looking for some K1000 Best Practices and solutions for Notifications
I have read through many articles on this site, the sample setup post as well as the documentation provided. However, I find that a simple setup like this is pretty difficult if you are not familiar with MySQL queries. The fact that the original ticket issue (body of email) is being added as a comment rather than a unique field explaining the situation makes it very difficult to manage the triggers for notifications. Can anyone provide some guidance? I am using a mix of email on event notifications and some queries from this site to help a little. I can get 4 of these 7 items resolved this way but fall short on the remainder. I would prefer to use the MySQL statements since it provides more granular control. Can someone provide some guidance? Is there any way to view the table structure? - that would be tremendously helpful.
Kace 1000 build 5.4.76847
Low and Medium Priority
High Priority just has changes to timers and slight message modifications.
Desired Rules and Settings:
1. Notify on Create Ticket
a. Submitter Specific Message
b. All of IT Message
2. Notify for Unassigned Tickets
a. 15 minute timer
b. Notify All of IT
3. Notify on Assignment Change
a. Notify Owner (technician) - to let a tech know he has been assigned a ticket
4. Notify on Comment
a. Submitter and Owner (technician) – same message
5. Notify on Closed Ticket
a. Submitter Only
6. Notify on User Reply to Closed
a. REOPEN Ticket
b. Notify Owner (technician)
7. Notify on Escalation
a. Unresolved 1 week automatic auto escalation unless Waiting on…or On Hold
b. Owner Specific Message
c. Submitter Specific Message
Answers (1)
Here is the base SQL Template I use for my notification (time based) Rules:
SELECT
FIELD_138 as DAYSTART,
FIELD_140 as DAYEND,
FIELD_139 as DAYOFWEEK,
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- change fields
C.COMMENT, -- $comment
C.DESCRIPTION, -- $description
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY,
S.NAME AS STATUS,
I.NAME AS IMPACT,
CAT.NAME AS CATEGORY
FROM (HD_TICKET)
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
where (S.NAME = 'Waiting for Ticket Owner' or S.NAME = 'Closure Requested')
and DATE_SUB(NOW(), INTERVAL 1 DAY) > DATE(HD_TICKET.MODIFIED)
and (HD_TICKET.HD_QUEUE_ID = 1 or HD_TICKET.HD_QUEUE_ID = 13 or HD_TICKET.HD_QUEUE_ID = 10 or HD_TICKET.HD_QUEUE_ID = 9 or HD_TICKET.HD_QUEUE_ID = 12)
and TIME(NOW())< ASSET_DATA_20.FIELD_140 and TIME(NOW())> ASSET_DATA_20.FIELD_138
and DAYOFWEEK(NOW()) = ASSET_DATA_20.FIELD_139
GROUP BY HD_TICKET.ID
You will need to remove the FIELD_138-FIELD_140 in the select field as well as the WHERE statement calling on those fields (and ASSET_DATA_20 from the FROM) but I left them in there to give you an example of how I structure my rules. What are they there for? They call on an ASSET_DATA_20 table which holds my business hours and updates daily so that these ticket rules only shoot off when I want them too, unlike the KACE built in escalations which can go off 100 times in the middle of a night or weekend.
I also run an UPDATE query with all of these rules that reset the MODIFIED (as rules don't change the ticket MODIFIED on their own, even if a comment is added via ticket rule) so the timer for the notification resets. I also have a COUNTER built in so when the counter hits 3, another rule shoots off to notify the submitters manager that for 3 days this ticket has not been responded to.
update HD_TICKET
Set HD_TICKET.MODIFIED = NOW(),
HD_TICKET.CUSTOM_FIELD_VALUE10 = HD_TICKET.CUSTOM_FIELD_VALUE10 + 1
WHERE (HD_TICKET.ID in (<TICKET_IDS>))
Anyways: Play with this if you like. SQL can be quick and easy to learn. I'm not the sharpest plastic fork in the drawer, and I picked up on it, so I'm sure you can :). Focus on the WHERE section where you will build your filters to find the tickets you want it to find.
If you want the cleaned up version, I'll leave that for you as well:
select
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- change fields
C.COMMENT, -- $comment
C.DESCRIPTION, -- $description
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY,
S.NAME AS STATUS,
I.NAME AS IMPACT,
CAT.NAME AS CATEGORY
FROM (HD_TICKET, ASSET_DATA_20)
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
where
As far as your listed rules above: I have almost all of those, so they are good thoughts. Don't forget you can manipulate the EMAIL ON EVENTS check boxes and customize those emails as well, although I only have those going to submitters here and there, I depend on rules for the most part due to our pickiness.
Comments:
-
Thank you for the quick responses. I am going to try the code using MySQL Workbench since I am now connected and very helpful. To respond to one of the questions...I am using:
Events: Escalation being used to send email to all techs daily for any open tix.
Creation being used to send Ack to Submitter only.
Ticket Change being used to notify a tech that a ticket has been assigned to them. (thus limiting my us of comment notifications)
Ticket Closed being used to notify Submitter only.
Stock Rules:
WaitingOverdue unchanged.
OverdueClose unchanged
EmailOnClose not being used
CustomerResponded reopen on waiting for customer
Reopen - not working for some reason - creating new tickets - ticket id included in emails
Custom Rules:
Notify Owner on Unassigned every 15 minutes until not unassigned.
Create Ticket notification for techs
Does this help show where I may be going wrong? - civilnj 10 years ago-
I don't think you were going anywhere wrong?
Email on Events are good to use, just remember that if a ticket is submitted at the end of the day or friday (I don't know what your help desk hours are) then the email on events will fire off.
There are a couple work arounds for this, like a ticket rule that puts all tickets into a STALLED status (like OFFICE CLOSED) status at the end of each day or end of FRIDAY and then return it to it's previous status on Monday Morning. Remember that a STALLED ticket state does not trigger escalations.
I went the other way and physically put in business hours into an asset and then call upon them, but to each their own :)
I have the same custom rule for unassigned tickets, but since all my tickets have auto-assignment set, that should never technically go off, we hope. As far as Ticket Creation for techs, that's a good one for some companies but we have that with our OLD service desk and the flood of emails get out of control....
As far as RE-OPEN not working, I couldn't tell you why the canned rule is not working without more info. I don't use it. We don't want users commenting "thank you" on a ticket or us adding info to a ticket or them responding to an email on a ticket and the rule shooting off and reopening tickets. We prefer to let a user update the ticket, which will still inform us, and us reopening the ticket if we deem it necessary. - Wildwolfay 10 years ago
As for running rules based on the originating email, aka the first comment, it is possible to craft your SQL queries to look at the initial comment when you join to the comment table:
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
I'm not clear from your post which rules you need help getting to run. It might be best to post each that you need help with as separate questions. - chucksteel 10 years ago
Know SQL, and KACE will dance for you. - Wildwolfay 10 years ago
Thank you for the quick responses. I am going to try the code using MySQL Workbench since I am now connected and very helpful. To respond to one of the questions...I am using:
Events: Escalation being used to send email to all techs daily for any open tix.
Creation being used to send Ack to Submitter only.
Ticket Change being used to notify a tech that a ticket has been assigned to them. (thus limiting my us of comment notifications)
Ticket Closed being used to notify Submitter only.
Stock Rules:
WaitingOverdue unchanged.
OverdueClose unchanged
EmailOnClose not being used
CustomerResponded reopen on waiting for customer
Reopen - not working for some reason - creating new tickets - ticket id included in emails
Custom Rules:
Notify Owner on Unassigned every 15 minutes until not unassigned.
Create Ticket notification for techs
Does this help show where I may be going wrong? - See more at: http://www.itninja.com/question/looking-for-some-best-practices-and-solutions-for-notifications#sthash.HS00uLTK.dpuf - civilnj 10 years ago