/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Help with update statement

12/16/2015 1097 views
Not really good at this sql stuff. But this update statement is moving the tickets to the required queue but in the closed status. any ideas how i can fix this. any help will be appreciated...

update

HD_TICKET T

join HD_CATEGORY CATS 

on T.HD_CATEGORY_ID = CATS.ID 

join HD_STATUS SS 

on T.HD_STATUS_ID = SS.ID 

join HD_PRIORITY PS 

on T.HD_PRIORITY_ID = PS.ID 

join HD_IMPACT IMS 

on T.HD_IMPACT_ID = IMS.ID, HD_QUEUE QD, HD_CATEGORY CATD, HD_STATUS SD, HD_PRIORITY PD, HD_IMPACT IMD 

set T.HD_CATEGORY_ID = CATD.ID, T.HD_STATUS_ID = SD.ID, T.HD_PRIORITY_ID = PD.ID, T.HD_IMPACT_ID = IMD.ID, T.HD_QUEUE_ID = QD.ID where T.ID = <TICKET_IDS> and QD.NAME = 'Sales Service SCS'



Thanks 

4 Comments   [ + ] Show comments

Comments

  • I am confused on how this is handled. the ticket comes in to the Helpdesk (31) queue by email and it has a default value of 1, which is in the state of opened. When the this query runs

    select
    HD_TICKET.ID
    from
    HD_TICKET
    join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID
    and C.ID = <CHANGE_ID>
    where
    HD_TICKET.TITLE rlike ('test')
  • Then the update runs
    update
    HD_TICKET T
    join HD_CATEGORY CATS
    on T.HD_CATEGORY_ID = CATS.ID
    join HD_STATUS SS
    on T.HD_STATUS_ID = SS.ID
    join HD_PRIORITY PS
    on T.HD_PRIORITY_ID = PS.ID
    join HD_IMPACT IMS
    on T.HD_IMPACT_ID = IMS.ID, HD_QUEUE QD, HD_CATEGORY CATD, HD_STATUS SD, HD_PRIORITY PD, HD_IMPACT IMD
    set T.HD_CATEGORY_ID = CATD.ID, T.HD_STATUS_ID = SD.ID, T.HD_PRIORITY_ID = PD.ID, T.HD_IMPACT_ID = IMD.ID, T.HD_QUEUE_ID = QD.ID where T.ID = <TICKET_IDS> and QD.NAME = 'other queue’

    the status is changed to closed. Looking at the other queue (40) default status is set to 150 which is opened. Where is the status of close coming from? The update is just passing the same status from the prior queue…I am so confused. Please help.
    • The status is getting set as T.HD_STATUS_ID = SD.ID but looking at the query I don't see where that value is getting populated. SD is an alias to HD_STATUS. After the rule runs what is the value for HD_TICKET.HD_STATUS_ID for the ticket?
  • is sat to 141 = which is the state of closed for that queue. can I just do this in the statement T.HD_STATUS_ID = '140' , would this work?
  • Jus got this from dell support: "the ticket rule says to update status.id=status.id , those id's are unique to each queue , so if id
    2 in queue 1 is NEW and the id 2 in queue 2 is Opened the ticket rule will do exactly what you told to do set id to id , hope that makes sense."

All Answers

0
Status IDs are unique to each queue, take a look at the HD_STATUS table to check which status values pertain to the new queue and set the ID to the correct status. 
Answered 12/17/2015 by: chucksteel
Red Belt

  • Thanks chucksteel...I am a little worry of making any changes to the statement. I see the status table has 2 status values on for close and one for open. not sure how to enter the correct value...

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