/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Kace K1000 V7.0 - No New Emails Generated When New Ticket Created. Require New SQL Code

02/09/2017 954 views
Good Day Everyone,

  In the previous of Kace 6.3 and below - a custom rule I had in place, with SQL code,  generated an email when a new ticket was generated by a user. Since upgrading to version 7 - it appears new tables were either renamed, etc. Thus my email rule is now broken. Shown below is the previous SQL code. Can someone please help with new SQL code to reflect the changes in version 7?

Basically members in my organization who own certain Kace queues are not being alerted when new Kace tickets are being created. This is forcing queue owners to check the web interface. 

Thanks
Steve

Previous SQL Code - version 6.3 and below
select HD_TICKET.ID, 
HD_TICKET.ID as TICKNUM, 
'networkadministrators@hinda.com' as EMAILCC,
HD_TICKET.TITLE, 
U1.USER_NAME as OWNER_NAME, 
U3.USER_NAME as LASTINPUTNAME,  
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, 
STATE, 
U1.FULL_NAME as OWNER_FULLNAME, 
U1.EMAIL as OWNER_EMAIL, 
U2.USER_NAME as SUBMITTER_NAME, 
U2.FULL_NAME as SUBMITTER_FULLNAME, 
U2.EMAIL as SUBMITTER_EMAIL, 
U3.EMAIL as UPDATEREMAIL, 
U3.FULL_NAME as UPDATERNAME,
UNIX_TIMESTAMP(TICKETCHANGE.TIMESTAMP),
TICKETCHANGE.COMMENT as COMMENT,
TICKETINITIAL.COMMENT as INITIAL_COMMENT,
TICKETCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,
HD_CATEGORY.CC_LIST AS CATEGORYCC,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.LOCATION AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID and TICKETCHANGE.ID=
JOIN HD_TICKET_CHANGE TICKETINITIAL ON TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID
and TICKETINITIAL.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
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 = TICKETCHANGE.USER_ID 
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_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
TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and
HD_TICKET.HD_QUEUE_ID = 1 and
HD_STATUS.NAME != 'Closed'

 


 
0 Comments   [ + ] Show comments

Comments


All Answers

1
With the 7.0 upgrade the user's location is no longer stored in the USER table so you will need to make a few changes to any rules that reference that column.
First thing, add a join to the ASSET table in order to get locations:
left JOIN ASSET SUBMITTER_LOCATION on SUBMITTER_LOCATION.ID = U2.LOCATION_ID
This line goes with the other join statements.

Next, select the location name from the SUBMITTER_LOCATION table:
SUBMITTER_LOCATION.NAME AS SUBMITTER_LOCATION
This line replaces the current U2.LOCATION AS SUBMITTER_LOCATION


Answered 02/10/2017 by: chucksteel
Red Belt

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