Jgreen has submitted this change and it was merged. Change subject: redeploy OTRS idle_agent_report ......................................................................
redeploy OTRS idle_agent_report Change-Id: Ie5151001d03dc276c8588995b97b2f9e4905d5de --- A files/otrs/idle_agent_report M manifests/role/otrs.pp 2 files changed, 253 insertions(+), 0 deletions(-) Approvals: Jgreen: Checked; Looks good to me, approved jenkins-bot: Verified diff --git a/files/otrs/idle_agent_report b/files/otrs/idle_agent_report new file mode 100755 index 0000000..7e69598 --- /dev/null +++ b/files/otrs/idle_agent_report @@ -0,0 +1,247 @@ +#!/usr/bin/perl +use strict; +use CGI; +use FindBin qw($Bin); # use ../ as lib location +use lib "$Bin/../.."; +use lib "$Bin/../../Kernel/cpan-lib"; +use Kernel::Config; +use Kernel::System::Time; +use Kernel::System::Log; +use Kernel::System::Main; +use Kernel::System::Encode; +use Kernel::System::User; +use Kernel::System::Group; +use Kernel::System::DB; +use Kernel::System::Auth; +use Kernel::System::AuthSession; + +# if any of these queries returns >5K rows we have a problem +my $rowlimit = 5000; + + +# http://dev.otrs.org/2.4/Kernel/System/Group.html +# http://meta.wikimedia.org/wiki/OTRS#OTRS_administrators +my %RoleOK2View = ( + 'OTRS admins' => 1, + 'Wikimedia Technical Staff' => 1, +); + +# reporting queries provided by Philippe, simply numbered for easy access from URI query string +# stab otrs db libs for lacking fetchrow hashref +my $query = { + 1 => { + 'desc' => 'Last login over 6 months', + 'cols' => [ qw(login id email lastlogin) ], + 'sql' => 'SELECT login, id, eml.preferences_value, DATE_FORMAT(FROM_UNIXTIME(ull.preferences_value), "%Y-%m-%d") FROM users LEFT JOIN user_preferences AS ull ON (ull.user_id = users.id) LEFT JOIN user_preferences AS eml ON (eml.user_id = users.id) WHERE ull.preferences_key = "UserLastLogin" AND eml.preferences_key = "UserEmail" AND valid_id = 1 AND FROM_UNIXTIME(ull.preferences_value) < DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY FROM_UNIXTIME(ull.preferences_value)', + }, + 2 => { + 'desc' => 'No login one month, no ticket three months', + 'cols' => [ qw(login id email lastlogin lastticket) ], + 'sql' => 'SELECT u.login, u.id, eml.preferences_value, DATE_FORMAT(FROM_UNIXTIME(ull.preferences_value), "%Y-%m-%d"), DATE_FORMAT(last_ticket, "%Y-%m-%d") FROM users AS u LEFT JOIN user_preferences AS ull ON (ull.user_id = u.id) LEFT JOIN user_preferences AS eml ON (eml.user_id = u.id) LEFT JOIN (SELECT MAX(change_time) AS last_ticket, user_id FROM ticket GROUP BY user_id) AS t ON (t.user_id = u.id) WHERE ull.preferences_key = "UserLastLogin" AND u.valid_id = 1 AND eml.preferences_key = "UserEmail" AND FROM_UNIXTIME(ull.preferences_value) < DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND FROM_UNIXTIME(ull.preferences_value) >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND last_ticket < DATE_SUB(CURDATE(), INTERVAL 3 MONTH) GROUP BY u.id ORDER BY last_ticket', + }, + 3 => { + 'desc' => 'Inactive users who occasionally log in', + 'cols' => [ qw(login id email lastlogin lastticket) ], + 'sql' => 'SELECT u.login, u.id, eml.preferences_value, DATE_FORMAT(FROM_UNIXTIME(ull.preferences_value), "%Y-%m-%d"), DATE_FORMAT(last_ticket, "%Y-%m-%d") FROM users AS u LEFT JOIN user_preferences AS ull ON (ull.user_id = u.id) LEFT JOIN user_preferences AS eml ON (eml.user_id = u.id) LEFT JOIN (SELECT MAX(change_time) AS last_ticket, user_id FROM ticket GROUP BY user_id) AS t ON (t.user_id = u.id) WHERE ull.preferences_key = "UserLastLogin" AND u.valid_id = 1 AND eml.preferences_key = "UserEmail" AND last_ticket < DATE_SUB(CURDATE(), INTERVAL 120 DAY) GROUP BY u.id ORDER BY last_ticket', + + }, +}; + +# I use CGI.pm here instead of the built in stuff to avoid tangling with the +# OTRS UI libaries. CGI.pm is quick and dirty way to select query and (below) +# whether or not to output a csv file +my $q = CGI->new; +my $params = $q->Vars; +my $qn = (defined $query->{$params->{'qn'}}) ? $params->{'qn'} : 1; + +# essentially boilerplate to create common OTRS objects we'll use for database +# calls and client authentication +my %CommonObject = (); +$CommonObject{'ConfigObject'} = Kernel::Config->new(); +$CommonObject{'LogObject'} = Kernel::System::Log->new( + 'LogPrefix' => 'idle_agent_report', + 'ConfigObject' => $CommonObject{'ConfigObject'}, +); +$CommonObject{'MainObject'} = Kernel::System::Main->new(%CommonObject); +$CommonObject{'EncodeObject'} = Kernel::System::Encode->new(%CommonObject); +$CommonObject{'TimeObject'} = Kernel::System::Time->new(%CommonObject); +$CommonObject{'DBObject'} = Kernel::System::DB->new(%CommonObject); +$CommonObject{'UserObject'} = Kernel::System::User->new(%CommonObject); +$CommonObject{'GroupObject'} = Kernel::System::Group->new(%CommonObject); +$CommonObject{'AuthObject'} = Kernel::System::Auth->new(%CommonObject); +$CommonObject{'SessionObject'} = Kernel::System::AuthSession->new(%CommonObject); + +# OTRS-authenticated sessions should have a valid Session cookie which returns +# UserID etc. We retrieve the cookie and check against OTRS auth API. +# see http://dev.otrs.org/2.4/Kernel/System/AuthSession.html +# I do not bother to update stored session data for fear of breaking something. +my (%SessionData,$RoleAuthOk); +my $UserRoles; +my $ClientSessionID = $q->cookie('Session'); +if (defined $ClientSessionID) { + my $ValidSessionID = $CommonObject{'SessionObject'}->CheckSessionID( + 'SessionID' => $ClientSessionID, + ); + # use SessionData UserID to get Role membership, then check against %RoleOK2View + # there must be a better way but the Group API seems utterly lame and broken + if ($ValidSessionID) { + %SessionData = $CommonObject{'SessionObject'}->GetSessionIDData( + 'SessionID' => $ClientSessionID, + ); + my %Roles = $CommonObject{'GroupObject'}->RoleList( + 'Valid' => 1, + ); + my %UserRoles = $CommonObject{'GroupObject'}->GroupUserRoleMemberList( + 'UserID' => $SessionData{'UserID'}, + 'Result' => 'HASH', + ); + for my $RoleID (keys %UserRoles) { + $UserRoles .= "$RoleID-$Roles{$RoleID}|"; + if (defined $RoleOK2View{$Roles{$RoleID}}) { + $SessionData{'RoleAuthenticated'} = 1; + } + } + chop $UserRoles; + } +} + +# Various possible output outcomes +if ( (! defined %SessionData) or (! defined $SessionData{'RoleAuthenticated'}) or (! defined $CommonObject{'DBObject'}) ) { + + # failure modes + print "Content-type: text/html\n\n" . + "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" " . + "\"http://www.w3.org/TR/html4/loose.dtd\";>\n" . + "<html><head>\n" . + "<meta HTTP-EQUIV='REFRESH' content='5; url=/'>\n" . + "</head><body>\n"; + + if ( ! defined %SessionData ) { + # No valid session data + print "<b>You need to log in to OTRS.</b><p>\n"; + } elsif ( ! defined $SessionData{'RoleAuthenticated'} ) { + # Logged in, but not in an approved Role + print "<b>Sorry, this report is only available to administrators.</b><p>\n"; + $CommonObject{LogObject}->Log( + Priority => 'error', + Message => "$SessionData{'UserID'}: $UserRoles", + ); + } elsif ( ! defined $CommonObject{'DBObject'}) { + # Db connection fail + print "<b>Database connection failed!</b></p>\n" + } + + print "You should be redirected to the OTRS main page in a few seconds.<br>\n" . + "If not, <a href='/'>[click here]</a><p>\n" . + "</body></html>"; + +} elsif (defined $params->{'csv'}) { + + # Valid session, group membership, and successful db connection. + # CGI request for CSV format. Proceed. + + # suggest document name + my $filename = 'OTRS-' . $query->{$qn}->{'desc'}; + $filename =~ s/\s+/_/g; + + print "Content-type: text/csv\n" . + "Content-Disposition: attachment; filename=" . $filename . ".csv\n\n" . + join("\t", (@{$query->{$qn}->{'cols'}},'queues')) . "\n"; + + # fetch and format the user data + my $Users = GetUsers(); + for my $UserID (sort { $Users->{$a}->{'row'} <=> $Users->{$b}->{'row'}} keys %{$Users}) { + for my $c (@{$query->{$qn}->{'cols'}}) { + print "$Users->{$UserID}->{$c}\t"; + } + print join(',', sort keys %{$Users->{$UserID}->{'queues'}}) . "\n"; + } + +} else { + + # Valid session, group membership, and successful db connection. Proceed. + print "Content-type: text/html\n\n" . + "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" " . + "\"http://www.w3.org/TR/html4/loose.dtd\";>\n" . + "<html><head>\n" . + "<title>otrs last login report</title>\n" . + "<style type='text/css'>\n" . + " body,a,textarea { background-color:white; color:black; font:13px sans; }\n" . + " td { background-color:white; color:black; font:13px sans; vertical-align:top; }\n" . + " input, select { background-color:white; color:black; border: 1px solid gray; font:13px sans; }\n" . + " table, td { border-width: 1px 1px 1px 1px; border-collapse: collapse; }\n" . + " a { color:blue; text-decoration:none; }\n" . + "</style>\n" . + "</head><body>\n"; + + # wee form at the top for report controls + print "<form method=get action=''>\n<select name=qn size=1 onchange=submit()>\n"; + for my $n (sort keys %{$query}) { + print "<option value=$n"; + print " selected" if ($n == $qn); + print ">$query->{$n}->{'desc'}</option>\n"; + } + print "</select>\n<input type=submit value=>>\n" . + "<input type=button value='csv' onClick=\"parent.location='$ENV{SCRIPT_URI}?qn=$qn&csv=1'\">" . + "</form><p>\n"; + + # the report table + print "<table border=1>\n" . + "<tr><td><b>" . join("</b></td><td><b>", (@{$query->{$qn}->{'cols'}},'queues')) . "</b></tr>\n"; + my $Users = GetUsers(); + for my $UserID (sort { $Users->{$a}->{'row'} <=> $Users->{$b}->{'row'}} keys %{$Users}) { + print "<tr>"; + for my $c (@{$query->{$qn}->{'cols'}}) { + print "<td>$Users->{$UserID}->{$c}</td>"; + } + print "<td>" . join(', ', sort keys %{$Users->{$UserID}->{'queues'}}) . "</td>\n</tr>\n"; + } + + print "</table>\n</body></html>"; + +} +exit; + + + + +# Get list of users matching the main query report, and the queues they can read+write +sub GetUsers { + + # Get list of users + my $Users; + my $row = 1; + $CommonObject{'DBObject'}->Prepare( SQL => $query->{$qn}->{'sql'}, Limit => $rowlimit ); + while (my @row = $CommonObject{'DBObject'}->FetchrowArray()) { # no fetchrow hashref, stab! cry! + my $h; + map { $h->{${$query->{$qn}->{'cols'}}[$_]} = $row[$_] } (0 .. scalar(@row)); + unless (defined $Users->{$h->{'id'}}->{'row'}) { + $h->{'row'} = $row; + $row++; + } + $Users->{$h->{'id'}} = $h; + } + + # Get list of queues + my $query = "SELECT u.id,q.name " . + "FROM users u, role_user ru, group_role gu, queue q, groups g, roles r " . + "WHERE u.id = ru.user_id " . + "AND ru.role_id = r.id " . + "AND ru.role_id = gu.role_id " . + "AND gu.group_id = q.group_id " . + "AND g.id = gu.group_id " . + "AND gu.permission_key in ('rw') " . + "AND gu.permission_value = 1 " . + "AND r.valid_id = 1 " . + "AND q.valid_id = 1 " . + "AND u.valid_id = 1 " . + "AND g.valid_id = 1 " . + "AND u.id IN (" . join(',', keys %{$Users}) . ")"; + $CommonObject{'DBObject'}->Prepare( SQL => $query ); + while (my @row = $CommonObject{'DBObject'}->FetchrowArray()) { + $row[1] =~ s/::.*$//; # no documentation, but OTRS treats :: as a delimiter for grouping queues + $Users->{$row[0]}->{'queues'}->{$row[1]} = 1; + } + + return $Users; +} diff --git a/manifests/role/otrs.pp b/manifests/role/otrs.pp index b8591e1..c5bdcc7 100644 --- a/manifests/role/otrs.pp +++ b/manifests/role/otrs.pp @@ -49,6 +49,12 @@ group => www-data, mode => '0755', source => 'puppet:///files/otrs/otrs.TicketExport2Mbox.pl'; + '/opt/otrs/bin/cgi-bin/idle_agent_report': + ensure => present, + owner => otrs, + group => www-data, + mode => '0755', + source => 'puppet:///files/otrs/idle_agent_report'; '/usr/local/bin/train_spamassassin': ensure => present, owner => root, -- To view, visit https://gerrit.wikimedia.org/r/83822 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: Ie5151001d03dc276c8588995b97b2f9e4905d5de Gerrit-PatchSet: 1 Gerrit-Project: operations/puppet Gerrit-Branch: production Gerrit-Owner: Jgreen <jgr...@wikimedia.org> Gerrit-Reviewer: Jgreen <jgr...@wikimedia.org> Gerrit-Reviewer: jenkins-bot _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
