Cell Phone Record Template Preparation
Each carrier sends records in a slightly different format. There will be times to compare records from two different carriers during an investigation. Also, the lead detectives and prosecutors will not likely be familiar with phone records. Putting all the records in a consistent, easy to understand format will help them understand the records with less interpretation, and save us time. The recommended template contains the following columns.
- Target number
- Connected To
- Duration Sec
- First Tower/Sector
- First Lat
- First Long
- First Azimuth
- First Address
- First City
- First State
- Last Tower/Sector
- Last Lat
- Last Long
- Last Azimuth
- Last Address
- Last City
- Last State
Records from different carriers will have different information so our template may adjust depending on our requirements for individual cases. We should include some elements of the template for every case. Include the target number. In the original records, the target number may not appear in a column by itself. It may appear as the calling number or the called number. Our analysis would benefit from having this number in a column by itself. Likewise, the connected to numbers, the numbers connecting to the target number during a call, may appear only in the calling or called number columns, and our analysis would benefit by having these numbers in columns by themselves. In some records, the only way to tell if a call was incoming or outgoing is by which column the target number appears. Therefore, the template should contain a column indicating if the call was incoming or outgoing. When we receive records with voice calls, text messages and data connections, we will want them in the same finished product. Having a column identifying the type of connection will be valuable. The timestamp column will provide the date and time, and the Duration Sec column will provide the length of the call. In cases where location information is not provided, or not valuable to the case, these columns may be enough. In cases where location information is valuable, we want not only the latitude and longitude, but we also want the address, city, and state in our template. We may map only a small portion of the records, but we want location information available to the lead detective and prosecutor. The latitude and longitude may not mean much without a map, but the address, city, and state probably would.
We will copy the data from our working CDR to populate the template, ensuring the data stays in the proper rows. The easiest way to maintain a consistent template is to save the template in a workbook and keep it in a convenient location like the desktop. Then copy the template to the working CDR worksheet.
Copy the target number to the first cell under the Target Number column on the template, then auto-fill the column with the target number.
The Connected To column will have the phone number connected to the target number. Since Sprint provides the phone numbers in either the CALLING_NBR or CALLED_NBR columns, we will need to use an If Then formula to separate the target number from the connected to numbers. Since we copied the target number into the CALLED_NBR column based on the DIALED_DIGITS column earlier, the target number will be in either the CALLING_NBR or CALLED_NBR column of each row. Our If Then will look for the target number in the CALLED_NBR column. If it appears in the CALLED_NBR column, then the number in the CALLING_NBR column will be imported to the Connected To column, If the target number does not appear in the CALLED_NBR column, then the number in the CALLED_NBR column will be imported onto the Connected To column. Our formula in the example will be =IF(M2=B2,A2,B2).
Auto-fill the column and remove the formula by copying it and Pasting Special Values.
Cell Phone Record Analysis for Investigators Paperback
The Connected To column is formatted as numbers. We can format the column as phone numbers by using the Format Cells function. Select all the entries in the column after the paste, but if we leave it for some reason, ensure the column is selected before we format the cells. Right-click on the selection and click on format cells. Select Special and Phone Number.
In the Direction column of our template, we need to identify whether the call was inbound, outbound, forwarded, or undetermined. Sprint provides the direction information in the MOBILE ROLE column in the Sprint records. Copy the MOBILE ROLE column and paste it in the Direction column ensuring the entries stay in the same rows.
The Type column in the template identifies whether the entry was a voice call or text message. In Sprint records we base the type of entry on the NEID used. On these records, the voice calls have an NEID of 420 or 422. All others are text messages. The NEID numbers will likely be different in your area, but you should be able to identify them with the NEID list from Sprint mentioned earlier in this course. After analyzing a few sets of records, you should become familiar with the NEIDs in your area.
Cell Phone Record Analysis for Investigators eBook
To fill the Type column, we will use an If Then formula with the addition of OR. If the NEID column is 420 OR 422, then we want the text to return “Voice.” If not, then we want the text to return “Text.” The text we want to return must be in quotation marks “”.In this case, the formula will look like [=IF(OR(H2=420,H2=422),“Voice”,“Text”]. (Exclude the brackets.)
Auto-fill the column, Copy and Paste special, values.
Leave the Subscriber column blank for now. We will fill it in later.
Next, we will fill the Timestamp and Duration Sec columns. Copy the START_DATE column from the Sprint records to the Timestamp column in the template and the DURATION (SEC) column in the Sprint records to the Duration Sec column in the template.
We will use the First Cell/Sector and Last Cell/Sector columns as the lookup value to import the latitudes, longitudes, and sector azimuths into the template using the VLookup formula. Having the lookup value identical in both the template and the cell site list is critical. The VLookup formula won’t work otherwise. We will use the NEID, cell number, and sector number, in that order, as the lookup value. We will use the Concatenate formula to bring the NEID, cell, and sector into a single cell in the template. In this case, the formula will look like [=CONCATENATE(H2,J2,I2)] (exclude the brackets).
Auto-fill the column. At this point, the column reads as numbers but has formulas. The formula will not work as the lookup value for the VLookup formula, so we need to Copy the column, and Paste special, values. But, we are still not finished. When the numbers are pasted, by default, Excel records them as text. Again, the numbers stored as text will not work as the lookup value. A warning sign will appear next to the first selected cell, and all the cells will have a green triangle in the upper left corner to denote the number is stored as text. Ensure all the numbers stored as text are selected and click the drop-down next to the warning sign. Select Convert to Number.
Repeat the process with the Last Cell/Sector column.
The latitude, longitude, and sector azimuths for cell sites and sectors are not available in the CDR. They are in a separate spreadsheet provided by Sprint. We can import the data into our template using the VLookup formula. We will need to set the lookup value in the cell site list in the same format as we used in the First Cell/Sector and Last Cell/Sector columns in the template.
Open a working copy of the cell site list. If the records cover more than one NEID, such as if the phone traveled to different geographic areas during the timeframe of the records, Sprint will provide more than one cell site spreadsheet. Sprint provides cell site lists for all NEIDs with cell sites used in the records. If there is more than one cell site list, combine them into one making sure the columns are in order. Copy all the data from one of the spreadsheets and paste it under all the data in another. Make sure the combined spreadsheet is renamed to reflect the combination of more than one NEID.
In our example, the only calls with cell site information are in NEID 422. We had some calls in NEID 420, but none of them had cell site information.
Check the BTS MANUFACTURER column. Look to see if any of the entries show Lucent. Most of the time the same manufacturer will be consistent throughout an NEID. If Lucent is present, we need to adjust the sectors in the template. For Lucent NEIDs, the sector numbers in the cell site list will be 1, 2, and 3, but the sector numbers in the CDR will be 2, 3, and 4. We would need to convert the sectors in the CDR from 2 to 1, 3 to 2, and 4 to 3. If we don’t, we would be mapping the wrong sector. If the manufacturer is anything other than Lucent, such as Samsung or Nortel, this is not an issue. The sector numbers in the cell site list will correspond with the sector numbers in the CDR. In our example, the manufacturer is Samsung.
Add a blank column in column A while shifting the original columns to the right. Name it NEID/Cell/Sector.
We are going to combine the NEID, Cell#, and Sector columns. The formula will look like [=CONCATENATE(E2,B2,J2)] (exclude the brackets).
Auto-fill the column, Copy, Paste special, values, and Convert to Number.
Originally posed: http://revforensics.com/cell-phone-record-template-preparation/