Madeleine – 74,000 calls/texts 3

Page 5


In view of the different formats of databases that operators sent, as can be seen in the CDs / DVDs in the file, it was necessary to proceed with the formatting of date, time, and other data that needed to be understood.

{I.e. the different formats needed to be converted to a single, common format.}

For example, it was necessary to separate the elements that made up the date, in the case of Vodaphone – 20070502, and then to return to regroup them correctly – 02-05-2007. The hours in the case of TMN are recorded with the digits related to the separate hours of the minutes and the seconds 17 345, creating formulas to add “0” (zero), where they were necessary, it separated minutes from seconds and then regrouped the three pairs of numbers, adding the world separator of hours “:” 17:03:45 hours.

Page 6

Beyond this “cleaning”, it was necessary to determine which table column corresponded to the phone transmitter and receiver, as well as what activated the antenna, because even in these cases the three operators would adopt different ways to archive their records.

TMN sent only a sheet in CSV (Comma-Separated Values) where in addition to the date, time, name of the antenna and respective code, it has a column marked with “MSISDN”, which contains the numbers which activate the antenna and another called “OTHER_PARTHY”. The distinction between the transmitter and the receiver number, is made across the indications, “Voice started”, “Voice ended”, “SMS originated” or “SMS finished”, that are in the column labelled “CALL_TRANSACTION_GROUP”. Existing also a column for duration.

Vodaphone sent three (3) Excel books, corresponding to the three days requested. Each book consisted of four (4) pages – “Outgoing calls”, “Incoming Calls”, “SMS sent” and “SMS received”. The separation between types of contacts makes it possible to determine the direction of the contact, which is the numbers always in the column entitled “Number of Phone Caller” intended for the numbers that are in the “Phone Number Called” column. As for the activation of the antennas, it depends on whether you are in the paper of made or received contacts. The pages of voice or SMS made, who activates the antenna is the source phone – caller – while on the other the voice and SMS received is the receptor – called – who activates the antenna.

Optimus sent only one book, distributing the contacts by multiple sheets, “Cell ID”, “Calls Made”, “Calls Received”, “GPRS”, “SMS Sent”, “SMS Received”, “Roamers In”, “Roamers Out” and “UMTS” . As to the activation of the antennas (14931, 14932 and 14933), this information is contained in the sheets themselves with the indication in column – “A_BTS_CGI” or “B_BTS_CGI”, where these agree with “A_MSISDN” or “B_MSISDN” to activate the antenna.

{GPRS is a system where service is not guaranteed, but the operator uses any spare capacity at the time for traffic. This would fail if the system was a t full capacity for guaranteed services.}

{UMTS is a 3G very high speed service first implemented in 1999, but it requires new antennas and new frequency allocation.}

Now in the concern of the contact direction, the direction is always “A_MSISDN” to “B_MSIDSN”, except for “SMS Sent” in that the constant numbers “B_MSIDSN”, are message centres through which the SMSs pass, whereby the meaning is “A_MSISDN” to “C_MSIDSN”, the sheet “SMS Received”, also it is a particular case that had to be studied “manually”, to avoid the triangulation of this type of contact (C sends to A which in turn sends to B, which receives).

Page 7

When there existed whether in “A_MSISDN”, or in “C_MSISDN”, numbers that corresponded to messaging centres and the numbers that actually sent the message, only the latter was picked up, in order to with the resource of the program/analysis tool (Analyst Notebook ), draw up a representative graph of the communications made.

As regards the sheet “RoamersIn,” according to an official of this operator, this sheet should not have been included in the book, since there would be constant data, to the match, in the other sheets. However, he advised to carry out a detailed analysis of that data, as he feared to have occurred some error in the system that makes the records.

As it turned out in verification only one contact that was in this sheet was not found in the rest.

The contact maintained with that employee it was obtained the information that in the “SERVED_NUMBER” column, in the “RoamersIn” sheet are indicated the numbers that activate the antennas, there having no indication or way to tell whether it was making or receiving communication.

This as it turned out caused some errors in the charts (Charts) developed by elements of the English authorities and rectified by colleagues of the DCCB.

{DCCB = Direcção Central de Combate ao Bandestismo, basically the central leader of the combat of organised crime, and it is a unit within the PJ.}

These errors are: the direction of contact; their duplication, because there are differences of seconds between the registration of the contact and the registration of the “roamings”; and, the lack of SMS’s received.

After this work, they produced the graphics (Charts1) that are attached2. From the reading the following (conclusions) can be taken away:

1 It joins a file that must be installed to proceed to the reading of this type of computer file.

2 In the different graphics it will be possible to bring together two contacts of different colours very close, the two overlap, between the two participants, but this is due to the fact that the participants activated different operators in the same contact and as such has seconds of difference.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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