ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    osTicket: Basic Ticket Report of All Tickets with Subject

    IT Discussion
    osticket osticket 1.10 mysql sql mariadb report query
    4
    7
    3.0k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • scottalanmillerS
      scottalanmiller
      last edited by

      There are many ways to make a report, this is a simple query that will allow you to view all tickets, or easily output to CSV to open in LibreOffice Calc or Excel and manipulate however you like. This is a generic, general purpose query. Not that ost_ is a default table prefix, yours easily will vary. Adjust accordingly.

      SELECT DISTINCT ost_ticket.number AS Ticket_Number,
        ost_user.name AS Client,
        ost_staff.username AS Staff,
        ost_ticket.source AS Source,
        ost_ticket.created AS Date_Created,
        ost_ticket.closed AS Closed_Date,
        ost_ticket_status.name AS Status,
        ost_help_topic.topic AS Category,
        ost_ticket__cdata.subject AS Subject
      FROM ost_ticket
        JOIN ost_staff ON ost_staff.staff_id = ost_ticket.staff_id
        JOIN ost_user_email ON ost_ticket.user_id = ost_user_email.user_id
        JOIN ost_user ON ost_user_email.id = ost_user.id
        JOIN ost_ticket_status ON ost_ticket.status_id = ost_ticket_status.id
        JOIN ost_ticket__cdata ON ost_ticket.ticket_id = ost_ticket__cdata.ticket_id
      INNER JOIN ost_help_topic ON ost_ticket.topic_id = ost_help_topic.topic_id;
      
      1 Reply Last reply Reply Quote 3
      • stacksofplatesS
        stacksofplates
        last edited by

        I'm thinking of pairing Grafana with it to have usable reports.

        1 Reply Last reply Reply Quote 3
        • dafyreD
          dafyre
          last edited by

          Or an open source reporting tool... I used to know a couple, but my brain is failing me at the moment.

          1 Reply Last reply Reply Quote 0
          • scottalanmillerS
            scottalanmiller
            last edited by

            Jasper?

            dafyreD 1 Reply Last reply Reply Quote 0
            • dafyreD
              dafyre @scottalanmiller
              last edited by

              @scottalanmiller said in osTicket: Basic Ticket Report of All Tickets with Subject:

              Jasper?

              That's one of them, yeah.

              1 Reply Last reply Reply Quote 0
              • bbigfordB
                bbigford
                last edited by

                Since there is no inner or outter join specified, inner join is obviously being used where 'join' is specified (outter join having to be explicitly defined where an outter join is needed). How come you explicitly specify 'inner join' at the bottom when you could just use 'join' again as your inner join?

                Obviously join and inner join accomplish the same thing, just curious why one is not explicitly defined and the other is.

                scottalanmillerS 1 Reply Last reply Reply Quote 0
                • scottalanmillerS
                  scottalanmiller @bbigford
                  last edited by

                  @bbigford said in osTicket: Basic Ticket Report of All Tickets with Subject:

                  Since there is no inner or outter join specified, inner join is obviously being used where 'join' is specified (outter join having to be explicitly defined where an outter join is needed). How come you explicitly specify 'inner join' at the bottom when you could just use 'join' again as your inner join?

                  Obviously join and inner join accomplish the same thing, just curious why one is not explicitly defined and the other is.

                  I expanded on something done by someone from the osTicket team. it was some weird stylistic choice that they made and I just followed.

                  1 Reply Last reply Reply Quote 1
                  • 1 / 1
                  • First post
                    Last post