/build/static/layout/Breadcrumb_cap_w.png

KACE 1000 v7 Create a query that notifies technical group when ticket is reassigned

I am trying to figure out a query to email techs when a ticket get reassigned to that technical group. I am having issue trying to figure this out. here is what I have.
   
SELECT
HD_TICKET.ID as TICKNUM, -- $id
      HD_TICKET.TITLE as TITLE, -- $title
      SUBMITTER.FULL_NAME as SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL as SUBMITTER_EMAIL,     -- $submitter_email
CAT.NAME as CATEGORY, -- $category
P.NAME as PRIORITY, -- $priority
S.NAME as STATUS,   -- $status
I.NAME as IMPACT,   -- $impact
HD_TICKET_CHANGE.COMMENT as COMMENTS, -- $comments
HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION as DESCRIPTIONS, -- $description
'USERS EMAIL ADDRESS HERE' as NEWTICKETEMAIL
FROM ORG1.HD_TICKET
LEFT JOIN HD_TICKET_CHANGE ON HD_TICKET.ID=HD_TICKET_CHANGE.HD_TICKET_ID
join HD_IMPACT I on I.ID=HD_IMPACT_ID
join HD_STATUS S on S.ID=HD_STATUS_ID
left join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
join HD_PRIORITY P on P.ID=HD_PRIORITY_ID
join HD_CATEGORY CAT on CAT.ID=HD_CATEGORY_ID
where HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION LIKE '%Changed Technical Group from "%" to "Desktop Team".%'
and CUSTOM_FIELD_VALUE7 = "Desktop Team"
and HD_TICKET_CHANGE.ID=<CHANGE_ID>

I am getting an error when I run Ticket search result below:

There were syntax errors in your query.

mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= and HD_TICKET.HD_QUEUE_ID = '1'' at line 22] in EXECUTE(" SELECT HD_TICKET.ID as TICKNUM, -- $id HD_TICKET.TITLE as TITLE, -- $title SUBMITTER.FULL_NAME as SUBMITTER_FNAME, -- $submitter_fname SUBMITTER.EMAIL as SUBMITTER_EMAIL, -- $submitter_email CAT.NAME as CATEGORY, -- $category P.NAME as PRIORITY, -- $priority S.NAME as STATUS, -- $status I.NAME as IMPACT, -- $impact HD_TICKET_CHANGE.COMMENT as COMMENTS, -- $comments HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION as DESCRIPTIONS, -- $description 'Emaill Address here' as NEWTICKETEMAIL FROM ORG1.HD_TICKET LEFT JOIN HD_TICKET_CHANGE ON HD_TICKET.ID=HD_TICKET_CHANGE.HD_TICKET_ID join HD_IMPACT I on I.ID=HD_IMPACT_ID join HD_STATUS S on S.ID=HD_STATUS_ID left join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID join HD_PRIORITY P on P.ID=HD_PRIORITY_ID join HD_CATEGORY CAT on CAT.ID=HD_CATEGORY_ID where HD_TICKET_CHANGE.OWNERS_ONLY_DESCRIPTION LIKE '%Changed Technical Group from "%" to "Desktop Team".%' and CUSTOM_FIELD_VALUE7 = "Desktop Team" and HD_TICKET_CHANGE.ID= and HD_TICKET.HD_QUEUE_ID = '1' ")

I have no knowledge of query language so any help would be appreciated


0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 7 years ago
Red Belt
0
The ticket search will fail due to the presence of the <CHANGE_ID> variable. That will be replaced at runtime with the ID of the change when the ticket is saved.

Comments:
  • so what value should I place there. ID ? - anonymous_136266 7 years ago
    • Personally I don't use the test function. I have a queue setup where I can test rules. Put the rule in place and make a ticket that should trigger it. - chucksteel 7 years ago
    • simply omit it to see all changes which qualify, which will help you make sure you're getting the correct info. Depending on the size of your service desk, you may want to include a "LIMIT" statement at the end of the query so that the query doesn't take forever to run. - JasonEgg 7 years ago

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