PostreSQL query for Zammad

  • What Grafana version and what operating system are you using?
    grafana 10.3.3, ubuntu
    zammad 6.2

  • What are you trying to achieve?
    Add a panel to track time

  • How are you trying to achieve it?
    Via postgreSQL database

  • What happened?
    I’m weak.
    I tried to modify the code (picture as below) but I wanted it to give me the time for each employee.

As I understand it, there are several variables for this in the database.

public.ticket_time_accountings.time_unit = amount of time spent
public.ticket_time_accounting_types.name = employee name
public.ticket_time_accounting_types.id = employee ID

I came up with this code, but it gives me a lot of rows at once, and I would like them to be grouped by employees and linked to the time spent.
like this

SELECT 
  public.ticket_time_accountings.time_unit,
  public.ticket_time_accounting_types.name,
  public.ticket_time_accountings.time_unit AS metric
FROM
  public.ticket_time_accountings,
  public.ticket_time_accounting_types
  • What did you expect to happen?
    I learned and did it all myself, or someone helped me do it -)

  • Can you copy/paste the configuration(s) that you are having problems with?

SELECT
  min(tickets.created_at) AS "time",
  users.firstname as metric,
  count(*) as total /*,
  count(case when state_id=4 then 1 ELSE 0 END) as closed*/
FROM tickets
inner join users on (tickets.owner_id=users.id)
WHERE
$__timeFilter(tickets.created_at) and tickets.state_id in (1,2)
group by users.id
ORDER BY 1

I need something like this just for the time spent.