auto close resolved tickets after 3 days

Comments

• Chefkeks

  June 2015

  You'd need to make a script that checks the ticket status and the age of a ticket which then runs via scheduled tasks to do this. And no, there is currently no solution built-in / shipped with osTicket to do so.
• EOHSupport

  June 2015

  Would you know what would be the SQL to do this direct on the Database ?

  
  

  
  
• Chefkeks

  June 2015

  No, but my approach would be to look at the ticket closing function, export it to a script and then code another query for the status / 3 days time span.
  
  I know that's not the answer you hoped for, but that's all I can advise.
• EOHSupport

  June 2015

  have been playing.

  
  

  Update MY_ticket.ost_ticket set status_id = 3 where status_id = 2 and DATEDIFF(now(), updated ) > 3; 

  
  

  Just need to put this is a script now and schedule it.

  
  
• EOHSupport

  June 2015 edited June 2015

  I think this SQL is better:

  
  

  *** change ost_DB to the ost database name.

  
  

  use ost_DB;

  update ost_DB.ost_ticket 

  inner join ost_ticket_status on ost_ticket.status_id = ost_ticket_status.id 

  set ost_ticket.status_id = 

  (select ost_ticket_status.id from ost_ticket_status where ost_ticket_status.name like 'closed')

  where ost_ticket_status.name like 'resolved' 

  and DATEDIFF(now(), ost_ticket.updated ) > 3; 

  
  

  I am going to schedule this with windows task scheduler. the command to schedule is:

  mysql -hlocalhost -uthe_userid -pthe_password < The_sql_File.SQL > the_output_File.txt
  

  
  

  I am thinking it would be good to have a feature in OST that allows you run SQL command on a schedule.

  
  

  If anyone else uses this I'd appreciate feed back.
• Chefkeks

  June 2015

  Looks really good! Won't need it myself, but thank you for sharing! :)
• EOHSupport

  June 2015

  You're welcome