Update GLPI tickets with requesters group

When using GLPI it can be very usefull to automatically assign a group based on the ticket requester. This allows you to use the reporting module and report for instance based on dept group. The problem is that GLPI does allow this using the Behaviour plugin, but it will not report tickets that couldnt be assigned a group. This will actually render reports unusable because you might miss unassigned tickets. For this reason we wrote a little script to process this AND report any ticket that couldnt be assigned (the requester isnt assigned a group)

Business Rules:
0. GLPI version : 0.83.7
1. GLPI uses the mailgate that creates tickets of known users.
2. All known users are assigned to at least one group
4. Script seeks groups and then assigns them uniquely to the ticket.
5. Script will be triggered by cron
6. All actions will/can be reported in a mail
7. If no actions where executed, no mail will be send.

$usr = 'john'; $pas = 'doe'; $db = 'glpi_0837';</pre>
$db = new mysqli("localhost", $usr, $pas, $db);
 printf("Connect Failed %s\n", mysqli_connect_error());
/* Get all the tickets */
$s1 = 'select t.id, t.users_id_recipient from glpi_tickets t';
$r1 = $db->query($s1);
while($row = $r1->fetch_array(MYSQLI_ASSOC)){
 // check to see if ticket has a group assigned //
 $s2 = "select * from glpi_groups_tickets where tickets_id = '{$row['id']}' and type = '1'";
 $res1 = $db->query($s2);
 // Update the tickets without a group assignment.
 if($res1->num_rows == 0){
 // There is no group for this ticket so find the applicable group and assign it
 $s3 = "select ti.id as tid,
 tu.id as tuid,
 gr.id as gid,
 gr.name as group_name,
 FROM glpi_tickets ti, glpi_tickets_users tu, glpi_groups gr, glpi_users us, glpi_groups_users gu
 WHERE ti.id = tu.tickets_id
 AND tu.type = 1
 AND tu.users_id = us.id
 AND tu.users_id = gu.users_id
 AND gu.groups_id = gr.id
 AND ti.id = '{$row['id']}'";
 if($res2 = $db->query($s3)){
 if($res2->num_rows > 0){
 while($row1 = $res2->fetch_array(MYSQLI_ASSOC)){
 $groups[$row1['tid']][$row1['gid']] = $row1['group_name'];
 $messages[$row['id']][] = "INFO: Updated ticket:{$row1['tid']} with group {$row1['gid']}:{$row1['group_name']}";
 $messages[$row['id']][] = 'ERROR: No group assigned to requester!';
 $messages[$row['id']][] = "INFO: Please assign groups to the requester in this ticket.";
 $messages[$row['id']][] = "ERROR: SQL errorno: {$db->errno} met melding: {$db->error} is opgetreden";
 //$messages[$row['id']][] = 'INFO: Ticket allready has a group assigned';

// Generate a mailmessage
$message = 'INFO: Script running at: https://glpi.amis.nl/salami/automated_tasks/assign_actor_groups.php <br/>';
$ecount = 0;
 foreach($messages as $key => $val){
 foreach($val as $k => $v){
 $message .= "ON Ticket: $key : {$v} <br/>";
 $ecount ++;
 $mail = true;
 $mail = false;

// Insert the associations
 foreach($groups as $key => $val){
 foreach($val as $k => $v){
 $sql = "insert into glpi_groups_tickets(tickets_id, groups_id, type) values('{$key}','{$k}','1');";
 //$message .= "INFO: Group $k:$v assigned to ticket $key<br/>";
 $message .= "ERROR: Failed to associate $k:$v to ticket $key<br/>";
 $message .= "ON General : Finished... <br/>";
 $message .= "ON General : Did nothing, but finished with succes... <br/>";
if($ecount > 0){
 $message .= "ON General : INFO: Please correct the reported errors <br/>";

$to = 'AMIS Support <support@amis.nl>';

$subject = 'Automated ticket - groups assignment';

// To send HTML mail, the Content-type header must be set
$headers = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

// Additional headers
$headers .= 'From: Monitor <monitor@amis.nl>' . "\r\n";

// Mail it
 mail($to, $subject, $message, $headers);
//echo $message;


Simply run this script


2 thoughts on “Update GLPI tickets with requesters group

  1. To be honest, I am unfamiliar with the behavior plugin. It might well have the exact same functionality. I haven’t tested it though.

    One difference might be that this script reports tickets that could not be assigned with a group.
    The generated mail is forwarded to the same mailbox that is being processed by the mailgate.
    This allows us to keep the ticketdata complete so we dont miss anything in our reports.

    But thanks for the insight non the less 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s