Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Bulk change ticket status

I need to change the ticket status of about 300 tickets, based on the value of a custom form field.  

This is easy to do for new tickets, using filters.  However that only applies to newly created tickets, not existing tickets, and I need to modify the existing ones.

Can I do this via sql somehow, or through any other means of automating it?

Thanks

Comments

  • You do that directly in the database. 

    In the UI you could perform a search for those records, select them all and change their status, but that would not be automated.
  • edited January 13
    >In the UI you could perform a search for those records,

    If only... Unfortunately an advanced-search interface bug prevents me from doing exactly that.  I need to change the status based on a choice made in a custom field.  See https://github.com/osTicket/osTicket/issues/2625

    Anyhow, here's what I ended up doing in SQL, in case someone else runs into this.   

    In my situation, the user has selected a custom-field drop-down where the value is one of "Boy", "Girl", or other options that do not include the words "Boy" or "Girl".  

    Based on the value of that field, I need to set the status to "Waitlist - Boy", "Waitlist - Girl", or "Waitlist - Other".   This is why there are three SQL statements below.

    You'll need to have some SQL knowledge, and figure out the IDs of various elements in the data, like the status IDs, the form IDs, the field IDs, etc.

    OTHER
    update 
    ost_ticket, ost_form_entry, ost_form_entry_values, ost_ticket_status
    set
    ost_ticket.status_id = 14 ## The status ID for "Waitlist - Other" 
    where
    ost_ticket.status_id = 1 AND ## I only want to affect "Open" tickets, you could substitute any status ID here
    ost_ticket.status_id = ost_ticket_status.id AND
    ost_form_entry.form_id=8 AND ## This is the ID of the form that holds my field
    ost_form_entry.object_type = "T" AND ## This ensures the entry is related to a Ticket
    ost_ticket.ticket_id = ost_form_entry.object_id AND
    ost_form_entry_values.entry_id = ost_form_entry.id AND
    ost_form_entry_values.field_id = 41 AND ## I know this is the field that contains "Boy", "Girl", or other stuff
    ost_form_entry_values.value not like "%girl%" AND ## Since I'm targeting the other non-boy non-girl values, I negate these two to get results without boy or girl
    ost_form_entry_values.value not like "%boy%";

    Looks like I can't paste the other SQL commands here, I'm over my character limit on this post.. So.. just adjust the SQL above to filter for different values, and adjust the status ID to assign the correct status based on those values. 

    Hope this helps someone.
Sign In or Register to comment.