/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Help With Custom Rule

03/14/2017 807 views

Hello IT Ninjas! Like many people on here I am battling the email loop issue with the K1000 management appliance.  I am not a SQL guy, but we have received a script that is supposed to reset the submitter and CC list to blank if there are more than 15 comments in 15 minutes.  However, when we ran it last night during a loop I got the error shown below. Would be grateful for any help.  

Select SQL:

SELECT HD_TICKET_ID, COUNT(ID) NumberUpdates FROM ORG1.HD_TICKET_CHANGE

WHERE

TIMESTAMP > DATE_SUB(NOW(), INTERVAL 15 MINUTE)

GROUP BY HD_TICKET_ID

HAVING NumberUpdates > 15

ORDER BY COUNT(ID) DESC

 

 

Update SQL:

UPDATE

HD_TICKET

set SUBMITTER_ID = 0,

OWNER_ID = 0,

CC_LIST=''

WHERE ID in ()

 

Error:

03/13/2017 17:38:46> Starting: 03/13/2017 17:38:46 03/13/2017 17:38:46> Executing Select Query... 03/13/2017 17:38:46> selected 1 rows 03/13/2017 17:38:46> Executing Update Query... 03/13/2017 17:38:46> 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 ')' at line 6] in EXECUTE("UPDATE HD_TICKET set SUBMITTER_ID = 0, OWNER_ID = 0, CC_LIST='' WHERE ID in () ") 03/13/2017 17:38:46> Ending: 03/13/2017 17:38:46

0 Comments   [ + ] Show comments

Comments


All Answers

2
this may be because you have not defined the array into which the selected tickets from the select statement are entered in your update statement.

rather than "Where ID in ()" try

 "where 
        (HD_TICKET.ID in (<TICKET_IDS>))"
Answered 03/16/2017 by: Hobbsy
Red Belt

  • Made the suggested update and ran the rule this morning while an email loop was happening. Still getting an error. (Shown Below). For some reason it really seems to have an issue with the TICKET_IDS
  • 03/20/2017 08:25:22> Starting: 03/20/2017 08:25:22 03/20/2017 08:25:22> Executing Select Query... 03/20/2017 08:25:22> selected 1 rows 03/20/2017 08:25:22> Executing Update Query... 03/20/2017 08:25:22> 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 '))' at line 7] in EXECUTE("UPDATE HD_TICKET set SUBMITTER_ID = 0, OWNER_ID = 0, CC_LIST='' where (HD_TICKET.ID in ()) ") 03/20/2017 08:25:22> Ending: 03/20/2017 08:25:22

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