0

Can someone help me figure out how I can retrieve all tickets? I read online and saw that there's no API to do this yet? I also read that i can write some sql code to retrieve them?

My objective is: Check OSticket to see if the ticket with the same subject is created more than 3 times, then to basically alert me ( for now it can just be a message in Powershell that says it, as I'm scripting in PS).

For that I need to retrieve all tickets in the OSticketDB. Since I just have it locally for now, I have a sql DB setup but I don't see something along the lines of ost_tickets? Not sure how I can retrieve tickets that have been duplicates from same subject.

enter image description here

Miroslav Adamec
  • 1,060
  • 1
  • 15
  • 23
encrypt
  • 267
  • 1
  • 3
  • 13

1 Answers1

0

I'm not sure I understand your question correctly. But here is SQL query, that will return all tickets, where subject has occurred more than 3 times.

SELECT 
    cdata.ticket_id,
    cdata.subject,
    ticket.number,
    subjectstable.subjectcount
FROM
    osticketdb.ost_ticket AS ticket
        INNER JOIN osticketdb.ost_ticket__cdata AS cdata ON ticket.ticket_id = cdata.ticket_id
        INNER JOIN 
            (SELECT subject, COUNT(*) as subjectcount FROM osticketdb.ost_ticket__cdata GROUP BY subject) AS subjectstable
            ON subjectstable.subject = cdata.subject
WHERE subjectstable.subjectcount > 3
Miroslav Adamec
  • 1,060
  • 1
  • 15
  • 23