/build/static/layout/Breadcrumb_cap_w.png
10/17/2016 1152 views
I am having some problem where I am able to move a ticket with Rule and modifying some part, but I also need a notification run. 

Here are the queries:

Select SQL
select HD_TICKET.*,
                        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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
                        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_TICKET.TITLE like '%Employee Email and Equipment Request Form%') and HD_TICKET.HD_QUEUE_ID = 3 )


UPDATE QUERY
update HD_TICKET, HD_CATEGORY as T5
SET HD_TICKET.HD_QUEUE_ID = 11
and HD_PRIORITY.ID = New
WHERE HD_TICKET.ID = ;

EMAIL EACH RECIPIENT
Subject:  [TICK:$ticknum] Assigned to $queuename: $title
Column containing email address: NEWTICKETEMAIL
Message:
$submitter_fname has opened a ticket.  
 
The submission was:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)
Title: $title
Priority: $priority
Status: $status

History: $comments
Please see your ticket at https://kace.company.com/adminui/ticket.php?ID=$ticknum


For Email Each Recipient, I know I haven't declared the variables in Select SQL, but as soon I declare or call from SQL it gives me an error. 

Any help?
Thanks
2 Comments   [ + ] Show comments

Comments

  • Can you post the error message you get?
  • Or can you post how you declare the variables? Where should get an emailnotification?

All Answers

1
Try this select-sql
select HD_TICKET.*,
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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
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,
'yourmailadress@yourcompany.com' AS NEWTICKETEMAIL
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_TICKET.TITLE like '%Employee Email and Equipment Request Form%') and HD_TICKET.HD_QUEUE_ID = 3 )

Enter the email address where the notification should be sent.



Answered 10/18/2016 by: svmay
Red Belt

  • I forgot to mention earlier that I already have one Rule in place which sends out an email notification whenever a ticket is assigned to that queue manually. Will the SQL posted by you not conflict with that one? I will try the SQL query posted and report back.
    • Who would you like to notify by email?
      • a group email address
      • Is the select in the other rule the same? I think you get two mails instead of one (when the select is the same)
      • what do you want to happen when a new ticket is created in this queue?
  • well I already have a rule in place which sends out an email notification to a group email address. I have a main queue where all tickets fall in "Tech Group" from there tech group user manually assign tickets to their respective queue, "Equipment Request". When they manually assign, I have a rule created under "Equipment Request", as soon as a ticket comes in an email is dispatched to notify a group email address, which is working fine if they manually assign the ticket.

    To eliminate this process, I want a Rule which runs every 15mins to look for anything like "Equipment....." and transfer it to the "Equipment Request" from "Tech Group" and sends an email notification to group email address. I was able to create the Rule to move the ticket from "Tech Group" to "Equipment Request", but the Notification Rule which I already have in place seems like not sending email notification, it seems that only works when someone manually assign the ticket to that group not when a Rule is ran.

    I hope I was able to explain :)
    • ok, I hope I have already understand you - sorry english is not my native.

      Again for me, that I understand you correctly - you want that a new ticket with a specific title automatically change the queue to "equipment request" and when this happen a group get an mailnotification - right?
      • I would make a custom rule to change the queue when the title contains - so the ticket change automatically the queue. I would create another custom rule In the queue "equipment request", where send the mail notification for your group when a new ticket is in this queue.
  • exactly, thats what I have right now. 1 rule to send mail notification when a ticket is added to the queue, and 2nd in the "tech queue" which changes ticket automatically. but rule to send mail notification in "equipment request" only working when the ticket is assigned manually to the group not if the ticket is being moved by a rule.
  • Here is the Rule which sends mail notification:

    SQL
    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
    Unix_Timestamp(C.TIMESTAMP),
    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 https://kace.lush.com/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
    ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
    H.COMMENT, -- $comments
    -- 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, -- $priority
    -- about status
    S.NAME AS STATUS, -- $status
    -- about impact
    I.NAME AS IMPACT, -- $impact
    -- about category
    CAT.NAME AS CATEGORY, -- $category
    -- other fields --
    HD_QUEUE.NAME AS QUEUENAME, -- $queuename
    -- example of static distribution list
    'group@company.com' 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
    AND H.ID = (SELECT Min(HD_TICKET_CHANGE.ID)
    FROM HD_TICKET_CHANGE
    WHERE HD_TICKET_CHANGE.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
    JOIN HD_QUEUE_OWNER_LABEL_JT ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID
    JOIN USER_LABEL_JT ON HD_QUEUE_OWNER_LABEL_JT.LABEL_ID = USER_LABEL_JT.LABEL_ID
    JOIN USER OLIST ON USER_LABEL_JT.USER_ID = OLIST.ID
    /* queue */
    JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
    WHERE
    (C.DESCRIPTION LIKE '%Changed Ticket Queue%') AND OWNER.USER_NAME is null
    /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1


    EMAIL EACH RECIPIENT
    Subject: [TICK:$ticknum] Assigned to $queuename: $title
    Column Containing email address: NEWTICKETEMAIL
    Message:
    $submitter_fname has opened a ticket.

    The submission was:
    Ticket: $ticknum
    From: $submitter_fname ($submitter_email)
    Title: $title
    Priority: $priority
    Status: $status

    History: $comments
    Please see your ticket at https://kace.company.com/adminui/ticket.php?ID=$ticknum


    This rule is working fine. May be my above SQL has C.DESCRIPTION LIKE '%Changed Ticket Queue% due to which it doesn't email notification when a ticket is moved via rule but only sends when a ticket is manually moved?
    • I don't really have an answer for it, I doesn't use such a kind of rule. But what you can do - use the comment function at the custom ticketrule who change the queue id per rule and set a comment in this ticket (maybe: "Queue change by rule") and look for tickets with this comment at the select in the custom ticket rule (in queue "equipment request").

      I have no other good solution at the moment. But I will look for a matching solution for you.
      • OK, is there anyway to update C.DESCRIPTION from Update SQL or if I want a rule to add some values to C.DESCRIPTION such as "Changed ticket Queue from" ?
      • ok, so how do i update "C.DESCRIPTION" OR "HISTORY" of the ticket with this rule to add "Queue change by rule" so I can use it with another rule to send notification?
      • You can add a comment by activate the checkbox "Append comment to ticket" and write the comment in the textfield.
      • I tried that already and it does add the comment, but the script to send notification is only working on "Ticket Save". May be I need an additional 2nd script to run every 15mins?

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share