Использование режима Запрос SQL

[paver]

Режим "Запрос SQL" административного интерфейса в ряде случаев позволяет оперативно получить достаточно специфическую информацию из БД OTRS.
Для его использования требуются минимальные знания SQL, структура данных приведена ТУТ.

Режим позволяет за один раз выполнить строго один запрос типа SELECT.
При генерации запроса система автоматически подставит в него параметр LIMIT с заданным ниже формы ввода запроса количеством выводимых строк и завершит оператор символом ";"
(использование собственных LIMIT или ";" в запросе приведет к ошибке).

Результат можно просмотреть сразу после выполнения запроса в HTML-формате либо вывести в виде таблицы (CSV, Excel).

Ниже приведены некоторые примеры использования режима. Писались чайником, поэтому на оптимальность не претендуют. Проверялись в основном на OTRS 4 версии, СУБД - MySQL.
Обсуждать данный топик лучше ТУТ.

[paver]

Число открытых и закрытых тикетов по дням (за последние 31 день)

Код: Выделить всё

SELECT date,
  SUM(history_type_id = 1) AS created,
  SUM(history_type_id IN (1, 27) AND state_id IN (2, 3)) AS closed
FROM (
  SELECT DISTINCT history_type_id, ticket_id, state_id, LEFT(create_time, 10) AS date
  FROM ticket_history
  WHERE history_type_id IN (1, 27) AND create_time > CURRENT_DATE - INTERVAL 30 DAY
  ) AS a
GROUP BY date

[paver]

Активность агентов (по добавленным артиклям за последние 31 день)

Код: Выделить всё

SELECT concat(users.last_name, ' ', users.first_name) as agent_fullname, count(article.create_by) as articles
FROM article LEFT JOIN users ON article.create_by = users.id
WHERE article.create_time > CURRENT_DATE - INTERVAL 30 DAY
GROUP BY article.create_by

[paver]

Активность клиентов (по числу заявок, выводятся создавшие более 5 заявок)

Код: Выделить всё

SELECT customer_user_id, count(customer_user_id) as count, queue_id
FROM ticket
WHERE create_time > "2016-01-01"
GROUP BY customer_user_id
HAVING count > 5
ORDER BY count DESC

[paver]

Тикеты с числом артиклей больше заданного (типа, народ вместо отработки заявки занимается отписками и перепихиванием тикета друг другу).
Чтобы можно было быстро перейти на заявку из списка, в выводе SELECT используется конструкция типа CONCAT('https://<адрес вашего OTRS>/otrs/index.pl?Action=AgentTicketZoom;TicketID=',h1.ticket_id) AS link. Не забудьте исправить.

Код: Выделить всё

SELECT create_by, create_time, count(ticket_id) as articles, concat('https://<адрес вашего OTRS>/otrs/index.pl?Action=AgentTicketZoom;TicketID=',ticket_id) as link
FROM article
WHERE create_time > "2016-01-01"
GROUP BY ticket_id
HAVING articles > 5

[paver]

Заявки, у которых после закрытия менялся статус (переоткрывали)

Код: Выделить всё

SELECT DISTINCT h1.ticket_id,
  CONCAT('https://<адрес вашего OTRS>/otrs/index.pl?Action=AgentTicketZoom;TicketID=',h1.ticket_id) link
FROM ticket_history h1
LEFT JOIN ticket_history h2 USING (ticket_id)
WHERE h1.history_type_id IN (1, 27) AND h1.state_id IN (2, 3)
  AND h2.history_type_id = 27 AND h2.state_id NOT IN (2, 3)
  AND h2.create_time > h1.create_time

[paver]

Число открытых и закрытых тикетов в разрезе агентов (за последние 31 день)

Код: Выделить всё

SELECT concat(u.last_name, ' ', u.first_name) as agent_fullname,
  SUM(h.history_type_id = 1) AS created,
  SUM(h.history_type_id IN (1, 27) AND h.state_id IN (2, 3)) AS closed
FROM ticket_history h
LEFT JOIN users u ON h.create_by = u.id
WHERE h.history_type_id IN (1, 27) AND h.create_time > CURRENT_DATE - INTERVAL 30 DAY
GROUP BY h.create_by

Или то же самое с итогами

Код: Выделить всё

SELECT IFNULL(z.agent_fullname,"Total") FIO, z.created, z.closed, z.created + z.closed Total
FROM (
SELECT concat(u.last_name, ' ', u.first_name) as agent_fullname,
  SUM(h.history_type_id = 1) AS created,
  SUM(h.history_type_id IN (1, 27) AND h.state_id IN (2, 3)) AS closed
FROM ticket_history h
LEFT JOIN users u ON h.create_by = u.id
WHERE h.history_type_id IN (1, 27) AND h.create_time > CURRENT_DATE - INTERVAL 31 DAY
GROUP BY agent_fullname WITH ROLLUP
) z

[alexus]

# ----
# ----
# query to retrieve all affected tickets for agent in this queue - все тикеты, где агент поучаствовал в определённых очередях за определённый период
# ----
SELECT u.first_name, u.last_name, COUNT(a.ticket_id) FROM users u LEFT JOIN (SELECT DISTINCT t.user_id, th.ticket_id, max(th.id) FROM ticket_history th, ticket t
WHERE th.ticket_id = t.id
AND th.queue_id IN (3, 7)
AND th.create_time > '2012-01-01'
AND th.create_time < '2013-10-10'
AND th.owner_id != 1
GROUP BY t.user_id, th.ticket_id) a
ON u.id = a.user_id
WHERE u.id IN (SELECT ug.user_id FROM group_user ug WHERE ug.permission_key = 'rw' AND ug.permission_value = 1 AND ug.group_id IN (SELECT queue.group_id FROM queue WHERE queue.id IN (16)))
AND u.id != 1
GROUP BY u.first_name, u.last_name

[paver]

Заявки, закрытые в процессе добавления (при добавлении заявки сразу был установлен статус Закрыта успешно/неуспешно).

Код: Выделить всё

SELECT concat(u.last_name, ' ', u.first_name) as agent_fullname, h.create_time, concat('https://<адрес вашего OTRS>/otrs/index.pl?Action=AgentTicketZoom;TicketID=',h.ticket_id) as link
FROM ticket_history h
LEFT JOIN users u ON h.create_by = u.id
WHERE h.history_type_id = 1 AND h.state_id IN (2, 3)

В WHERE при необходимости можно добавить дополнительные ограничения, например, конкретные очереди или период создания заявки.

[damisha]

Все добавленные заметки по заявкам, для типов заявок ServiceRequest за последние 12 часов.

Код: Выделить всё

select ticket.tn, article.a_body from article,ticket,ticket_type where article.ticket_id=ticket.id and ticket.type_id=ticket_type.id and (UNIX_TIMESTAMP(now())-ticket.create_time_unix<43200) and article.ticket_id in 
	(	
	select id from ticket where type_id=
		( 
		select id from ticket_type where name="ServiceRequest"
		)
	)

Меняя параметр name="ServiceRequest" - меняем тип заявки, 43200 - диапазон (в секундах)

[paver]

Все добавленные заметки по заявкам, для типов заявок ServiceRequest за последние 12 часов.

Код: Выделить всё

select
	select
		select

УЖОС

Код: Выделить всё

SELECT t.tn, a.a_subject, a.a_body, a.create_time
FROM ticket t
JOIN article a ON t.id = a.ticket_id
JOIN ticket_type tt ON t.type_id = tt.id
WHERE t.create_time > NOW() - INTERVAL 12 HOUR
  AND tt.name="ServiceRequest"

[alex.t]

На выходе будет Отчет содержащий Номер тикета, Тему тикета, Приоритет, Время создания, Время решения (у меня этот ID=16, получите время именно последнего статуса), Sla, ID Состояния, Ссылка на Тикет.

Код: Выделить всё

SELECT
	distinct
	t.tn as Номер_тикета, 
	t.title as Тема_обращения,
        'Приоритет - '||th.priority_id as Приоритет,
        t.create_time as Время_создания,
        th.create_time as Время_выполнения,
        sla.name as Sla,
	'http://<Адрес вашей ОТРС>/otrs/index.pl?Action=AgentTicketZoom;TicketID='||t.id as Ссылка_на_тикет
FROM ticket_history th
join ticket t on t.id=th.ticket_id
LEFT JOIN ticket_history_type tht ON (th.history_type_id = tht.id)
LEFT JOIN users us ON (th.create_by = us.id)
LEFT JOIN queue qu ON (th.queue_id = qu.id)
LEFT JOIN ticket_state ts ON (th.state_id = ts.id) 
LEFT JOIN sla ON (t.sla_id = sla.id)
LEFT JOIN ticket_state ON (t.ticket_state_id = ts.id) 
LEFT JOIN ticket_history h2 USING (ticket_id)
WHERE
th.create_time >= '2018-03-01 00:00:01' /* дата С */
AND th.create_time <= '2018-05-25 23:59:59' /* дата По */
and tht.name='StateUpdate'
and th.state_id in () /* в скобках указать, ID статусов */
and qu.id in () /* в скобках указать ID очереди*/
and th.history_type_id = 27 AND th.state_id = 16
  AND h2.history_type_id = 27 AND h2.state_id NOT IN (16)
  AND h2.create_time > th.create_time
order by t.tn