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.

<?php
$usr = 'john'; $pas = 'doe'; $db = 'glpi_0837';</pre>
$db = new mysqli("localhost", $usr, $pas, $db);
if(mysqli_connect_errno()){
 printf("Connect Failed %s\n", mysqli_connect_error());
 exit();
}</pre>
/* 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,
 ti.users_id_recipient,
 tu.id as tuid,
 tu.tickets_id,
 tu.users_id,
 tu.type,
 us.id,
 us.name,
 gr.id as gid,
 gr.name as group_name,
 gu.users_id,
 gu.groups_id
 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']}";
 }
 }else{
 $messages[$row['id']][] = 'ERROR: No group assigned to requester!';
 $messages[$row['id']][] = "INFO: Please assign groups to the requester in this ticket.";
 }
 }else{
 $messages[$row['id']][] = "ERROR: SQL errorno: {$db->errno} met melding: {$db->error} is opgetreden";
 }
 }else{
 //$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;
if(isset($messages)){
 foreach($messages as $key => $val){
 foreach($val as $k => $v){
 $message .= "ON Ticket: $key : {$v} <br/>";
 $ecount ++;
 }
 }
 $mail = true;
}else{
 $mail = false;
}

// Insert the associations
if(isset($groups)){
 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');";
 if($db->query($sql)){
 //$message .= "INFO: Group $k:$v assigned to ticket $key<br/>";
 }else{
 $message .= "ERROR: Failed to associate $k:$v to ticket $key<br/>";
 }
 }
 }
 $message .= "ON General : Finished... <br/>";
}else{
 $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
if($mail){
 mail($to, $subject, $message, $headers);
}
//echo $message;
<pre>

?>

Simply run this script
(AFTER YOU HAVE TESTED IT AGAINST YOUR TEST ENVIRONMENT)

About these ads

About Chris Gralike

Listen carefully to the people around you. Keep an open-mind, realize there is far more to learn, do, and accomplish. Treat your colleagues and competitors with respect and have FUN doing what you do best! These are my recommendations for success, what are yours? Find me at : LINKEDIN : http://www.linkedin.com/in/chrisgralike TWITTER : http://twitter.com/#!/chris_gralike WORDPRESS : http://sysengineers.wordpress.com/

Posted on October 28, 2013, in General, GLPI and tagged , , , , , , , , , . Bookmark the permalink. 2 Comments.

  1. Chris, other than running on a cron schedule, how does this differ from the Behaviour plugin, which automatically assigns the requester’s group?

  2. 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 54 other followers

%d bloggers like this: