It's not recommended to have an external writable interface with your database, but if you create a user that only has "SELECT" permissions to the database, the following might be usable:

(Nonetheless, you may paste this in the SQL Box interface of OTRS and get a result.)

select queue.name as "Queue", ticket_state.name as "Ticket State", count(ticket.id) as "count" from ticket left join ticket_state on ticket.ticket_state_id=ticket_state.id left join queue on ticket.queue_id=queue.id where ticket_state.name in ("new", "open") group by queue.name, ticket_state.name

Note that any external access to the database bypasses the front security provided by the OTRS interface and potentially compromises the confidentiality of the contents of the database. This is why GenericInterface and TicketSearch is recommended, as it is secured through the application.

Don't forget that there are reports/statistics available in OTRS that can pull this information via bin/otrs.Console.pl Mint::Stats::Generate --number xxxxx

See: https://doc.otrs.com/doc/manual/admin/5.0/en/html/statistics-module.html

On Fri, Sep 28, 2018 at 10:17 AM Roy Kaldung <kaldung@gmail.com> wrote:

Hi Ralf,

> On Sep 28, 2018, at 3:06 PM, Ralf Hildebrandt <Ralf.Hildebrandt@charite.de> wrote:
>
> We#d like to programmatically query the number new or open tickets in
> our OTRS 5 installation. How would we go about doing that?

I would recommend the GenericInterface and the TicketSearch operation.

- Roy

---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/mailman/listinfo/otrs