Question about reports

RT Developers
1

I want to make a report or a statistic on who creates most tickets. All my users have a commenet filed which begins with his role, for example student faculty of XXX, stuent faculty of YYY, professor faculty of XXX, Secretary faculty of XXX.
Now I want to know which group of users creates the most tickets. I searches in Tickets Overlay where I can limit results by Watchers, in special type the requestor.
I tried using a SQL gui to get an idea of the structure. When i make limitwatcher with the requestor, I use the requestors mail address, which is a field in the users table, so it should be the same to have a search by comments, and wildcards shouldn’t be harder…
How can I start a limit search, or create one that filters the comment instead of the emailaddress, I tried to grep by emailaddress, but haven’t find where it is coded…

Thanks for any help

Samuel

2

Now I got it!!!

I send it to your for your interest.
I copied limit watcher and changed a little bit and created LimitUserComments.
With LIKE as operator instead of = you can search a part of it
here the diff, for me it seems to work, but I have to rewrite the statistic site, because i get all results x2

— Tickets_Overlay.pm.17122003 2003-12-17 13:13:58.000000000 +0100
+++ Tickets_Overlay.pm 2003-12-17 13:12:39.000000000 +0100
@@ -103,6 +103,7 @@
Requestor => [‘WATCHERFIELD’ => ‘Requestor’,],
CC => [‘WATCHERFIELD’ => ‘Cc’,],
AdminCC => [‘WATCHERFIELD’ => ‘AdminCC’,],

  • UserComments => [‘USERCOMMENTS’],
    Watcher => [‘WATCHERFIELD’],
    LinkedTo => [‘LINKFIELD’,],
    CustomFieldValue =>[‘CUSTOMFIELD’,],
    @@ -119,6 +120,7 @@
    TRANSFIELD => &_TransLimit,
    TRANSDATE => &_TransDateLimit,
    WATCHERFIELD => &_WatcherLimit,
  • USERCOMMENTS => &_UserCommentsLimit,
    LINKFIELD => &_LinkFieldLimit,
    CUSTOMFIELD => &_CustomFieldLimit,
    );
    @@ -590,6 +592,78 @@

}

+sub _UserCommentsLimit {

  • my ($self,$field,$op,$value,@rest) = @_;
  • my %rest = @rest;
  • $self->_OpenParen;
  • my $groups = $self->NewAlias(‘Groups’);
  • my $group_princs = $self->NewAlias(‘Principals’);
  • my $groupmembers = $self->NewAlias(‘CachedGroupMembers’);
  • my $member_princs = $self->NewAlias(‘Principals’);
  • my $users = $self->NewAlias(‘Users’);
  • #Find user watchers
    +# my $subclause = undef;
    +# my $aggregator = ‘OR’;
    +# if ($restriction->{‘OPERATOR’} =~ /!|NOT/i ){
    +# $subclause = ‘AndEmailIsNot’;
    +# $aggregator = ‘AND’;
    +# }
  • $self->_SQLLimit(ALIAS => $users,
  •               FIELD => $rest{SUBKEY} || 'Comments',

  •               VALUE           => $value,

  •               OPERATOR        => $op,

  •               CASESENSITIVE   => 0,

  •               @rest,

  •              );

  • {{{ Tie to groups for tickets we care about

  • $self->_SQLLimit(ALIAS => $groups,
  •               FIELD => 'Domain',

  •               VALUE => 'RT::Ticket-Role',

  •               ENTRYAGGREGATOR => 'AND');
  • $self->Join(ALIAS1 => $groups, FIELD1 => ‘Instance’,
  •          ALIAS2 => 'main',   FIELD2 => 'id');

  • }}}

  • If we care about which sort of watcher

  • my $meta = $FIELDS{$field};
  • my $type = ( defined $meta->[1] ? $meta->[1] : undef );
  • if ( $type ) {
  • $self->_SQLLimit(ALIAS => $groups,
  •                 FIELD => 'Type',

  •                 VALUE => $type,

  •                 ENTRYAGGREGATOR => 'AND');
  • }
  • $self->Join (ALIAS1 => $groups, FIELD1 => ‘id’,
  •           ALIAS2 => $group_princs, FIELD2 => 'ObjectId');
  • $self->_SQLLimit(ALIAS => $group_princs,
  •               FIELD => 'PrincipalType',

  •               VALUE => 'Group',

  •               ENTRYAGGREGATOR => 'AND');
  • $self->Join( ALIAS1 => $group_princs, FIELD1 => ‘id’,
  •           ALIAS2 => $groupmembers, FIELD2 => 'GroupId');
  • $self->Join( ALIAS1 => $groupmembers, FIELD1 => ‘MemberId’,
  •           ALIAS2 => $member_princs, FIELD2 => 'id');
  • $self->Join (ALIAS1 => $member_princs, FIELD1 => ‘ObjectId’,
  •           ALIAS2 => $users, FIELD2 => 'id');
  • $self->_CloseParen;
    +}
    sub _LinkFieldLimit {
    my $restriction;
    my $self;
    @@ -1307,6 +1381,32 @@

}

+sub LimitUserComments {

  • my $self = shift;
  • my %args = ( OPERATOR => ‘=’,
  •             VALUE => undef,

  •             TYPE => undef,

  •           @_);
  • $RT::Logger->debug(“we are searching for $args{‘VALUE’} and operator $args{‘OPERATOR’} also type $args{‘TYPE’}”);
  • #build us up a description
    +# my ($watcher_type, $desc);
    +# if ($args{‘TYPE’}) {
    +# $watcher_type = $args{‘TYPE’};
    +# }
    +# else {
    +# $watcher_type = “Watcher”;
    +# }
  • $self->Limit (FIELD => ‘UserComments’,
  •              VALUE => $args{'VALUE'},

  •              OPERATOR => $args{'OPERATOR'},

  •              TYPE => $args{'TYPE'},

  •              DESCRIPTION => join(

  •               ' ', $self->loc('UserComments'), $args{'OPERATOR'}, $args{'VALUE'},

  •              ),

  •             );

+}

}}}

SamuelFrom: Senoner Samuel
Sent: Tuesday,16 December,2003 16:59
To: rt-devel@lists.fsck.com
Subject: [rt-devel] Question about reports

I want to make a report or a statistic on who creates most tickets. All my users have a commenet filed which begins with his role, for example student faculty of XXX, stuent faculty of YYY, professor faculty of XXX, Secretary faculty of XXX.
Now I want to know which group of users creates the most tickets. I searches in Tickets Overlay where I can limit results by Watchers, in special type the requestor.
I tried using a SQL gui to get an idea of the structure. When i make limitwatcher with the requestor, I use the requestors mail address, which is a field in the users table, so it should be the same to have a search by comments, and wildcards shouldn’t be harder…
How can I start a limit search, or create one that filters the comment instead of the emailaddress, I tried to grep by emailaddress, but haven’t find where it is coded…

Thanks for any help

Samuel