Ticket to notify Managers

Question

Hi guys. I need some help here on how to create a ticket rule that will send an email to a manager distrubution list (AD group) if the ticket is:
 Status = NEW
 Impact = 'Many people can't work"
 Owner = Unassigned
 
 I thought this would have been easy to create, however I am currently having problems trying to make the select query work whenever I add the condition Owner Fullname = Unassigned. When I try to test the query, it just doesn't find any tickets that has this condition. I've tried different variations of this like Owner Fullname contains Unassigned, etc. but none work. 
 
 Any help would be appreciated. Thanks all.

Answer

There's a reference in there to change_id.
 
 Since your running on a schedule there wouldn't be a change id.

Answer

Hi David, so you mean I need to just take this part? 
 ===============================================================
 /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID 
 
 AND C.ID=<CHANGE_ID> 
 
 /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID 
 ===============================================================
 
 Also, if I were to use the SQL statements you originally wanted me to try out, how do I go about sending the results of that via email to a distribution group?

Answer

Hey allenn,

Try this:

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 = 'New'

                         AND HD_IMPACT.NAME = 'Many People Can\'t Work'

                         AND HD_TICKET.OWNER_ID = 0

                         and HD_TICKET.HD_QUEUE_ID = 17/* <--- Enter your Queue Number Here */

Your going to want to test for the owner_id = 0 (not sure you can do this in the wizard).

Make sure you change the queue_id to match your queue.

Its in the very last line of the SQL.

Answer

Dchristian thanks man!
 
 This is what I have been looking for ---> AND HD_TICKET.OWNER_ID = 0
 This is if the Owner Name is "Unassigned" (please correct me if I'm wrong). I could never figure out how to get that Where clause to work until you showed me this one. 
 
 I'll be using this and will let you and the other guys know if I ran into some issues again. Many, many thanks!

Answer

David,
 Thanks for the info again. I will still test that query you gave me. By the way, so if I wanted to send an email to a group, how do I that?
 Also, if I wanted to include the title of the Ticket in the email subject, and other information about the ticket included in the email body (title, category, priority, severity status, and others) how do I pull that information from that query?
 In the query here:
 http://www.kace.com/support/kb/index.php?action=artikel&cat=8&id=613&artlang=en#Appendix_Send_an_email_to_the_su
 the author was using $title for the Title, $category for Category and so on. 
 
 How or where are these variable declared?
 
 Any help again would be great! Thanks.

Answer

You can use any column that you return in your select.
 
 So if your select returns STATUS_NAME the variable is $status_name.
 
 I would recommend connecting to the back end DB and playing around with the queries.
 
 Once you have them in your select, the rest is easy.

Answer

David, here's my ticket rule. The condition is basically, if the ticket status is New, AND Impact is "Many people can't work", AND Owner is "unassigned", send an email to management group.
This rule works fine, if I use the Frequency On Ticket Save. This rule runs and an email to the group is sent. However, when I change the rule Frequency to 15 minutes, for some reason it does not get triggered.
====
SELECT

-- ticket fields

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://vk1000/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') AS 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, -- $priority

-- about status

S.NAME AS STATUS, -- $status

-- about impact

I.NAME AS IMPACT, -- $impact

-- about category

CAT.NAME AS CATEGORY, -- $category

-- other fields

-- -- example of static distribution list

'<i have a group email here>' AS NEWTICKETEMAIL -- $newticketemail

FROM HD_TICKET

/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID

AND C.ID=<CHANGE_ID>

/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID

/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID

/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID

/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID

/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID

/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID

/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

WHERE S.NAME LIKE 'New%' AND I.NAME LIKE 'Many people can\'t work' AND HD_TICKET.OWNER_ID = 0
/* this is necessary when using group by functions */

GROUP BY HD_TICKET.ID

HAVING 1=1

========================================

What do you think is causing this not to run when Frequency is changed to 15 minutes or some other time?

Ticket to notify Managers

Answers (7)

Posted by:

View more:

Related Questions

Related Links

Related Posts

KACE Product Support

Ticket to notify Managers

Answers (7)

Posted by:

Don't be a Stranger!

View more:

Related Questions

Related Links

Related Posts

Share