/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Custom Ticket Rule - email alert on ticket w/o owner (hourly)

05/05/2017 846 views
So, unfortunately, we don't have any SQL gurus on staff, and I went through the wizard, which effectively does what I want, but it returns way too much data to make the alert useable.  I need an email to be sent to the help desk manager when a new ticket is created and hasn't been assigned an owner (after a 1 hour grace period).  The code that KACE created for me works very well, EXCEPT it give me way too many variables.  ALl I need is the ticket ID/# and the title (and the submitter would be nice).  Here's the code it gives me (with 61 variables per ticket):

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(HD_TICKET.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 - Not Started') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 5 )

Any help is greatly appreciated, thanks!
Alex
0 Comments   [ + ] Show comments

Comments


All Answers

1
Hello Alex,

have you set the checkbox "Send an e-mail to each recipient in query results." ?
In there - you can configure the information you want to have to - with variables.

Answered 05/08/2017 by: svmay
Red Belt

1
It sounds like you are using the Email Results option, which isn't exactly what you want in this case. You should be using the Email each recipient in query results option as smvay says. In order to use that option you will need to make a small modification to the select query. After this line:
Q.NAME as QUEUE_NAME add the following:
, "helpdeskmanager@company.com" as HELPDESKMANAGER

Use the actual email address for the helpdesk manager (and don't forget the comma). 

Now you can use HELPDESKMANAGER as the value for Column Containing email addresses in the option for sending the email. Also, all of the columns selected will be available as variables for the email subject and message as $variable. The email you are currently receiving should show you those columns.

Answered 05/08/2017 by: chucksteel
Red Belt

  • I added in the new line, and edited the email address - this is working properly (in as much as I am getting emails). However, for one ticket without an owner, I received 4 email alerts.

    Also, can you give a bit more clarity about the variable names I can use? I tried to use $HD_TICKET and $SUBMITTER_NAME but those came through as text, not as the values.
    • The variables need to be in lower case, that should fix $submitter_email. There isn't a column called HD_TICKET, that's a table name. If you want ID that would be $id.

      Any columns selected in the statement will be made available as variables. If you look through the statement most of the lines end with AS SOMETHING, that SOMETHING will be $something in the email.
      • Fantastic - very helpful, thank you. So, it's working as designed, but what I was hoping for was a single email with all results, not an individual email for each result. Any chance that's possible?
      • Two options:
        Create a report and have the report scheduled to run on a schedule.

        Use the rule but change it back to the Email Results option and reduce the columns being selected in the statement.
      • Can't reply to your latest response for some reason - using Email Results is the way I started (and prefer) but both you and svmay directed me to this format instead. If I wanted to go back to the single email - what portion of the SQL code should I modify to only pull three variables (ticket number, submitter and ticket title)? Thanks so much!
      • Replies can only go three levels on ITNinja.

        If you just want those three columns then this is your select statement:
        select HD_TICKET.ID as "Ticket Number",
        HD_TICKET.TITLE,
        U2.FULL_NAME as SUBMITTER_FULLNAME
        from HD_TICKET
        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
        WHERE ((( HD_STATUS.NAME = 'New - Not Started') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 5 )
      • I get the following error:

        05/08/2017 11:15:01> Starting: 05/08/2017 11:15:01 05/08/2017 11:15:01> Executing Select Query... 05/08/2017 11:15:01> mysqli error: [1054: Unknown column 'HD_STATUS.NAME' in 'where clause'] in EXECUTE("select HD_TICKET.ID as "Ticket Number", HD_TICKET.TITLE, U2.FULL_NAME as SUBMITTER_FULLNAME from HD_TICKET LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID WHERE ((( HD_STATUS.NAME = 'New - Not Started') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 5 )")
      • D'oh. Forgot about the status.

        select HD_TICKET.ID as "Ticket Number",
        HD_TICKET.TITLE,
        U2.FULL_NAME as SUBMITTER_FULLNAME
        from (HD_TICKET, HD_STATUS)
        LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
        WHERE ((( HD_STATUS.NAME = 'New - Not Started') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 5 )
        and HD_STATUS.ID = HD_STATUS_ID
      • And now, this error:

        05/08/2017 11:22:43> Starting: 05/08/2017 11:22:43 05/08/2017 11:22:43> Executing Select Query... 05/08/2017 11:22:43> mysqli error: [1054: Unknown column 'HD_STATUS.NAME' in 'where clause'] in EXECUTE("select HD_TICKET.ID as "Ticket Number", HD_TICKET.TITLE, U2.FULL_NAME as SUBMITTER_FULLNAME from HD_TICKET LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID WHERE ((( HD_STATUS.NAME = 'New - Not Started') AND HD_TICKET.OWNER_ID = '0') and HD_TICKET.HD_QUEUE_ID = 5 )")
      • You didn't change the select statement, it's the same error.
      • arg, bummer - I changed the update SQL statement - and so now I need to start all over again (or can I leave the update statement blank as we aren't changing anything?
      • Yes, you can uncheck the box to update the ticket.
      • Thank you very much! This works exactly as I was hoping now. Really appreciate the assist!

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

 
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