FTP Daily CDR to remote Server

Discussion in 'General' started by QCFourie, Oct 24, 2010.

  1. QCFourie

    Joined:
    May 25, 2010
    Messages:
    16
    Likes Received:
    0
    Hi Everyone

    Hope this script can be helpful to someone. Basically it dumps the daily CDR records to a file, archive’s it with ZIP and then ftp’s it to an ftp server using NCFTP.

    First install NCFTP client by following instructions in http://www.ncftp.com/download/

    Then, create folder /tmp/backup and assign the appropriate write rights to the folder for your user.

    Create the script below in the /usr/sbin folder, let's call the script ftpDailyBackup.sh

    Don’t forget to change the details applicable to your environment.

    ### Script Start

    ### System Setup ###
    BACKUP=/tmp/backup
    NOW=$(date +"%Y-%m-%d")

    ### MySQL Setup ###
    MUSER="user"
    MPASS="password"

    ### FTP server Setup ###
    FTPU="user"
    FTPP="password"
    FTPS="192.168.0.1"

    ### Other stuff ###
    EMAILID="me@work.com"

    ### Start MySQL Backup ###
    FILE=$BACKUP/qcs-$NOW.txt

    mysqldump -u $MUSER -p$MPASS --fields-enclosed-by=\" --fields-terminated-by=',' --tab=$BACKUP asteriskcdrdb cdr --where="calldate > '$NOW 00:00:00'"

    mv $BACKUP/cdr.txt $FILE

    ### Archive File
    zip -r9 $FILE.zip $FILE

    ### Dump backup using FTP ###
    ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF
    lcd $BACKUP
    mput $FILE.zip
    quit
    EOF

    ### Find out if ftp backup failed or not ###
    if [ "$?" == "0" ]; then
    rm -f $FILE.zip
    rm -f $FILE
    rm -f $BACKUP/cdr.sql
    rm -f $BACKUP/cdr.txt
    else
    T=/tmp/backup.fail
    echo "Date: $(date)">$T
    echo "Hostname: $(hostname)" >>$T
    echo "Backup failed" >>$T
    mail -s "BACKUP FAILED" "$EMAILID" <$T
    rm -f $T
    fi

    ### Script End

    Finally schedule a cron job to execute after hours, lets make it 23:00 at night.
    # crontab –e

    Ener the line:
    59 22 * * * sh /usr/sbin/ftpDailyBackup.sh



    Regards
    Quentin

    quentin@qcsolutions.co.za
     
  2. danardf

    Joined:
    Dec 3, 2007
    Messages:
    8,069
    Likes Received:
    12
    Hi.

    Indeed, this script should be useful. :)

    Good work.
     
  3. garrywadams

    Joined:
    Jul 1, 2010
    Messages:
    37
    Likes Received:
    0
    When I first ran through your instructions I installed NCFTPd (the FTP server, not the FTP client). You might update your post to indicate that you need the FTP client, not the server. I was confused at first until I realized that what I installed was not an FTP client at all. :)

    The NCFTP website has great instructions on instaling the NCFTPd server, but is lacking in the client install instructions. I have modified the script to run from where I extracted the binaries. Does anyone know what I need to do to add the NCFTP client binaries so they don't have to be run from the temp directory where they were extracted?

    Great script, by the way. This is just what I was looking for. I may need something that can do more of an hourly dump instead of daily, but this should do for now.

    Thanks!
     
  4. garrywadams

    Joined:
    Jul 1, 2010
    Messages:
    37
    Likes Received:
    0
    Ok, I got the NCFTP client installed so I don't have to run it from the /tmp directory anymore.

    Still, it would be nice to modify the frequency of the script to run several times during the day. We will be using it for call record integration with our CRM. The data will only be helpful if it is current. Definitely a step in the right direction!
     
  5. QCFourie

    Joined:
    May 25, 2010
    Messages:
    16
    Likes Received:
    0
    Glad it could be of some use.

    To increase the frequency, to let’s say hourly, I would just change the T-SQL MYSql statement to retrieve the last hours data based on the current date and time and then the cronjob to execute hourly.

    Thanks again
    Quentin

    www.qcsolutions.co.za
     
  6. garrywadams

    Joined:
    Jul 1, 2010
    Messages:
    37
    Likes Received:
    0
    I'm not very good with SQL, sorry... I don't see a T-SQL statemet in that code anywhere. I'm not exactly sure what I'm looking for. Could you be a little more specific?

    Also, I have changed a few other items to suit my environment a little better. For example, our FTP server sits on the same LAN so bandwidth isn't really an issue. I am skipping the archive section so that I don't have to extract the zip file to view the CDR. I am also saving it in csv format so I can open it directly in Excel on the other end. Other than that, its pretty much un-modified.

    My code (with sensitive info changed, of course):

    ### Script Start

    ### System Setup ###
    BACKUP=<BACKUP DIR>
    NOW=$(date +"%Y-%m-%d")

    ### MySQL Setup ###
    MUSER="<SQL USER>"
    MPASS="<SQL PASS>"

    ### FTP server Setup ###
    FTPU="<FTP USER>"
    FTPP="<FTP PASS>"
    FTPS="<FTP SERVER>"

    ### Other stuff ###
    EMAILID="<EMAIL ADDRESS>"

    ### Start MySQL Backup ###
    FILE=$BACKUP/<PBX NAME>-$NOW.csv

    mysqldump -u $MUSER -p$MPASS --fields-enclosed-by=\" --fields-terminated-by=',' --tab=$BACKUP asteriskcdrdb cdr --where="calldate > '$NOW 00:00:00'"

    mv $BACKUP/cdr.txt $FILE

    ### Archive File
    # zip -r9 $FILE.zip $FILE

    ### Dump backup using FTP ###
    ncftp -u"$FTPU" -p"$FTPP" $FTPS<<EOF
    lcd $BACKUP
    mput $FILE
    quit
    EOF

    ### Find out if ftp backup failed or not ###
    if [ "$?" == "0" ]; then
    # rm -f $FILE.zip
    rm -f $FILE
    rm -f $BACKUP/cdr.sql
    # rm -f $BACKUP/cdr.txt
    else
    T=/tmp/backup.fail
    echo "Date: $(date)">$T
    echo "Hostname: $(hostname)" >>$T
    echo "Backup failed" >>$T
    mail -s "BACKUP FAILED" "$EMAILID" <$T
    rm -f $T
    fi

    ### Script End
     
  7. garrywadams

    Joined:
    Jul 1, 2010
    Messages:
    37
    Likes Received:
    0
    On second thought, I think I will change the frequency of the cron job and leave the SQL dump portion alone. This means that every hour (or however often I schedule the cron job) I will get an updated csv file with the entire day's CDR. I will overwrite the copy on the FTP server so that when I open it, it will be the full day's CDR up to the most recent hour.

    That will mean fewer files to prune out when the FTP directory starts getting huge, and it will mean that if I miss an hour (because a server is rebooting or offline or something) it won't matter. I won't have a blank spot in my CDR because next time it runs it will update with the full day's CDR.

    In short, the code as it exists above will work perfectly. Thanks again!
     
  8. QCFourie

    Joined:
    May 25, 2010
    Messages:
    16
    Likes Received:
    0
    Very good idea!

    Thanks for the feedback.

    Quentin
     

Share This Page