OTRS plugin for Grafana


#1

Hello

Is it possible integrate OTRS and Grafana?

The OTRS’s dashboard are very poor…

Thanks.

Ivan


#2

The Zabbix App (code on GitHub) would be similar if you want to start developing an app for OTRS.

Is there an API that give you access to the data?


#3

Well, i dont kwon if OTRS has a open API, but i try to acess OTRS database using a SQLDB plugin. Access directly mysql database and do querys.
But SQLDB is not working fine with the lastest Grafana’s version.
When grafana will have a plugin for others databases (link mssql, postgresql or mariadb/mysql) will be able to do dashboards for the most of systems.

These is possible on the next grafana’s roadmap?


#4

Here is the Grafana Roadmap.

A MySQL data source is in development and is already available in nightly builds and will be released in v4.3.0 in the next few days.


#5

It is a wonderfull News!!!


#6

I installed grafana-4.3.0-7748pre1 to test mysql datasource.

when a try a query, i get these error:
dial tcp: address (my mysql ip address): missing port in address

I Try configure host:port, but i got the same error.


#7

This is what the connection details should look like. No http:// before the hostname. That error you are getting means the connection string is wrong.


Using nightly Builds grafana_4.3.0-7785pre1_amd64.deb MYSQL error
#8

Thanks. Its working mysql connection

Now i have some issues in select time…

For example, In the mysql select command:

SELECT DATE(ticket.create_time) AS 'Date', COUNT(ticket.id) AS '# of tickets'
FROM ticket
    INNER JOIN
        queue ON ticket.queue_id = queue.id
WHERE
    ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
GROUP BY DATE(ticket.create_time);

Results:
±-----------±-------------+
| Date | # of tickets |
±-----------±-------------+
| 2016-12-14 | 1 |
| 2016-12-21 | 1 |
| 2017-03-09 | 1 |
| 2017-03-22 | 1 |
| 2017-03-28 | 1 |
| 2017-03-29 | 1 |
| 2017-03-31 | 3 |
| 2017-04-03 | 1 |
| 2017-04-04 | 1 |
| 2017-04-06 | 1 |
| 2017-04-24 | 1 |
| 2017-04-25 | 7 |
| 2017-04-26 | 5 |
| 2017-04-27 | 1 |
| 2017-05-04 | 2 |
±-----------±-------------+

I need Date and tickets became X and Y axes in grafana, but i cant do, uning time series.

Anyone can help me?


#9

Old entrie but I had the same problem:

You have tu change the output format of ticket.create_time and column names are pre definated by grafana:

SELECT UNIX_Timestamp(ticket.create_time) AS time_sec, COUNT(ticket.id) AS value
FROM ticket INNER JOIN queue
ON ticket.queue_id = queue.id
WHERE ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
GROUP BY DATE(ticket.create_time)


#10

Hello,

For reference I made dashboards to view the results in the OTRS Survey module.