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

Только для готовых решений! Пожалуйста, не используйте для вопросов и обсуждений!

Модератор: ykolesnikov

Ответить
paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

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

Сообщение paver » 12 окт 2016, 10:39

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

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

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

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

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

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

Сообщение paver » 12 окт 2016, 10:42

Число открытых и закрытых тикетов по дням (за последние 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
--
OTRS 6.0.22

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

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

Сообщение paver » 12 окт 2016, 10:44

Активность агентов (по добавленным артиклям за последние 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
--
OTRS 6.0.22

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

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

Сообщение paver » 12 окт 2016, 10:48

Активность клиентов (по числу заявок, выводятся создавшие более 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
--
OTRS 6.0.22

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

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

Сообщение paver » 12 окт 2016, 10:51

Тикеты с числом артиклей больше заданного (типа, народ вместо отработки заявки занимается отписками и перепихиванием тикета друг другу).
Чтобы можно было быстро перейти на заявку из списка, в выводе 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
--
OTRS 6.0.22

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

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

Сообщение paver » 12 окт 2016, 10:54

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

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

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
--
OTRS 6.0.22

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

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

Сообщение paver » 20 окт 2016, 10:49

Число открытых и закрытых тикетов в разрезе агентов (за последние 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
Последний раз редактировалось paver 16 ноя 2016, 11:07, всего редактировалось 1 раз.
--
OTRS 6.0.22

alexus
OTRS Гуру
Сообщения: 5192
Зарегистрирован: 20 сен 2010, 18:17
Откуда: Москва
Благодарил (а): 92 раза
Поблагодарили: 82 раза

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

Сообщение alexus » 20 окт 2016, 11:30

# ----
# ----
# 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
С уважением,
Алексей Юсов

Prod: OTRS CE ITSM 6.0.28 on CentOS 7 Apache 2.4 MariaDB 10.4.13 + Radiant Customer Portal

Radiant System OTRS Intergrator RU
Группа OTRS Community в Teleram
Хотите внедрить OTRS? Спросите меня как!

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

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

Сообщение paver » 03 ноя 2016, 12:04

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

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

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 при необходимости можно добавить дополнительные ограничения, например, конкретные очереди или период создания заявки.
--
OTRS 6.0.22

damisha
OTRS Новобранец
Сообщения: 8
Зарегистрирован: 27 янв 2017, 13:34
Откуда: Дубна

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

Сообщение damisha » 01 фев 2017, 11:54

Все добавленные заметки по заявкам, для типов заявок 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 - диапазон (в секундах)
С уважением,
Дамир Асадуллин

Prod: OTRS ITSM 5.0.14 on Debian 8.6 x64 Linux with MySQL 5.5

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

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

Сообщение paver » 02 фев 2017, 07:15

damisha писал(а):Все добавленные заметки по заявкам, для типов заявок 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"
--
OTRS 6.0.22

alex.t
OTRS Новобранец
Сообщения: 30
Зарегистрирован: 29 апр 2018, 09:26
Благодарил (а): 1 раз
Поблагодарили: 9 раз

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

Сообщение alex.t » 25 май 2018, 14:01

На выходе будет Отчет содержащий Номер тикета, Тему тикета, Приоритет, Время создания, Время решения (у меня этот 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

Ответить