Reporting in GLPI

For some time now, our company has used GLPI as a Helpdesk system. You can read about the system here .
Of course, the indisputable advantages of GLPI are its free and open source codes, as well as a fairly large number of different plug-ins - one of them will be discussed.
When working with us, we naturally use the ITIL recommendations, and they suggest that everything needs to be measured and evaluated. So GLPI lacks a good reporting system.
Initially, 4 ticket reports were built into GLPI (v.0.80.2):

• Global
• By ticket
• By item
• By hardware

These statistics are not quite enough.

I found the Reports plugin for GLPI, installed and added a few 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 in a certain period of time over a given period (although, again, you had to make a small change to the code for convenience). For example, I’m very interested in how many applications I created in September 2011 at night — accordingly, we enter the date and time range and get a report.
Changes made:

To the Tickets report opened at night, sorted by priority: The Reports plugin is also interesting for creating new own reports.

Строка 82: WHERE `glpi_tickets`.`status` NOT IN ('new') ".

Вместо строки:

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



I will not consider simple reports (output in one table) - they are easily generated using the SimpleReport function.

Consider creating a summary report - output to several separate tables on one page (this is convenient from the point of view of clarity).

The report will be called KPI - in it we will count the number of user requests in a 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, create the statkpi folder (the stat prefix is ​​necessary for the report to be displayed in the Assistance-Statistics section, and not Reports - it's just more convenient for us).
3. In this folder, create the statkpi.php file (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: 5. Next, I added an instance of the PluginReportsDateIntervalCriteria class to apply the time criteria myself. 6. We display the form for establishing the reporting period and check the validity of the filling: 7. Next, we begin to generate a report (get the name) 8. We get the variables of the beginning and end of the period, as well as the identifier of our organization: 9. We make a request to the mysql database (only those are selected , whose name begins with 'ru' and the types of solutions for tickets are pre-created - correct for your company):

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




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



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




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




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




$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. We execute a query to the database and pull the variables from the result: 11. And draw the tables (we have 3 so far) - the code is long enough so that it is in a separate file . Naturally, this is just an example of creating your own report - maybe it will help someone.

$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);