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