Creating reports in GLPI

For some time in our company, GLPI has been used as the Helpdesk system. About the system itself, you can read here .
Of course, the undoubted advantages of GLPI is its free and open codes, as well as a fairly large number of different plug-ins - one of them will be discussed.
When working with you, we naturally use ITIL recommendations, and they assume that everything should be measured and assessed. So GLPI lacks a good reporting system.
Initially, GLPI (v.0.80.2) contains 4 ticket reports:

• Global
• By ticket
• By item
• By hardware

This statistic is not quite enough.

I found the Reports for GLPI plugin, installed and added some more reports for GLPI - one of the most interesting: Tickets opened at night, sorted by priority. This report allows you to determine how many applications you had during a certain period of time over a given period (although, again, for convenience we had to make a small change to the code). For example, I am very interested in how many applications I had created in September 2011 at night - respectively, we enter a range of dates and times and get a report.
Changes made:

In the report Tickets opened at night, sorted priority:

82: WHERE `glpi_tickets`.`status` NOT IN ('new') ".

:

WHERE `glpi_tickets`.`status` NOT IN ('solved', 'closed') ".

')
The Reports plugin is also interesting for creating new own reports.
I will not consider simple reports (output in one table) - they are easily generated using the SimpleReport function.

Consider the creation of a consolidated report - output in several separate tables on one page (this is convenient from the point of view of visibility).

The report will be called KPI - in it we will count the number of user requests in the period of time, the number of tickets resolved remotely, in place and escalated.

1. First install the Reports plugin.
2. Next, in the \ glpi \ plugins \ reports \ report \ folder, we create the statkpi folder (the stat prefix is ​​required for the report to be displayed in the Assistance-Statistics section, and not for the Reports - it’s just more convenient for us).
3. In this folder, create a file statkpi.php (if you are interested in translating into other languages, it is better to additionally create localization files and read all the names from them).
4. Copy the header from another report:

<?php
$USEDBREPLICATE=1;
$DBCONNECTION_REQUIRED=0;
define('GLPI_ROOT', '../../../..');
include (GLPI_ROOT . "/inc/includes.php");
$report = new PluginReportsAutoReport();


5. Next, I added an instance of the PluginReportsDateIntervalCriteria class to apply the time criteria myself.

$dt = new PluginReportsDateIntervalCriteria($report, '`glpi_tickets`.`date`', $LANG["reports"][60]);

6. We display the form to establish the reporting period and check the validity of filling:

$report->displayCriteriasForm();
$display_type = HTML_OUTPUT;
if ($report->criteriasValidated())


7. Next, we start to generate a report (we get the name)

{
$report->setSubNameAuto();
$title = $report->getFullTitle();


8. Get the variable start and end of the period, as well as the identifier of our organization:

$stdate=$dt->getStartDate();
$findate=$dt->getEndDate();
$ent=$CFG_GLPI["entity"];


9.We create a query to the mysql database (users select only those whose name starts with 'ru' and pre-created types of ticket solutions - correct for your company):

$sql = "SELECT (select name from glpi_entities where id=$ent) as 'entity',
(select count(id) from glpi_users where is_active=1 and name like 'ru%') as 'users',
(select count(id) from glpi_tickets where date >= '$stdate' and date <= '$findate') as 'id3',
(select count(id) from glpi_tickets where date >= '$stdate' and date <= '$findate')/(select count(id) from glpi_users where is_active=1 and name like 'ru%') as 'avg',
(select count(id) from glpi_tickets where date >= '$stdate' and date <= '$findate' and ticketsolutiontypes_id = 5) as 'onsite',
(select count(id) from glpi_tickets where date >= '$stdate' and date <= '$findate' and ticketsolutiontypes_id = 4) as 'servicedesk',
(select count(id) from glpi_tickets where date >= '$stdate' and date <= '$findate' and ticketsolutiontypes_id = 6) as 'escalated'";


10. Execute the query to the database and pull out the variables from the result:

$result_sql = mysql_query ($sql);
$result_row=mysql_fetch_array($result_sql, MYSQL_ASSOC);
$entity = $result_row['entity'];
$users = $result_row['users'];
$tickets = $result_row['id3'];
$avg = $result_row['avg'];
$tickets_onsite = $result_row['onsite'];
$onsite_ratio = round($tickets_onsite/$tickets*100, 1);
$onsite_ef = round($tickets_onsite/$users, 1);
$tickets_servicedesk = $result_row['servicedesk'];
$servicedesk_ratio = round($tickets_servicedesk/$tickets*100, 1);
$servicedesk_ef = round($tickets_servicedesk/$users, 1);
$tickets_escalated = $result_row['escalated'];
$escalated_ratio = round($tickets_escalated/$tickets*100, 1);
$escalated_ef = round($tickets_escalated/$users, 4);


11. And we draw the tables (we have 3 of them so far) - the code is rather long, so that it is in a separate file . Naturally, this is just an example of creating your own report - it can help someone.