When using Best Practical’s Request Tracker (RT) ticketing system, we populate many of the users in the system from our Active Directory (AD) using RT’s LDAP tools. This means that our users have their username, email, fullname, department and work phone numbers appear in RT based on what is held in the AD.

We’ve recently been asked if we could add some additional information onto the user records in RT based on AD’s LDAP data, but for which there is not an existing RT User attribute available. In this case it was the “description” in an AD LDAP user record, which here includes information on whether the user is staff, student, visitor, etc. This post details how we’ve gone about it using a custom field attached to the User record in RT, plus changes to make this custom field value usable in search results and charts for reporting.

Adding the User custom field

As an RT administrator its relatively easy to create a new custom field and attach it to RT’s users. In this case we decided to call the custom field “UserType” (as that’s the bit of the AD description contents that we’re interested in). Its just a simple single value text custom field in RT.

Importing LDAP description data into the UserType custom field

We have to tell RT how to get LDAP description data into the UserType custom field for each user. In our case this is done in two places in the local RT_SiteConfig.pm:

• $ExternalSettings
• $LDAPMapping

Both of these have had an addition made to their LDAP attribute mappings that looks like:

'UserCF.UserType' => 'description',

These extra lines map the LDAP description attribute to the User custom field ‘UserType’, which can then be filled in either during LDAP based logins to the system or nightly via cron invoking the rt_ldapimport tool.

At this point we have a custom field attached to each local user that appears in our AD database, but we wanted to be able to use that in TicketSQL searches and display results.

Accessing UserType custom field in search queries and results

To let us make use of the UserType custom field in searches we need to provide some local tweaks to the RT system. Luckily RT is superb at allowing local modifications to its code base, giving us an /opt/rt4/local directory that, as well as accomodating whole local versions of library routines, HTML/Mason page elements, CSS, JavaScript, images and configuration files, can hold local overlay and callback files to allow us to target changes with minimal disturbance to the distributed code.

In this case we’ll make two alterations via this route: a local overlay for the RT::Report::Tickets RT Perl module to allow us to search on the new custom field and a local callback fired off from the Elements/RT__Ticket/ColumnMap Mason page element to display the vallue of that custom field for a particular type of user (in our case we’re interested in ticket requestors).

The local overlay in /opt/rt4/local/lib/RT/Report/Tickets_Local.pm adds the UserType custom field to “Watcher” grouping of searchable user attributes, and then provides a replacement for the GenerateWatcherFunction() that contains the DBDx::SearchBuilder based implementation of the user custom field searching. It looks like this:

use strict;
no warnings qw(redefine);
package RT::Report::Tickets;

our @GROUPINGS;
push @GROUPINGS, (
    Subject       => 'Enum',
    );

our %GROUPINGS_META;
$GROUPINGS_META{'Watcher'} = {
    SubFields => [grep RT::User->_Accessible($_, "public"), qw(
       Name RealName NickName
       EmailAddress
       Organization
       Lang City Country Timezone
       CF.UserType
       )],
    Function => 'GenerateWatcherFunction',
};

sub GenerateWatcherFunction {
    my $self = shift;
    my %args = @_;

    my $type = $args{'FIELD'};
    $type = '' if $type eq 'Watcher';

    my $column = $args{'SUBKEY'} || 'Name';

    my $u_alias = $self->{"_sql_report_watcher_users_alias_$type"};
    unless ( $u_alias ) {
        my ($g_alias, $gm_alias);
        ($g_alias, $gm_alias, $u_alias) = $self->_WatcherJoin( Name => $type );
        $self->{"_sql_report_watcher_users_alias_$type"} = $u_alias;
    }
    if($column =~ /^CF\.(.+)$/) {
        my $cfName = $1;
        my $cf = RT::CustomField->new( $self->CurrentUser );
        $cf->Load($cfName);
        unless ( $cf->id ) {
            $RT::Logger->error("Couldn't load CustomField #$cfName");
            @args{qw(FUNCTION FIELD)} = ('NULL', undef);
        } else {
            my $user = new RT::User($self->CurrentUser);
            my $type = 'RT::User';
            my $ocfvalias = $self->Join(
                TYPE   => 'LEFT',
                ALIAS1 => $u_alias,
                FIELD1 => 'id',
                TABLE2 => 'ObjectCustomFieldValues',
            FIELD2 => 'ObjectId',
            $cf->SingleValue? (DISTINCT => 1) : (),
                );
            $self->Limit(
                LEFTJOIN        => $ocfvalias,
                FIELD           => 'CustomField',
                VALUE           => $cf->Disabled ? 0 : $cf->id,
                ENTRYAGGREGATOR => 'AND'
                );
            $self->Limit(
                LEFTJOIN        => $ocfvalias,
                FIELD           => 'ObjectType',
                VALUE           => RT::CustomField->ObjectTypeFromLookupType($type),
                ENTRYAGGREGATOR => 'AND'
                );
            $self->Limit(
                LEFTJOIN        => $ocfvalias,
                FIELD           => 'Disabled',
                OPERATOR        => '=',
                VALUE           => '0',
                ENTRYAGGREGATOR => 'AND'
                );

            @args{qw(ALIAS FIELD)} = ($ocfvalias,, 'Content');
        }
    } else {
        @args{qw(ALIAS FIELD)} = ($u_alias, $column);
    }

    return %args;
}


1;

The %GROUPINGS_META hash has had its ‘Watcher’ element replaced to add an extra potentially searchable subfield called ‘CF.UserType’. This is our UserType custom field on the User object we’re searching for. The @GROUPINGS array also has an extra element added in but that’s not really relevant here – we just wanted to add ticket subjects to it.

The main guts of the work comes in the reworked GenerateWatcherFunction() routine. This is based on the version in the distributed RT code, with an additional check to see if the column being processed matches the regex pattern /^CF.(.+)$/. If it does, the part after “CF.” is taken as the name of the custom field to look for (in our case ‘UserType’) and then it uses three DBDx::SearchBuilder calls to do a series of SQL joins to link the user object to the custom field and check if the custom field matches what we’re looking for. We need to not only match a custom field based on the ‘UserType’ custom field name but also make sure that the custom field type matches a User object type. We also need to make sure it has not been disabled.

The second part making the UserType custom field available in searching is the callback for search result display that lives in /opt/rt4/local/html/Callbacks/lboro/Elements/RT__Ticket/ColumnMap/Once. This adds a RequestorsUserType column map for the searches that grabs the UserType custom field for the matched requestors. This looks like this:

<%init>
  $COLUMN_MAP->{'RequestorsOrganization'} = {
    title     => 'RequestorsOrganization', # loc
    attribute => 'Requestors',
    value     => sub { 
      my $deepMembers = $_[0]->Requestor->DeepMembersObj;
      return "None" if(!$deepMembers);
      while(my $member = $deepMembers->Next) {
        if($member->MemberObj->IsUser) {
          return $member->MemberObj->Object->Organization;
        }
      }
      return "Unknown";
    }
  };
  $COLUMN_MAP->{'RequestorsUserType'} = {
    title     => 'RequestorsUserType', # loc
    attribute => 'Requestors',
    value     => sub { 
      my $deepMembers = $_[0]->Requestor->DeepMembersObj;
      return "None" if(!$deepMembers);
      while(my $member = $deepMembers->Next) {
        if($member->MemberObj->IsUser) {
          return $member->MemberObj->Object->FirstCustomFieldValue('UserType');
        }
      }
      return "Unknown";
    }
  };
</%init>
<%ARGS>
$GenericMap => undef
$COLUMN_MAP => undef
</%ARGS>

I should note that this callback also adds a RequestorOrganization display option too. We’ve used that for a while to show which school or department the requestor is from. That data is also pulled from the AD but unlike the UserType it goes into a straight RT User object attribute (the Organization field). This means that we need the Callback to display it in search results, but it is already included in the TicketSQL query builder.

Conclusion

So that’s how we’ve added a custom field to our users, populated the data in it from our ActiveDirectory via LDAP and then made the custom field available in TicketSQL queries and search results. The basic mechanism works well and is relatively easy to understand once you know what you’re looking at. The most complicated part is probably the DBDx::SearchBuilder calls required in the GenerateWatcherFunction() to do searches based on the custom field. However the code above in the overlay for RT::Report::Tickets is generalised – its looking for a search column that starts with “CF.”, and then takes the rest of the column name as the custom field name. Thus we could now easily add other custom fields to user objects and the %GROUPINGS_META hash and search for them in ticket watchers.