After a lot of trial and error, I got the system to email the technician assigned to a particular area when the user selects their building.  The last thing I want to do is insert a link to the ticket directly into the email so the technician just has to click the link to see what the ticket is.  I know it has something to do with the Change_field table, but I'm not sure now to go about adding this to my rule.  Here is what I found on a KB article.

 -- 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://kaceassets.sboe.org/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history

This is my ticket rule:

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,
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE, -- $title
      HD_TICKET.SUMMARY, -- $summary
      HD_CATEGORY.NAME as CATEGORY,
      HD_PRIORITY.NAME as PRIORITY,
      HD_STATUS.NAME as STATUS,

                        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,
'me@me.net' as TECHNICIAN_EMAIL,

                        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_TICKET.CUSTOM_FIELD_VALUE0 like '%Arleth%') OR HD_TICKET.CUSTOM_FIELD_VALUE0 like '%Eisenhower%') OR HD_TICKET.CUSTOM_FIELD_VALUE0 like '%Truman%') OR HD_TICKET.CUSTOM_FIELD_VALUE0 like '%Wilson%') and HD_TICKET.HD_QUEUE_ID = 40 )

This is my email query that goes to the technician:

NEW [$ticknum] $title                             TECHNICIAN_EMAIL

$submitter_fullname has opened a ticket. 

The submission was:
Ticket: $ticknum
From: $submitter_email
Category: $category
Priority: $priority
Status: $status
Title: $title
Summary: $summary

Please see your ticket at
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
You just need the ticket ID and the URL to your server. The ticket ID is available in the $id variable:
https://kace.company.com/adminui/ticket.php?ID=$id

Answered 04/01/2015 by: chucksteel
Red Belt

Please log in to comment
0
I'm working on a similar issue. I assume the KB article you mention is 111222. There is a line in that code which I think should create a link to the ticket. I'm testing it now, but since I'm very much winging it with the rest of my SQL code, if it fails for me, that doesn't really say much.

GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket athttp://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')

       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history


Based on the rest of the code shown, I would assume that this creates a variable $history which can be inserted in the email. The example does not use this. Having unexplained additions that don't seem to be used make interpreting and understanding these examples very difficult for the novice, but that's another story.


My basic question is what determines which values returned by the query will be available as variables in the email. Your own code appears to select  HD_TICKET.SUMMARY. My SQL syntax aware text editor displays the "-- $summary" as a comment. So what is the trick that makes this available as $Summary in your email example.


More to the point, inserting the same line in my own very similar query does not seem to work. Only $ticknum and $title (which appears in the email subject) work for me. The email I get shows the variable names, so I get:


"A new ticket has opened by: $SUBMITTER_FULLNAME Thesubmission was from: $SUBMITTER_EMAIL

Ticket: 359

Opening Comment: $SUMMARY"


Answered 04/01/2015 by: MichaelMc
Orange Belt

  • In order to generate the history you need to include the line you have and also join to the HD_TICKET_CHANGE table. These two joins get the latest change and all changes respectively:
    /* 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

    Variables are generated based on what columns are selected in the SELECT portion of the MySQL query. If your query includes HD_TICKET.* (any queries generated by the wizard should) then the standard fields in the ticket should all get variables, including the summary. I'm still running 6.2 which doesn't include the ticket summary field so I'm not sure if it is called that in the table or not. Also, I believe variables need to be in lower case, have you tried $submitter_fullname?
    • Thanks! This seems to be working now that the variables are in lower case. I think I had tried them in lower case at some other time, and must have had something else fail at that point. Do you know of a document which discusses KACE SQL details such as this? I also spent a very long time searching these forums for what the <CHANGE_ID> syntax meant, before I found it in an offhand comment in the middle of another thread.

      As for the join, that is indeed how it is written in the linked KB article. A newly opened ticket doesn't have much of a history, so $summary is plenty.
      • I recommend installing MySQL Workbench and connecting to the database using the reporting user to look at the tables. Dell has not published a database schema for recent versions but once you look at the tables it usually isn't difficult to figure out how things work and how tables are related.
      • This content is currently hidden from public view.
        Reason: Spam
        For more information, visit our FAQ's.
Please log in to comment
Answer this question or Comment on this question for clarity