Report generators

From OtterHub - OTRS Community Wiki
Jump to: navigation, search

Intention

The OTRS statistics module can not be compared to full blown report generators like Jasper Reports in terms of glossyness etc. This Wiki page should be a starting point to collect interesting and sophisticated SQL statements which can be used in Jaspersoft's iReport

SQL

We will add more structure here once we have some more examples

Number of tickets created by AGENTS per queue in given time

Interesting part: ticket is created by a user with an ID different to 1 -> normally agents

Please adjust the ${INTERVAL} part to your needs.

OTRS
2.4, 3.0
DB
MySQL 5.1
    SELECT queue.name AS 'Queue', COUNT(ticket.id) AS '# of tickets'
    FROM ticket
        INNER JOIN
            queue ON ticket.queue_id = queue.id
    WHERE
        ticket.create_by != 1
    AND
        DATE(ticket.create_time) >= DATE_SUB(CURDATE(),INTERVAL ${INTERVAL})
    AND
        ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
    GROUP BY queue.name;

Number of tickets created by AGENTS per date in given time

Interesting part: ticket is created by a user with an ID different to 1 -> normally agents

Please adjust the ${INTERVAL} part to your needs.

OTRS
2.4, 3.0
DB
MySQL 5.1
    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.create_by != 1
    AND
        DATE(ticket.create_time) >= DATE_SUB(CURDATE(),INTERVAL ${INTERVAL})
    AND
        ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
    GROUP BY DATE(ticket.create_time);

Number of tickets created by CUSTOMERS per queue in given time

Interesting part: ticket is created by user with ID 1 -> normally root@localhost and thus by the system

Please adjust the ${INTERVAL} part to your needs.

OTRS
2.4, 3.0
DB
MySQL 5.1
    SELECT queue.name AS 'Queue', COUNT(ticket.id) AS '# of tickets'
    FROM ticket
        INNER JOIN
            queue ON ticket.queue_id = queue.id
    WHERE
        ticket.create_by = 1
    AND
        DATE(ticket.create_time) >= DATE_SUB(CURDATE(),INTERVAL ${INTERVAL})
    AND
        ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
    GROUP BY queue.name;

Number of tickets created by CUSTOMERS per date in given time

Interesting part: ticket is created by user with ID 1 -> normally root@localhost and thus by the system

Please adjust the ${INTERVAL} part to your needs.

OTRS
2.4, 3.0
DB
MySQL 5.1
    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.create_by = 1
    AND
        DATE(ticket.create_time) >= DATE_SUB(CURDATE(),INTERVAL ${INTERVAL})
    AND
        ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
    GROUP BY DATE(ticket.create_time);

Number of tickets created in a CLOSED state per queue (first level solution)

Interesting part: ticket.create_time == ticket.change_time AND ticket.ticket_state is a closed state

Please adjust the ${INTERVAL} part to your needs.

OTRS
2.4, 3.0
DB
MySQL 5.1
    SELECT queue.name AS 'Queue', COUNT(ticket.id) AS '# of tickets'
    FROM ticket
    INNER JOIN
        queue ON ticket.queue_id = queue.id
    WHERE
        ticket.ticket_state_id IN (
            SELECT id FROM ticket_state WHERE type_id IN (
                SELECT id FROM ticket_state_type WHERE name = 'closed'
            )
        )
    AND
        ticket.create_time = ticket.change_time
    AND
        DATE(ticket.create_time) >= DATE_SUB(CURDATE(),INTERVAL ${INTERVAL})
    AND
        ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
    GROUP BY queue.name;

Average time in hours until a ticket is closed per queue

Please adjust the ${INTERVAL} part to your needs.

OTRS
2.4, 3.0
DB
MySQL 5.1
    SELECT
        queue.name AS 'Queue',
        AVG(
            TIMESTAMPDIFF( HOUR, ticket.create_time, ticket.change_time)
        ) AS 'avg. life time'
    FROM ticket
    INNER JOIN
        queue ON ticket.queue_id = queue.id
    WHERE
        ticket.ticket_state_id IN (
            SELECT id FROM ticket_state WHERE type_id IN (
                SELECT id FROM ticket_state_type WHERE name = 'closed'
            )
        )
    AND
        DATE(ticket.create_time) >= DATE_SUB(CURDATE(),INTERVAL ${INTERVAL})
    AND
        ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
    GROUP BY queue.name;

Average time documented by Agent per queue per time

Please adjust the ${INTERVAL} part to your needs.

OTRS
2.4, 3.0
DB
MySQL 5.1
    SELECT
        queue.name AS 'Queue',
        SUM(time_accounting.time_unit) / COUNT(ticket.id) AS 'avg. time',
        COUNT(ticket.id) AS '# of tickets'
    FROM ticket
    INNER JOIN
        time_accounting ON ticket.id = time_accounting.ticket_id
        AND
        DATE(time_accounting.create_time) >= DATE_SUB(CURDATE(),INTERVAL ${INTERVAL})
    INNER JOIN
        queue ON ticket.queue_id = queue.id
    WHERE
        ticket.ticket_state_id IN (
            SELECT id FROM ticket_state WHERE type_id IN (
                SELECT id FROM ticket_state_type WHERE name = 'closed')
            )   
    AND
        ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
    GROUP BY queue.name;

total time documented by Agent per queue per time

Please adjust the ${INTERVAL} part to your needs.

OTRS
2.4, 3.0
DB
MySQL 5.1
    SELECT
        queue.name AS 'Queue',
        SUM(time_accounting.time_unit) / 60 AS 'total time',
        COUNT(ticket.id) AS '# of tickets'
    FROM ticket
    INNER JOIN
        time_accounting ON ticket.id = time_accounting.ticket_id
        AND
        DATE(time_accounting.create_time) >= DATE_SUB(CURDATE(),INTERVAL ${INTERVAL})
    INNER JOIN
        queue ON ticket.queue_id = queue.id
    WHERE
        ticket.ticket_state_id IN (
            SELECT id FROM ticket_state WHERE type_id IN (
                SELECT id FROM ticket_state_type WHERE name = 'closed')
            )   
    AND
        ticket.queue_id IN ( SELECT id FROM queue WHERE valid_id=1 ORDER BY name)
    GROUP BY queue.name;

Amount of time units per ticket by agent in given time including FreeKeys of related article

Interesting part: If you need to provide a reason or cost centre entry per article, not just per ticket, this is considered here.

OTRS
2.4, 3.0
DB
MySQL 5.1
    SELECT
        u.login AS 'Agent',
        a.create_time AS 'Create time',
        a_freekey1 AS 'Free key #1',
        a_freekey2 AS 'Free key #2',
        t.time_unit AS 'Amount of time units',
        tk.tn AS 'Ticket number',
        tk.title AS 'Ticket title'
    FROM article a
        INNER JOIN
            users u
                INNER JOIN
                    ticket tk
                        INNER JOIN
                            time_accounting t
    ON
        a.create_by = u.id
            AND
                a.ticket_id = tk.id AND a.id = t.article_id
    WHERE 
        a.create_time like DATE_FORMAT(DATE_SUB(current_timestamp, INTERVAL 1 YEAR_MONTH), '%Y-%m-%');
 
Example output
+--------+---------------------+-------------+-------------+----------------------+------------------+---------------+
| Agent  | Create time         | Free key #1 | Free key #2 | Amount of time units | Ticket number    | Ticket title  |
+--------+---------------------+-------------+-------------+----------------------+------------------+---------------+
| marco  | 2011-10-04 11:59:15 | 1234        | abcd        |               100.00 | 2011100410000037 | My title      |
| stefan | 2011-10-04 13:16:41 | 4321        | dcba        |                20.00 | 2011100410000414 | Another title |
| marco  | 2011-10-04 13:22:35 | 0987        | wxyz        |               -30.00 | 2011100410000789 | My title      |
+--------+---------------------+-------------+-------------+----------------------+------------------+---------------+

Count of usage of response template in the last 30 days

   select 
         count(sr.name),
         sr.name 
   from ticket_history as th, standard_response as sr 
   where 
         th.name like 'ResponseTemplate%' and 
         sr.id = LEFT(TRIM(LEADING 'ResponseTemplate (' FROM th.name),LOCATE('/',TRIM(LEADING 'ResponseTemplate (' FROM th.name))-1)
         and DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= th.create_time 
   group by
         sr.name
   order by 
         sr.id;

Agent reported time on tickets

OTRS 3.x Mysql 5.x

   
   SELECT t.tn ticketnumber, 
          t.user_id ownerid, 
          CONCAT( u2.first_name,  " ", u2.last_name ) owner,
          a.create_by user_id,
          CONCAT( u.first_name,  " ", u.last_name ) name, 
          t.title title, 
          q.name queue, 
          t.customer_user_id customer_user,
          t.customer_id customer, 
          ta.time_unit time_unit, 
          DATE_FORMAT( ta.create_time,  "%m/%d/%y" ) create_time, 
          a.a_subject subject, 
          a.a_body body
   FROM ticket t
   LEFT JOIN time_accounting ta ON ta.ticket_id = t.id
   LEFT JOIN queue q ON t.queue_id = q.id
   LEFT JOIN article a ON a.id = ta.article_id
   LEFT JOIN users u ON u.id = a.create_by
   LEFT JOIN users u2 ON u2.id = t.user_id
   WHERE ta.time_unit IS NOT NULL 
   ORDER BY create_time

Sample output:

ticketnumber ownerid owner user_id name title queue customer_user customer time_unit create_time subject body
2011080310000011 1 Admin OTRS 2 Joe User test Raw tester tester@test.com 2.00 10/05/11 Note for some time

Used response templates per Ticket#

    select
         sr.name,
         t.tn,
         t.title
    from 
         ticket_history as th,
         ticket as t,
         standard_response as sr 
    where 
         th.name like 'ResponseTemplate%' and
         th.ticket_id=t.id and
         sr.id = LEFT(TRIM(LEADING 'ResponseTemplate ('FROM th.name),LOCATE('/',TRIM(LEADING 'ResponseTemplate (' FROM th.name))-1)  
    order by 
         t.tn;

Static Statistic Files

Next to the known "Statistics" module with so called dynamic objects, OTRS has the option for static statistic files. THey are stored in $OTRS_HOME/Kernel/System/Stats/Static/*.pm A static statistic is mostly a mixture of SQL statements, results of TicketSearch() use (and the use of other methods), results coming from other external resources (like external databases or local CSV files) and logical processing of result sets. There is one single static file coming with OTRS and another one if you install the FAQ package. You can use these files as example files, copy them and generate your own files. This actually gives you the option to create whatever kind and result of statistic as you define everything by yourself and you can provide these statistics just within the OTRS wher you can set the permissions and everything you know love about OTRS. Many installations use this kind of statistics instead of dealing with the overhead of an external reporting suite like JasperReports, BIRD or CrystalReprts. Of course they have their benefits, but they also require additional knowledge about how to use them while the static files are "just" perl modules which are understandable by every one who is ok with perl (especially the OTRS Group where you can buy specific statistics designed for you special needs). You have to know that every static file can only be used one time in OTRS, so if you need the same statistic twice (maybe because of naming convention for different departments) just copy the existing file to NameOfTheFile2 or whatever fits your needs. If there is a file that is not used within one of the available and numbered statistics you will find a new option in the "New" screen where you can select the Static Object just below the Dynamic Object. Once this file is in use by an numbered statistic the option just disappears.

Links