I would like to create a ticket rule that alerts our IT manager that a particular user has submitted a ticket. I've had a brief look and have managed to create a rule that emails our IT manager of all the tickets submitted by a particular user but not when an individual ticket is created. I don't have any experience of SQL so wondered if anybody would be able to help please. Thanks

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2

That rule should be pretty easily created using the rules wizard. You should be able to match submitter and then just have it send an email to a particular email address. Here's a good place to look for additional information:

http://www.kace.com/support/resources/kb/article/How-To-Notify-Any-User-or-Group-by-Email-When-a-New-Help-Desk

Feel free to post your SQL code that you come up with if you're getting close but can't quite get things the way you want them to work.

Answered 07/09/2012 by: chucksteel
Red Belt

  • This content is currently hidden from public view.
    Reason: Removed by user request
    For more information, visit our FAQ's.
  • Thanks for the quick reply Chucksteel. I've managed to create a query which looks up a particular user but I'm having problems getting it to send an email when the user submits a ticket. If you could have a look at look at what I’ve done and see where I’m going wrong that be great. Thanks again for your help
Please log in to comment

Answers

1

Thanks for the quick reply Chucksteel. I've managed to create a query which looks up a particular user but I'm having problems getting it to send an email when the user submits a ticket. If you could have a look at look at what I’ve done and see where I’m going wrong that be great. Thanks again for your help

 

What I've currently got is....

 

Frequency: On Ticket Save

 

Enabled: ticked

 

Query:

 

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

                        lnb@mewburn.com as newticketemail --$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 ((  (1  in (select 1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID and USER.FULL_NAME like '%nigel hackney%')) ) and HD_TICKET.HD_QUEUE_ID = 1 )

 

Send an email for each row: Ticked

 

Subject: [TICK:$ticknum] NEW TICKET: $title

Email Column: NEWTICKETEMAIL

Email Body: $submitter_fname has opened a ticket.  Please click here to reply to this email

<mailto:kboxhelpdesk@yourcompany.com?subject=[TICK:$ticknum]> or review it online at

 

kbox.yourcompany.com/adminui/ticket?ID=$ticknum

 

 The submission was:

 Ticket: $ticknum

 From: $submitter_fname ($submitter_email)

 Category: $category

 Priority: $priority

 Status: $status

 Severity: $impact

 

Opening Comment: $comment

 

Answered 07/10/2012 by: mewburn
Senior Yellow Belt

  • First, add the following to the join statements:

    JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
    and HD_TICKET_CHANGE.ID= < CHANGE_ID >
    This will allow you to make sure the rule only matches when the ticket is created, otherwise it will match every time the ticket it saved. Make sure this statement is below the from line and above the where line.

    Then I would replace:

    and (( (1 in (select 1 from USER where HD_TICKET.SUBMITTER_ID = USER.ID and USER.FULL_NAME like '%nigel hackney%')) ) and HD_TICKET.HD_QUEUE_ID = 1 )

    with the following:

    and U2.EMAIL = < nigel's email address >
    and HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'
    and HD_TICKET.HD_QUEUE_ID = 1

    The main issue I see with the code you posted is that your where statements are looking at the USER table in general and might not be matching this specific ticket. I would also match on the user's email address to make sure it is a good match. If you look in the join statements U2 is the submitter, so we need to match U2's email address (or name if you like).
  • Sorry, I had some formatting problems because my SQL code includes left and right brackets which were trying to be rendered as HTML codes. The <nigel's email address> should be replaced by Nigel's email address, if that isn't obvious.
Please log in to comment
Answer this question or Comment on this question for clarity