/build/static/layout/Breadcrumb_cap_w.png

Ticket to notify Managers

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.

0 Comments   [ + ] Show comments

Answers (7)

Answer Summary:
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 */
Posted by: dchristian 12 years ago
Red Belt
0
There's a reference in there to change_id.

Since your running on a schedule there wouldn't be a change id.
Posted by: allenn 12 years ago
Orange Senior Belt
0
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?
Posted by: dchristian 12 years ago
Red Belt
0
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.
Posted by: allenn 12 years ago
Orange Senior Belt
0
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!
Posted by: allenn 12 years ago
Orange Senior Belt
0
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.
Posted by: dchristian 12 years ago
Red Belt
0
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.
Posted by: allenn 12 years ago
Orange Senior Belt
0
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?
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ