Hi,
Would it be possible to get essentially the same listing as the SCP but from a SQL query. I've tried a few i've found online but they all seem to be for older versions and as i understand it the schema has changed significantly since.

Many Thanks

You would have to either write a very complex SQL quesry (with joins) or write a script to do this.

The base query would be something like:
SELECT * FROM ost_ticket WHERE status_id = '1' AND dept_id = '1'

status_id = 1 is the default Open ticket number. You would want to check your implementation to make sure that it matches mine.

dept_id = 1 is our IT department. We have multiple departments in osTicket, so you could remove this part if you want all departments.

You would use the results to pull the other pieces of information. Like the user_id field you would look the user up in ost_user table to get their name and in the ost_user_email table to get their email. Then use the ticket_id against the object_id and where object_type is equal to "T" from ost_thread to find the [thread] id which you can use against the ost_thread_entry to get title (subject). etc.

4 years later

If this request is of any use to anyone else, here is an SQL command that I use to display the open tickets assigned to a specific team via SQL.
For information, we use the IDs 1 and 6 for the ticket status "open"...

Displays all open tickets assigned to the specified team:

SELECT ost_ticket.ticket_id, ost_ticket.number, ost_ticket.status_id, ost_team.name, ost_staff.username, ost_ticket__cdata.subject FROM ost_ticket JOIN ost_team ON ost_team.team_id = ost_ticket.team_id JOIN ost_staff ON ost_staff.staff_id = ost_ticket.staff_id JOIN ost_ticket__cdata ON ost_ticket__cdata.ticket_id = ost_ticket.ticket_id WHERE ost_ticket.status_id IN (1, 6) AND ost_ticket.team_id = 9;

Output:

Only shows open tickets that are assigned to the specified team and marked as overdue:

SELECT ost_ticket.ticket_id, ost_ticket.number, ost_ticket.status_id, ost_team.name, ost_staff.username, ost_ticket.isoverdue, ost_ticket.est_duedate, ost_ticket__cdata.subject FROM ost_ticket JOIN ost_team ON ost_team.team_id = ost_ticket.team_id JOIN ost_staff ON ost_staff.staff_id = ost_ticket.staff_id LEFT JOIN ost_ticket__cdata ON ost_ticket.ticket_id = ost_ticket__cdata.ticket_id WHERE ost_ticket.status_id IN (1, 6) AND ost_ticket.isoverdue = 1 AND ost_ticket.team_id = 9;

Output:

Greetings,
Davokin

Write a Reply...