Virtual fields for xDR import

The service provider, “Call X,” provides you with an “x.csv” file that you want to import into the PortaSwitch database. The file’s end-user accounts are named: 990001, 990002, 990003, etc. although the PortaBilling accounts belonging to the same end users employ a different naming scheme: 1000001, 1000002, 1000003, etc.

Thus, to successfully import the “x.csv” file, you need to convert the account names used by Call X to account names that are used in PortaBilling.

To accomplish this, use the data transformation tool – the built-in function of the xDR Mediation utility. It enables you to define the additional fields containing values that are obtained by modifying the original .csv data.

There are two ways to declare rules for data transformation. One way is to define virtual fields and store them in a local conf file. It is useful if your source CDR file is fairly simple and requires only trivial modifications.

Another way is to create a custom data transformation module and declare data transformation rules within. This way is preferable if you deal with complex data transformation rules that contain multiple subroutines, work with external modules, etc.

This chapter provides guidelines how to adjust the xDR mediation process by declaring virtual fields within a local conf file.

To declare a virtual field you create a pair:

name=expr

where name is an attribute name in a format that PortaBilling RADIUS can understand and expr is the Perl code that converts/translates data.

For example, the virtual field that you create for converting account names used by Call X into end user account names used in PortaBilling may look like the following:

User-Name=sub { $x=$data{'User-Name'}; $x=~s/^99/100/; return $x}; }

For more information about PortaBilling RADIUS please refer to the PortaSwitch external system interfaces.

For recommendations on the usage of Perl expressions please see the Recommendations section of this handbook.

For more virtual fields examples please see the Additional virtual field examples section of this handbook.

By default, the [DataTransformation] section of the CDR Extraction config file includes a link to the special local file: /home/porta-cdrmediator/etc/cdr_export.<cdr_extraction instance name>.local.conf, where <cdr_extraction instance name> is the name of the corresponding CDR Extraction instance, e.g., cdr-extraction-7:

[DataTransformation]
# If you include a local file with virtual fields definitions
# MAKE SURE it has the [DataTransformation] section header,
# not just the individual fields
Include        = /home/porta-cdrmediator/etc/cdr_export.cdr-extraction-7.local.conf

Note that you can use the Include attribute only one time in the section but you can define a path to your custom file.

1. Log in to the server with the xDR mediator configured (e.g., the web server) using ssh.
2. Create a local configuration file in one of the available text editors (e.g., Vim):

   sudo vim /home/porta-cdrmediator/etc/cdr_export.<cdr-extraction_instance_name>.local.conf

    

   where the <cdr-extraction_instance_name> is the name of the corresponding cdr-extraction instance.

3. Define the data transformation rules within.

The CDR Extraction considers the content of the included file to be an extension of the [DataTransformation] section, and all name=expr pairs from this file will be correctly processed as virtual fields.

sudo systemctl restart cdr-extd@<cdr-extraction_instance_name>

After you have defined the data transformation rules in the file, you need to add the file to the Deposit Files via the configuration server web interface in order to keep it throughout system upgrades.

The advantages of this are:

• You keep your changes throughout all configuration modifications made via the configuration server web interface and system upgrades.
• You can comfortably create long and complicated code for modifying the original .csv data.
• The xDR Import config file remains compact and easy-to-read and maintain.

When you create complicated conversions, some auxiliary custom methods might also be handy. You can define these methods in the Perl module and declare their use in the Virtual Fields section.

Use the Init attribute to declare external modules:

Init=sub {use Porta::CDR_Import::Utilities;}

This attribute can also be used to declare global variables:

Init=<<PERL
use Porta::CDR_Import::Utils;
use vars qw(%service_types);
PERL

When possible, use subroutines when composing Perl expressions:

sub { some code; }

A Perl subroutine code is compiled once at the start of script execution and can then be called whenever needed. It is not forbidden to use free-form Perl expressions, but they are compiled each time they are called, therefore they use more system resources. For that reason, use the free-form expressions only when absolutely necessary.

Compare the following code examples. Both return the static value 1.2.100.100:

• Recommended. This code is compiled once at the beginning of script execution:

h323-remote-address=sub { return '1.2.100.100'; }

• Not recommended. This code is compiled every time it is called during script execution:

h323-remote-address='1.2.100.100';

If a field is to contain a rather long mini-program, you can use a here-document:

Called-Station-Id=<<CLD
sub {
    my $x = $data{'CalledNumber'};
    if ($x =~ m/^conference/) {
        return 'CONFERENCE';
    }
    else {
        $x =~ s/^\+//;
        return $x;
    }
}
CLD

In the above example, the calledNumber field of the original .csv file contains CLDs. The value can be either a called number in a format like +12125551155 or a special destination of the Conference service in this format: conference ABC, conference BCA. The script checks each value of the calledNumber field, and if the value starts with a “conference…” substring, it forwards ‘CONFERENCE’ as a Called-Station-Id attribute value; in other cases it removes the leading “+” and forwards the called number.

The CLD field of the original .csv file contains CLDs. Before forwarding the field values as Called-Station-Id, you want remove the two leading zeros from them. You can use the following code for this:

Called-Station-Id=sub { $x = $data{'CLD'}; $x =~ s/^00//; return $x; }

In the original .csv file each CLD is represented as two fields: Country and Dialed Number. The first contains a country code and the second – the rest of the dialed number.

To combine the data of these two fields and forward it on as a value of the Called-Station-ID attribute, use the following code:

Called-Station-Id=sub { return $data{Country}.$data{DialedNumber}; }

The Account field of the original .csv file contains account names. You do not want to import records that have 1111111 as the account name (for example, these can be technical records that separate blocks of real xDRs). However, you want to have information in the log file about the why there is a skip in order to be able to distinguish the intentionally skipped records from those that were skipped due to errors. For this you can use the following code:

User-Name=<<USERN
sub {
    my $x = $data{'Account'};
        if ($x eq '1111111') {
            log_message("User-Name=1111111, skip this xDR");
            return undef;
        }
        else {
            return $x;
        }
}
USERN

There are xDRs for different services in the original .csv file. The Service field contains service type names that are defined using a naming scheme which is different from the one used in PortaBilling. To map the service type names from the original .csv file (voice, data, sms) to the PortaBilling service type names (Voice, Netaccess, Msg), you can use the following code:

Init=<<PERL
use vars qw(%service_types);
%service_types = ('voice' => 'Voice', 'data' => 'NETACCESS', 'sms' => 'Msg');
PERL

PortaOne-Service-Type=<<SERVICE
my $x = $data{'Service'};
return $service_types{$x} ;
SERVICE