Downloading CDRs via FTP and Subsequent Filtering

Downloading CDRs via FTP and Subsequent Filtering

Downloading CDR files via sFTP:


A FTP client is required to access an sFTP account. FileZilla is a common, easy and free choice of FTP client software. Click here for download information.


Steps:

  1. Once installed, open FileZilla and then click File and then Site Manager so we can allow FileZilla to save your desired FTP account information.

     

  2. The Site Manager window will appear which will allow you to save various “site(s)”. Create a new one by clicking the New site button and name it appropriately. (try to include purpose and FTP location)

    1. Select the SFTP protocol from the Protocol dropdown menu

    2. Enter the IP or hostname into the Host field

    3. Enter the sFTP user account in the User field

    4. Enter the sFTP user account password in the Password field

    5. Note: If you have any Logon Type/Password saving issues, head to Edit > Settings > Interface > Passwords to then select the Save passwords option to allow for the Normal logon type.

  1. Clicking Connect or OK after entering the site information will be saved in the Site Manager for further use. Head back to the Site Manager if you would like to connect to your account quickly in the future.

    1. Note: The popup for Unknown host key is normal, verify the server details are correct and select the option to Always trust that host key if you prefer. Then select OK.

  2. To download once connected to a sFTP account, click+drag or right click a file or folder from the “Remote site:” (the FTP server) pane on the right hand side of FileZillla and it will download to the “Local site” (your computer) shown on the left hand side.

    1. To upload, perform step 4 but from the left side pane (Local site) to the right side pane (Remote site).



Filtering Bulk CDR Data:


CDR data pushed from the 46 Labs platform output in a bulk, hourly .CSV file format. 


The file/folder structure is as follows:

  • Year (Folder)

    • Month (Folder)

      • Day (Folder)

        • 24 hourly .CSV files


Once you have located the data and downloaded the files from the sFTP you can begin to filter the information using Excel or a similar program. A few notes:

  • These steps are based upon the use of Excel.

  • It may be difficult/slow for some computers to load large (multi-gigabyte sized) .CSV files.


Steps:

  1. Open Excel (or a similar program)

  2. Select the Data tab

  1. Click on the Get Data button, then under From File, select From Text/CSV

  1. Locate a downloaded CDR.csv file and click the Open button

  2. Once the file is loaded, select the Transform Data button so you can query the data

  3. Within the Power Query Editor:

    1. Locate either the Originating Carrier (orig_carrier_name, column N) or the Terminating Carrier (term_carrier_name, column V) by scrolling to the right

    2. Select the drop-down button from the right hand side of the column/header box

  1. Deselect all by clicking the (Select All) checkbox

    1. Select the specific carrier(s) you would like to incorporate into a new .CSV file

    2. Click OK to instruct it to sort by the selected carrier(s)

  1. Click the Close & Load button

  1. Save your newly generated .CSV file

    1. Sort for a specific ANI/DNIS if necessary before saving



    • Related Articles

    • CDR Dictionary

      The purpose of this article is to enumerate and describe the fields in the CDR. CDR Files Specification File format: The default file is comma-separated with a header row. Fields have maximum lengths based by data type as described in the Format ...

    • Latest Push List for PE/ECO

      August 2022 Push List: DNC List: The National Do Not Call Registry is a list of phone numbers from consumers who have indicated their preference to limit the telemarketing calls they receive. The DNC list will be loaded into a 46 Labs managed ...

    • Origination-Customer Relationship Configuration

      A Relationship is a network configuration between your company and your customers or vendors. By creating a Relationship, you will set up the routing logic and rates that enable you to capitalize on the phone calls made on your network. An ...

    • STIR/SHAKEN

      46 Labs - STIR/SHAKEN Explanation Moving Parts of STIR/SHAKEN The purpose of this section is to briefly explain the moving parts of STIR/SHAKEN and describe 46 Labs’ level of participation for each: Digital Certificate: This certificate is much like ...

    • Relationship Overview Dashboards

      Trunk Groups Overview Relationships > Trunk Groups Overview The Trunk Groups Overview page displays all the currently configured Trunk Groups in Perimeter.  The page displays the following information for each Trunk Group: Relationship - The parent ...